Critera for DLookup

[RBoshears]

I have this formula in the ControlSource section of a textbox within a form. I am attempting to populate the box with field information based on input from a combobox earlier in the form. The first two pieces of this formula works, but I can't get it to work using the criteria portion. Am I using faulty syntax somewhere?

=DLookUp(" [ItemDescription] ","tblNewItemSKUs2004 = ' " & [New Item UPC] & " ' ")

 

[bat17]

Looks OK at first glance, I would check the value being returned by the ComboBox as often the first column is a numeric ID field which is hidden and the Descriptive Column which you see is not the one retuned by the control but the Number ID.

 

[Russell Hauf]

DLookup needs:

-The field you want returned
-The table you are looking in
-The "WHERE" criteria

Your example seems to be missing the table. So it should be something like:

=DLookUp("[ItemDescription]","tblNewItemSKUs2004", "[UPC] = '" & [New Item UPC] & "'")

You also don't want spaces before or after the single quotes, or before or after [ItemDescription].

Hope this helps,

Russell

 

[RBoshears]

One More thing

Thanks for your help!! It now populates with a value. Now I need it to automatically change when the criteria changes. Here is the updated formula. Do I need a change here or an adjustment in one of the properties? Thanks again.

=DLookUp("[ItemDescription]","tblNewItemSKUs2004","[tblNewItemSKUs2004]![UPC]=Form![New Item UPC]")

 

[Russell Hauf]

If you want it to change when "New Item UPC" changes on your form, then put code in the AfterUpdate event of "New Item UPC" on your form. Also, you don't need to have [tblNewItemSKUs2004]![UPC], just [UPC], since you've already told DLookup what table to look in. So in a nutshell, you'd probably want code something like this:

Private Sub New_Item_UPC_AfterUpdate()
    Me.txtSomeTextBox = DLookup("[ItemDescription]", "tblNewItemSKUs2004" _
        , "[UPC]=" & Me.New_Item_UPC)
End Sub

If you do this, delete the Control Source of the textbox as this will now populate it.

Hope this helps,

Russell

 

[RBoshears]

Fantastic!!! Thanks for your help, It works great. ray: