lookup based on two fields...

[Guest_imported]

am new to access and need some help. Am helping a friend who is setting up a biological database that based on genus&nbsp;&nbsp;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&nbsp;&nbsp;amongst other things - genus and&nbsp;&nbsp;specie , and he wants the elcode value to appear (if one exists)<br><br>I would think that this involves some kind of lookup but I don't know how to do it based on two fields.

 

[DougP]

Any time you need to match 2 fields use the &quot;AND&quot; statement<br>Dim&nbsp;&nbsp;SQL as string<br>SQL = &quot;Select * From YourTable Where Somefield = '&quot; & Me!Text1 & &quot;' AND SomeOtherfield = '&quot; & Me!Text2 & &quot;';&quot;<br><br>Note Me!Text1 is a text box on your form where they are keying in &quot;genus&quot; or whatever. Me!Text2 is for &quot;specie&quot; <br><br>What do you want to return when you find the values?<br>The results in a subform?<br><br>then it would be <br>&nbsp;Me![YOURsubform].Form.RecordSource = SQL <p>DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.

 

[Guest_imported]

he wants the value placed into the form where the person is choosing the genus and specie values from the list box.<br><br>Hope you understand what I'm saying.....will try one more time....<br><br>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.<br><br>Is that clear?<br><br>Thanks for your&nbsp;&nbsp;help to this point! one more question...why the heck do they use &quot;me&quot;? for the query?

 

[DougP]

&nbsp;&nbsp;&nbsp;&nbsp;Dim db As Database, rst As Recordset, SQL As String<br>&nbsp;&nbsp;&nbsp;&nbsp;Set db = CurrentDb<br>&nbsp;&nbsp;&nbsp;&nbsp;' SQL string.<br>&nbsp;&nbsp;&nbsp;&nbsp;SQL = &quot;Select * From YourTable Where genus = '&quot; & Me!genus & &quot;' AND specie = '&quot; & Me!specie & &quot;';&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;Set rst = db.OpenRecordset(SQL)<br>&nbsp;&nbsp;&nbsp;&nbsp;Me!elcode = rst!elcode<br>&nbsp;&nbsp;&nbsp;&nbsp;rst.Close<br>&nbsp;&nbsp;&nbsp;&nbsp;db.Close<br>---------------------<br>OK first part opens the database where the Genus an Specie are then returns the elcode<br> <p>DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.