Creating autolookups in a form?

[Mark Pilbeam]

I have three fields (amongst others)
Customer ID, First name, and Surname, on a form which is attached to table
customers.
Users find a customer ID in the customer ID field, using a lookup.
Can an autolookup fill up the firstname, and surname fields in a form?
Secondly, if so, how can I do it.
3. Do I have to use a query for this sort of operation?

Mark

 

[Guest]

If the Lookup in the customerID field is a combo box, then you can either
have the First Name and Surname as column 2 and 3 in the combo and then set
the control source property of two text boxes on your form to
=ComboBoxName.Column(1)
=ComboBoxName.Column(2)

or assuming customerID field is numeric, set the control source property of
the two text boxes to
=DLookUp("FirstName","YourTable","CustomerID = " & Me.CustomerID)
=DLookUp("Surname","YourTable","CustomerID = " & me.CustomerID)

 

[John Vinson]

I have three fields (amongst others)
Customer ID, First name, and Surname, on a form which is attached to table
customers.
Users find a customer ID in the customer ID field, using a lookup.
Can an autolookup fill up the firstname, and surname fields in a form?
Secondly, if so, how can I do it.
3. Do I have to use a query for this sort of operation?

Mark

One idea to consider is to get away from the idea of a "lookup field".
It's not the only way to see data, nor even the best!

If you base your Form on the table, you can use the Combo Box wizard
to create an unbound combo box, using the option "use this combo to
find a record". This will *jump to* the actual record in the table,
opening it for viewing, for editing, or whatever.


John W. Vinson[MVP]

 

[Mark Pilbeam]

OK, but how would I resolve my problem.
I want my user to be able to select a User_ID from a table of existing names
and ID's.
Once the User_ID has been selected, I then want fields in the form
automatically filled in, based on the selected User_ID.
Any ideas?

Mark

 

[Douglas J Steele]

I believe John was assuming you had a form that would display the
information you wanted, and that you could use the Combo Box Wizard to build
code that would automatically open that form with the correct information.

Another option is to "hide" all of the data you want in the recordset that
provides the Row Source for the combo box. In the combo box's AfterUpdate
event, write code that takes the values of those hidden columns and puts
them in text box controls on your form.

Me.txtUserName = Me.cboMyComboBox.Column(1)
Me.txtUserPhoneNumber = Me.cboMyComboBox.Column(2)

The above assumes that the name is the 2nd column and the phone number is
the 3rd: column numbering starts at 0.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)