dlookup

  • Thread starter Thread starter Brian
  • Start date Start date
B

Brian

I have a table - tblProducts with
prodNum (text)
prodDesc (text)

A form populates a table tblOrders with
ordProdNum (text)
ordodDesc (text)

I want the product description to come up with the entry of the product number

The code:

Private Sub ordProdNum_AfterUpdate()
On Error GoTo Err_ordProdNum_AfterUpdate

Dim strProdNum As String

' Evaluate filter before it's passed to DLookup function.
strProdNum = "prodNum = " & Me!ordProdNum

' Look up product numbers assign it to ordProdDesc control.
Me!ordProdDesc = DLookup("[prodDesc]", "[tblProducts]", strProdNum)

Exit_ordProdNum_AfterUpdate:
Exit Sub

Err_ordProdNum_AfterUpdate:
MsgBox Err.Description
Resume Exit_ordProdNum_AfterUpdate

End Sub

When the ordProdNum control updated, I get a data type mismatch error

I know I can accomplish the same thing with a query, but am getting nowhere
with that as well

How can I get the product description field to populate be entering the
product number?
 
I have a table - tblProducts with
prodNum (text)
prodDesc (text)

A form populates a table tblOrders with
ordProdNum (text)
ordodDesc (text)

I want the product description to come up with the entry of the product number

The code:

Private Sub ordProdNum_AfterUpdate()
On Error GoTo Err_ordProdNum_AfterUpdate

Dim strProdNum As String

' Evaluate filter before it's passed to DLookup function.
strProdNum = "prodNum = " & Me!ordProdNum

' Look up product numbers assign it to ordProdDesc control.
Me!ordProdDesc = DLookup("[prodDesc]", "[tblProducts]", strProdNum)

Exit_ordProdNum_AfterUpdate:
Exit Sub

Err_ordProdNum_AfterUpdate:
MsgBox Err.Description
Resume Exit_ordProdNum_AfterUpdate

End Sub

When the ordProdNum control updated, I get a data type mismatch error
Click to expand...

Since ProdNum is of Text datatype, you need the syntatically required
quotemarks:

strProdNum = "prodNum = '" & Me!ordProdNum & "'"

For clarity, that's

strProdNum = "prodNum = ' " & Me!ordProdNum & " ' "

Do note that the product description should NOT be stored in the orders table;
it's redundant. The only reason to store it is if the description changes from
time to time and you want to store the description as of the point in time of
the order. If you just want to display the description on the order form, no
code and no DLookUp is needed at all; just use a textbox with a control source

=ordProdNum.Column(1)

to display the second (it's zero based) column of the combo's rowsource query.
 
Brain,

I actually see two issues but let's deal with the first issue which is your
DLookup. Your expression should read...

strProdNum = "[prodNum] = " & " ' " & Me![ordProdNum] & " ' "

....because it is a text field.

The second issue I see is your table set-up. You actually store the Product
Number, usually a Primary Key in the Order Detail table. One Order can have
many Details (Products).

tblOrder
oOrderID (PK)

tblOrderDetail
odOrderID (FK - link to tblOrder)
odProductID (FK - link to tblProducts)

tblProducts
pProductID (PK)
pDescription

Perhaps you should take a look at the Northwind database and how the Orders
tables with Products is set up. The way you have it set up, either you will
duplicating the OrderID, plus other information OR maybe you will only ever
have one line item per Order?

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm
 
Back
Top