Search for a field in a table and return a seperate field in the same record

[desk4tbc]

Hi guys,

I was hoping you could help me with a problem. I'm trying to search for a particular string that the user inputs against all records in a table and then use the first search result to return data in another field in the same record.

I'm trying to use sql to do the search but I don't know how to hold the records that match the search criteria and return information from a separate field.

DoCmd.RunSQL "SELECT PatientInsurance.[Patient ID] FROM PatientInsurance WHERE [Contact] LIKE '" & strSearch & "';"

Where PatientInsurance is a table and the contact is a field within that table. I want to return the field [Patient ID] from the first record found in that sql query. How would I go about storing that data in a variable?



EDIT: Changed the sql statement.

[RuralGuy]

Look at using DLookup() instead.

[desk4tbc]

Thanks again Rural. It hit me this morning that it would be an easier method rather than to set up a data array. Its what I get for staring at vba for 7 hours straight.

[RuralGuy]

Glad we could help.