Help Regarding Forms

M

mjcxp

I need help to do this....

I have a table that has employee information such as address and other
stuff. One of the fields it has is Location Number.

I have a different table that has Location Number, City, State, Address,
Phone # Etc.

I have a form with the first table that has the employee information. Here
is my problem, I want to type the Location Number in the Form and have it
automatically retrieve the City, State, Address, and Phone # from the table
and add it to the form in the fields.

Is this possible????

Thanks
 
O

Ofer

On the after update event of the Location Number field you can open a
recordset that will return all the values for the Location Number, it's
better then dlookup, because that way you access the table record just once.

Dim MyDB as database, MyRec as recordset
Set MyDB=codedb()
Set MyRec=MyDB.openRecordset("Select * From [Location Table Name] Where
[Location Number] = " & Me.[Location Number Field Name In The Form])
If MyRec.eof then
msgbox "No address for this number"
else
me.City = MyRec!City
me.State= MyRec!State
me.Address= MyRec!Address
me.[Phone #]= MyRec![Phone #]
End If
 
M

mjcxp

Thank You, worked perfectly!!!

Ofer said:
On the after update event of the Location Number field you can open a
recordset that will return all the values for the Location Number, it's
better then dlookup, because that way you access the table record just once.

Dim MyDB as database, MyRec as recordset
Set MyDB=codedb()
Set MyRec=MyDB.openRecordset("Select * From [Location Table Name] Where
[Location Number] = " & Me.[Location Number Field Name In The Form])
If MyRec.eof then
msgbox "No address for this number"
else
me.City = MyRec!City
me.State= MyRec!State
me.Address= MyRec!Address
me.[Phone #]= MyRec![Phone #]
End If


mjcxp said:
I need help to do this....

I have a table that has employee information such as address and other
stuff. One of the fields it has is Location Number.

I have a different table that has Location Number, City, State, Address,
Phone # Etc.

I have a form with the first table that has the employee information. Here
is my problem, I want to type the Location Number in the Form and have it
automatically retrieve the City, State, Address, and Phone # from the table
and add it to the form in the fields.

Is this possible????

Thanks
Click to expand...
Click to expand...
 
Top