Pulling info from query or table into form

[kirnavi]

Sorry if this is a stupid question, but hopefully that means there is an easy answer.

I'm creating a form and when someone updates a field, I want another field to look up information and populate. For example, a form where someone types in a Social Security number and then that person's name, DOB, etc populate some fields.

It would be better if I could pull from a query instead of from the table because it takes a few fields to make a unique identifier.

Thanks for any help.

 

[cranebill]

If you arent entering new data you can use a combo box with multiple columns. Hide all except the one you are using as that particlar field then in the after_update event of the combo box you can have it update the other fields by using something like:

Me!Name = Me!YourComboBox.Column(1)

Remember however if you use multiple columns when referencing them in this way it starts with 0

 

[cranebill]

Of for the combo box use table/query as your row source and you can build the SQL anyway you want

 

[kirnavi]

The problem with using combo boxes is that I want the user to be able to add if they need to (I only used SSNs as an example).

Let me explain what I have a little further. I have one table (table 1) that is created by a query, which pulls directly from an Oracle db. Then I have another table (table 2), which is populated via this form. The data on these two tables will not necessarily always match (e.g. table 1 has persons A, B, and C while table 2 has persons A, B, C, and D).

What I would like is for the user who is entering data (for table 2) into the form to see some info from table 1. So the user enters an ID number and if there's a match, that person's name pops up. If no match, no info pops up. However, later after Table 1 is refreshed, then I'd like to see the name pop up in the form (when the user revisits the form to update some other info).

Thanks!

 

[randy700]

How about....
Me.txtName = dlookup("LastName", "tblEmployees", "ssn = '" & me.txtSSN & "'")


Randy