dlookup using form and query

[ggreg]

I have search and checked the inter-net, I just do not seem to be finding what I am doing but I do not think I am doing any thing special....using access 2007......

My form has two textbox (Text27 and Text141 at the top(I am using only the detail area of the form) Then i have a section that have different pages for the different area of the data...titles
of the pages are like Dates...Address...Status

I would like my form to bring up data from my query "Cqry_mcr"
but I would like only the ccontid that a person selects

In the control source of Text27 I have
=DLookUp("[CCONTID]","[Cqry_mcr]","[ccontid]=Forms![Cfrm_mcr]!ccontid")

Text141 is = to PCN in my Cqry_mcr....pcn is a (many) to ccontid
I would like Text141 to work like I want Text27

I would like 141 and 27 to be search boxes to bring up
data in the form from query Cqry_mcr no matter which box
they use...27 or 141 ....


In the query.... in ccontid I have [Forms]![Cfrm_mcr]![Text27]

When I open the form it opens with no fields or text boxes
and showing no records at the bottom where the records: of

Can some one please help me ?

Thanks in Advance!

 

[randy700]

In the control source of Text27 I have
=DLookUp("[CCONTID]","[Cqry_mcr]","[ccontid]=Forms![Cfrm_mcr]!ccontid")

If the CCONTID field is numeric, try this:

DLookUp("[CCONTID]","[Cqry_mcr]","[ccontid]= [b][red]" &[/red][/b] Forms![Cfrm_mcr]!ccontid)

If the CCONTID field is text, try this:

DLookUp("[CCONTID]","[Cqry_mcr]","[ccontid]= [b][red]'" & [/red][/b]Forms![Cfrm_mcr]!ccontid[b][red] & "'[/red][/b]")

Randy

 

[PHV]

1) In the control source of Text27 I have
=DLookUp("[CCONTID]","[Cqry_mcr]","[ccontid]=Forms![Cfrm_mcr]!ccontid")
2) In the query.... in ccontid I have [Forms]![Cfrm_mcr]![Text27]

So, infinite loop, don't you think so ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ggreg]

Randy I made your change to look like the text one.....and PHV
I removed # 2 from the query.....

Now when opening the form it loads the whole query (or all the data) and I can not put anything into the text27...it dings
when trying to type in the box but only the first record is there

So how can I use the form to only bring in the record that I want
using a Dlookup method?

 

[randy700]

Sorry, after re-reading your post, I see my error.
It see that you are trying to look up a value in the query based on the value of the text box where you want to put it!

Your dlookup statement could be translated as:
Make this text box equal to the value of a field in the query that is equal to the value in this text box.


Randy

 

[ggreg]

randy,

I put the query back like it was and change
the text27 to =DLookUp("[CCONTID]","[Cqry_mcr]","[Text27]=Forms![Cfrm_mcr]!ccontid")

I still get nothing just a blue form with nothning on it.....

And if I take out the dlookup and put in the field name of the query ccontid.....I still get nothing and I do not understand why
the form is not opening with the text box for me to put text in
it to search for a record in the query....?