Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

lookup based on two fields...

Status
Not open for further replies.
Guest_imported

Guest_imported

New member
Joined
Jan 1, 1970
Messages
0
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.
 
Sort by date Sort by votes
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.
 
Upvote 0 Downvote
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?
 
Upvote 0 Downvote
&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.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

applestoapples
  • Locked
  • Question Question
Create duplicate rows based on Qty field
Replies
7
Views
634
Andrzejek
Andrzejek
breezett93
  • Locked
  • Question Question
Function that find all controls on a form and puts their tab index in numerical order
Replies
19
Views
2K
dhookom
dhookom
DeRochier
  • Locked
  • Question Question
Conditional formatting on Yes/No fields
Replies
2
Views
608
Axworthy
Axworthy
Kim296
  • Locked
  • Question Question
FILTER SUBFORM USING MAIN FORM FIELDS- VBA
Replies
8
Views
1K
Andrzejek
Andrzejek
priac
  • Locked
  • Question Question
Create a Duplicate of an Existing Record in Microsoft Access but leave some fields blank.
Replies
7
Views
2K
Andrzejek
Andrzejek

Part and Inventory Search

Sponsor

Back
Top