lookup based on two fields...
lookup based on two fields...
(OP)
am new to access and need some help. Am helping a friend who is setting up a biological database that based on genus and specie can identify the specimen by another text value called elcode. Not all specimens have been given an elcode value. He wants volunteers to enter information into a database form that contains amongst other things - genus and specie , and he wants the elcode value to appear (if one exists)
I would think that this involves some kind of lookup but I don't know how to do it based on two fields.
I would think that this involves some kind of lookup but I don't know how to do it based on two fields.
RE: lookup based on two fields...
Dim SQL as string
SQL = "Select * From YourTable Where Somefield = '" & Me!Text1 & "' AND SomeOtherfield = '" & Me!Text2 & "';"
Note Me!Text1 is a text box on your form where they are keying in "genus" or whatever. Me!Text2 is for "specie"
What do you want to return when you find the values?
The results in a subform?
then it would be
Me![YOURsubform].Form.RecordSource = SQL
DougP
dposton@universal1.com
Ask me how Bar-codes can help you be more productive.
RE: lookup based on two fields...
Hope you understand what I'm saying.....will try one more time....
Volunteer calls up the form that will enter specimen data into a large database. They choose the genus and specie and by doing so, the elcode (special identifying text value) will pop up into the elcode field on the form along with some other data that is taken from the elcode table.
Is that clear?
Thanks for your help to this point! one more question...why the heck do they use "me"? for the query?
RE: lookup based on two fields...
Set db = CurrentDb
' SQL string.
SQL = "Select * From YourTable Where genus = '" & Me!genus & "' AND specie = '" & Me!specie & "';"
Set rst = db.OpenRecordset(SQL)
Me!elcode = rst!elcode
rst.Close
db.Close
---------------------
OK first part opens the database where the Genus an Specie are then returns the elcode
DougP
dposton@universal1.com
Ask me how Bar-codes can help you be more productive.