Using DLookup to populate a text box

[davwhsdb]

Like the subject states, I want to use DLookup to populate a text box.


My main form is called Inquiry Portal, and I have a combo box on it
called InquiryNumber. When an inquiry number is selected from the
combo box, I want my text box to look in the InquiryHeader table and
pull the Initiated_By field that corresponds to the selected inquiry
number. The primary key that links all these tables is called
Inquiry_Number (confusing, huh?)

I wrote this DLookup statement to do this:
DLookup("[Initiated_By]", "InquiryHeader", "InquiryNumber =
Forms!InquiryHeader!Inquiry_Number")

Is the syntax of this statement correct?

Where do I put the statement? At first I put it in the data
ControlSource for the text box, but I don't think that will capture
changing the combo box. Should I put it in the On Change event for the
combo box and reference the text box? Like

me.txtbox.value = DLookup("[Initiated_By]", "InquiryHeader",
"InquiryNumber = Forms!InquiryHeader!Inquiry_Number")

I plan on having multiple text boxes that populate this way, so would I
just put a bunch of these statements in the combo box's On Change
event?

Thanks,
David

 

[Guest]

Forms!InquiryHeader!Inquiry_Number has to be outside the quotes. The way it
is coded, you are sending the literal value
"Forms!InquiryHeader!Inquiry_Number" to DLookup. It should be this if
[Initiated_By] is a numeric field:
DLookup("[Initiated_By]", "InquiryHeader", "InquiryNumber = " &
Forms!InquiryHeader!Inquiry_Number)

If it is a text field the syntax would be:
DLookup("[Initiated_By]", "InquiryHeader", "InquiryNumber = '" &
Forms!InquiryHeader!Inquiry_Number") & "'"

The value will change with the formula in the control source (preceded by =)
=DLookup("[Initiated_By]", "InquiryHeader", "InquiryNumber = " &
Inquiry_Number)

The problem is, it will not update a field if that is what you are
expecting. If it is only for display purposes, then that is fine. If you
need to update it to a field, then your control source needs to be that field
and you would put it in the After Update event of the combo. You will seldom
every use the Change event, particularly in a combo box. It fires after
every keystroke (when it says Change, it means it).

 

[davwhsdb]

Alright, so I tried that stuff and it doesn't work. I have regressed
to just trying to get the stupid text box to display a value from a
table by using DLookup.

For the Default Value of the text box, I have placed the following
code:

DLookUp("[Initiated_By]","InquiryHeader","Inquiry_Number =" &
"AB05-509")

I'm trying to look up the Initiated_By field in the InquiryHeader table
for the Inquiry Number of AB05-509 and the error message I get is "The
object doesn't contain the Automation object 'AB05'"

I tried a few variations of that code, as well:

DLookUp("[Initiated_By]","InquiryHeader","Inquiry_Number = AB05-509")
DLookUp("[Initiated_By]","InquiryHeader","[Inquiry_Number] = AB05-509")

I'm wondering if Access doesn't like the format of the inquiry number
since it has the - in there. And I'm not sure I understand the usage
of square brackets. I thought you only had to use them if your field
names had spaces in them.

Anyway, any help would be greatly appreciated, and thanks for the quick
response on the first question.

Dave

 

[Rick Brandt]

Alright, so I tried that stuff and it doesn't work. I have regressed
to just trying to get the stupid text box to display a value from a
table by using DLookup.

For the Default Value of the text box, I have placed the following
code:

DLookUp("[Initiated_By]","InquiryHeader","Inquiry_Number =" &
"AB05-509")

I'm trying to look up the Initiated_By field in the InquiryHeader
table for the Inquiry Number of AB05-509 and the error message I get
is "The object doesn't contain the Automation object 'AB05'"

I tried a few variations of that code, as well:

DLookUp("[Initiated_By]","InquiryHeader","Inquiry_Number = AB05-509")
DLookUp("[Initiated_By]","InquiryHeader","[Inquiry_Number] =
AB05-509")

I'm wondering if Access doesn't like the format of the inquiry number
since it has the - in there. And I'm not sure I understand the usage
of square brackets. I thought you only had to use them if your field
names had spaces in them.

Anyway, any help would be greatly appreciated, and thanks for the
quick response on the first question.

Dave

Your "inquiry number" is not a number. It is text and therefore needs quotes
around it. Otherwise Access thinks it is the name of a field or parameter.

DLookUp("[Initiated_By]","InquiryHeader","Inquiry_Number = 'AB05-509'")

 

[Guest]

It only works when you use the proper syntax
DLookUp("[Initiated_By]","InquiryHeader","Inquiry_Number = '" &
"AB05-509'")

 

[davwhsdb]

Yeah, I had a big discussion with a co-worker about what the "'" is and
converting the inquiry "number" to a string value.

It works now.

Thanks for the help!

Dave

 

[Guest]

Here is how it works:
If the field in the table is a number, use no delimiters:

DLookup("[Initiated_By]", "InquiryHeader", "InquiryNumber = " &
Forms!InquiryHeader!Inquiry_Number)

If it is a text field it needs either ' or "

DLookup("[Initiated_By]", "InquiryHeader", "InquiryNumber = '" &
Forms!InquiryHeader!Inquiry_Number) & "'"

If it is a date field it needs #

DLookup("[Initiated_By]", "InquiryHeader", "InquiryNumber = #" &
Forms!InquiryHeader!Inquiry_Number) & "#"

 

[Ken Snell \(MVP\)]

Another method:
http://www.mvps.org/access/forms/frm0058.htm