Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

lookup based on two fields...

lookup based on two fields...

lookup based on two fields...

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.

RE: lookup based on two fields...

Any time you need to match 2 fields use the "AND" statement
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


Ask me how Bar-codes can help you be more productive.

RE: lookup based on two fields...

he wants the value placed into the form where the person is choosing the genus and specie values from the list box.

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...

    Dim db As Database, rst As Recordset, SQL As String
    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
OK first part opens the database where the Genus an Specie are then returns the elcode


Ask me how Bar-codes can help you be more productive.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close