how to query table and add value to a variable

[Daniel M]

I need to query a table to select * where serialnumber = '1234' then i need
to take the productid and move it to a variable in the code to use. can
someone tell me the best way to do this?

 

[Rob Parker]

Hi Daniel,

I'm not sure if this is the best way, but here's one way you can do it:

Dim myProductID As Long
or, if productid is text:
Dim myProductID As String

myProductID = dLookup("ProductID","YourTableName","serialnumber = 1234")

If serialnumber is actually a text field, rather than a number, this will
need to be:

myProductID = dLookup("ProductID","YourTableName","serialnumber =
'1234'")

HTH,

Rob

 

[Evi]

Will all items with serial number 1234 have the same productID?
Is SerialNumber a number or a text field?
Evi

 

[Daniel M]

The serial number will always have the same productID. both fields are text
fields. I could use the dlookup but i still think i need a little help. I
have this..

Dim prodlookup As String
prodlookup = DLookup("productID", "producttable", "serialnumber = combo2")

This is ok if i am pulling from my combo2 control but i would rather pull
from a different table.

I will have a table full of just serial numbers (one column, multiple rows),
i will have another table with serial numbers and productIDs. i want to
lookup the productID of serial from the other table. This is a temp table and
will be created and destroyed constantly.