Dlookup is broken...

[cmraguilar]

So i've got a Dlookup function that will pull the information correctly when
the form will load. But when the combo box is changed, the Dlookup doesn't
change also. I have another field with a Dlookup function and that works
perfectly. I attemped to use the same code and changing the needed fields,
but I got errors from that.

This is the working code on one text box
=DLookUp("[ParentWSINumber]","tblSupplierParent","[SupplierName]='" &
Nz(Forms![Data Transaction Sets]!cboSupplierParentName.Column(1)) & "'")

This is the code on the text box that only loads once and doesn't change
when the combo box changes.
=DLookUp("[City]","[tblSupplierChild]","[WSInumber] = Forms![Data
Transaction Sets]!cboChildWSINumber")

I know they are pulling from difference combo box's but I dont think that
would make much of a difference. The combo box's have the needed field in
the query to populate them.

Thanks in advnace.

 

[Dirk Goldgar]

So i've got a Dlookup function that will pull the information correctly
when
the form will load. But when the combo box is changed, the Dlookup
doesn't
change also. I have another field with a Dlookup function and that works
perfectly. I attemped to use the same code and changing the needed
fields,
but I got errors from that.

This is the working code on one text box
=DLookUp("[ParentWSINumber]","tblSupplierParent","[SupplierName]='" &
Nz(Forms![Data Transaction Sets]!cboSupplierParentName.Column(1)) & "'")

This is the code on the text box that only loads once and doesn't change
when the combo box changes.
=DLookUp("[City]","[tblSupplierChild]","[WSInumber] = Forms![Data
Transaction Sets]!cboChildWSINumber")

I know they are pulling from difference combo box's but I dont think that
would make much of a difference. The combo box's have the needed field in
the query to populate them.

I notice that the one that is working has the combo box reference outside
the quotes of the argument, while the one that is not working has the combo
box reference inside the quotes. I haven't checked this out, but I suspect
that in the first case, Access can tell that the text box needs to be
recalculated when the combo box is changed, while in the second case it
can't.

See if rewriting the DLookup expression this way helps:

=DLookUp("[City]","[tblSupplierChild]",
"[WSInumber] = " & Str(
[Forms]![Data Transaction
Sets]![cboChildWSINumber]))

Note that I broke that onto multiple lines for clarity, but it would really
be all on one line.

Is the form [Data Transaction Sets] the same form that contains the text box
with this controlsource expression? If so, I would also leave out the full
form reference and just write:

=DLookUp("[City]","[tblSupplierChild]",
"[WSInumber] = " & Str([cboChildWSINumber]))

If just putting the control reference outside the quotes doesn't work, you
could explicitly requery the text box after the combo box is updated; e.g.:

'----- start of code -----
Private Sub cboChildWSINumber_AfterUpdate()

Me!YourTextboxName.Requery

End Sub
'----- end of code -----