DLookUp in simple(sic) terms

[TheToeOfPooh]

From reading through all the other posts I realise we have a Possible Error Between Chair And Keyboard here -

On a Form called frmBookIN
I have a ComboBox called cboProductID
Source Table is tblMasterStockCodesDescriptions
Source Field is ProductID

This bit works fine using a Row Source of
SELECT[tblMasterStockCodesDescriptions].[ProductID]FROM tblMasterStockCodesDescriptions;

What I now need to do is
When the user selects a ProductID
on the same form
in another Text(?) Box called txtProductDescription
display the corresponding Product Description
Source Table is same table as the ComboBox
Source Field is ProductDescription

Any help would be very appreciated.Please assume minimal knowledge here in grey, overcast Worcester.

Bernard

 

[TheToeOfPooh]

The following gives me an #Error in the Product Description box
with or without a selection being made in the ID ComboBox.

Could someone please see if my syntax is corect:
If relevant - All Field formats within Table are Text

=DLookUp("ProductDescription]","tblMasterStockCodesDescriptions","[ProductID]=forms![frmBookIN]![ProductID")

Bernard

 

[Norie]

Is what you posted exactly what you have?

Try this

=DLookUp("[ProductDescription]","tblMasterStockCodesDescriptions","[ProductID]"=Forms![frmBookIN]![ProductID])

 

[TheToeOfPooh]

The following just gives me the ProductDescription of the first item in the Table no matter which product code I enter:


=DLookUp("[ProductDescription]","tblMasterStockCodesDescriptions","[ProductID]=[ProductID]")

But at least it's better than #Error to look at

Bernard

 

[TheToeOfPooh]

Norie

Unfortunately your example results in a blank box - any other options\sugestions please?

Bernard

 

[Norie]

Whoops what I posted was slightly wrong. Try

=DLookUp("[ProductDescription]","tblMasterStockCodesDescriptions","[ProductID]= " & Forms![frmBookIN]![ProductID])

I'm assuming ProductID is a number.

 

[TheToeOfPooh]

Norie

Case of Sod's Law - the ProductID has to be Text.

Can you adapt accordingly please?

Bernard

 

[Norie]

Shouldn't be a problem just need to add quotes:

Try

=DLookUp("[ProductDescription]","tblMasterStockCodesDescriptions","[ProductID]='" & Forms![frmBookIN]![ProductID] & "'")

BTW it's ' rather than " that has been added.

 

[TheToeOfPooh]

Norie

Still no joy - Just a blank txt box where the ProductDescription should be

I have even tried adding the following code to the frmBookIN

Private Sub ProductDescription_BeforeUpdate(Cancel As Integer)
On Error Resume Next
If ProductID > 0 Then
ProductDescription = DLookup("[ProductDescription]", "tblMasterStockCodesDescriptions", "[ProductID]= ' " & Forms![frmBookIN]![ProductID] & "'")
End If

End Sub


My apologise for turning this into a bit of a quest, but any toher tips please?

Bernard

 

[Norie]

Have you checked the the code is working?

Is Forms![frmBookIN]![ProductID] returning the expected value?

I don't mean the DLookup, just that it is being triggered.

Also try Msgbox with the DLookUp.