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]

Norie

The answer to above is Yes

I have also used the ComboBox Wizard to create a manual version of the list of product descriptions (this works apart from not being "triggered" from the ProductID ComboBox).

Row Source is as follows:

SELECT [tblMasterStockCodesDescriptions].[ProductID], [tblMasterStockCodesDescriptions].[ProductDescription] FROM tblMasterStockCodesDescriptions;

Does this help?

Bernard

 

[Norie]

The answer to above is Yes

To all the questions?

 

[TheToeOfPooh]

Norie

Sorry for being so vague.

I have tired the following code

Private Sub ProductID_AfterUpdate()

On Error Resume Next

If ProductID >0 Then

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

End If
End Sub

When this runs the following Error Message appears
Run time error '-2147352567(80020009)'
You can't assign a value to this object

Does this shed any light?

Bernard

 

[Norie]

Which line is the error occuring on?

 

[TheToeOfPooh]

Norie

Yes - the problem was with my spacing.

The following works as you advised (as you said)
"Seems it was only a space that was
causing the problem"

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

Thank you for correcting me. ray:

That you have spent so much time helping me resolve this is outstanding.


Bernard

 

[jsi]

Hi
Include the column that you want o display in the textbox, in the combobox, then reference tha appropriate column of the combobox.
i.e. set the rowsource of the combo to
SELECT[tblMasterStockCodesDescriptions].[ProductID], [tblMasterStockCodesDescriptions].[ProductDescription]
FROM tblMasterStockCodesDescriptions;

Then you can do 1 of 2 things.
1 display the second column of Prod Desc in the combobox (if you don't really need to display the id
OR
Create your textbox and set the control source to:
=cboProductID.column(1)
(note column 1 references the second column (since the column index starts at 0

HTH

PS quick point:
If Product ID is a Primary key in your table, it should be an Autonumber field and should never be used to display to 'normal users' it is used solely to ID records and join tables. If you require a text ProductID for business purposes, this should not be your PK but rather a standard Unique indexed field. [soapbox off]