Pulling in multiple fields from a drop down box

[sportsdream]

OK, here is my question. I want to be able to pull data into a form
using a drop down box. The table holds three pieces of information.
Client Name, Phone, and Location where Location is the primary key. I
want the drop down box to be Client Name and when choosen will populate
the entire record not just the name field.

Thanks for helping,

Michael Paniak

 

[Wayne Morgan]

You will find an example here:
http://www.mvps.org/access/forms/frm0005.htm

Basically, you have the unique ID field for the record as the bound column
of the combo box, set that column's width to zero if you desire to hide this
column (usually it is hidden). Next, use the AfterUpdate event of the combo
box to tell the form to search to the desired record using the unique ID
field as the search parameter. The combo box would be an unbound control,
probably in the form's header.

 

[sportsdream]

Thanks for your reply Wayne. I am new at this and found out about the
combo and list boxes, but I am not sure how to do this. When I create
the form I see the column widths etc, but it never populates the other
fields with the data I would like. I forogt to mention I am using
access 97.

Michael

 

[Wayne Morgan]

First, the Row Source for the combo box needs two fields in it, the unique
ID field and the Name that you want displayed. The "name" field would
probably be a calculated field with the first and last names concatenated
together. Don't use "Name" for the name of the field, Name is a reserved
word. To create a calculated field, the Row Source will need to be a query.

Once you have that, the code in the example will cause the form to move to
the record that has the ID field value from the combo box.

Example:
Row Source
SELECT [ID], [LastName] & ", " & [FirstName] AS PersonName FROM tblMyTable
ORDER BY [LastName] & ", " & [FirstName];

Set the combo box's Bound Column to 1 (the ID field) and set the Column
Width property to 0";1". Set the Limit To List property to Yes. The combo
box's Control Source should be blank (unbound).

In the After Update event of the combo box, use the code from the example
(modified slightly):
'****** Code Start *********
'Move to the record selected in the control
Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone
'ComboOrListboxName is the name of the combo box control
'The syntax assumes ID to be a Number data type
rst.Findfirst "[ID] = " & Me![ComboOrListboxName]
Me.Bookmark = rst.Bookmark
Set rst = Nothing
'******* Code End *********

 

[Katrina]

In the Onchange event procedure:
me.phone = me.combobox.column(1)
me.location = me.combobox.column(2)


Keep in mind - for this purpose the first field in the combo box is
column(0)

HTH
Katrina