Bringing in data from another form (1 Viewer)

[inspmike]

I want to be able to enter a phone number of a client on my main form and have it go to another table and bring in the name,address,etc.. and populate the corresponding fields on my main form.
I'm sure there is a simple way to do this, I just can't seem to find it.
Thanks

 

[GMC]

Why not try creating a temporary recordset to do this??

------------------------------------------------------
Dim mydb as Database
Dim myrec as RecordSet
Dim criteria as String

On error goto err_egcode
'set up temporary workspace
Set mydb = dbengine.workspaces(0).databases(0)
Set myrec = mydb.OpenRecordSet("Customers",DB_OPEN_DYNASET)
'set criteria for finding relevant customer record
criteria = "[Telephone No] = " & "'" & me![t_tel_no] & "'"

'move to first record in Customers Table
myrec.movefirst
Do Until myrec.nomatch
myrec.findnext criteria
'Update fields on the main form with values from the Customers table
me![t_cust_name] = myrec![Cust Name]
me![t_add1] = myrec![Cust Add1]
..... etc
Loop

exit_egcode:
exit sub

err_egcode:
'some error handling routine here
resume exit_eg_code

--------------------------------------------------------

The above code assumes that the customer details are stored in a table called "Customers" and that telephone number is a unique field.
[N.B. The 'nomatch' loop is included for completeness]

Hope this helps

Grant