Multiple columns in combo box

[Guest]

How can I have a combo box with 4 columns in it, send the 4 fields for that
record to separate fields in a form? To reiterate: I have Name, address,
city and zip in separate fields. Can I pull all 4 up in a combo box, and
when I select the correct record, have the different fields populate the
respective slot in the form? If so, how? I don't yet know code so if that is
part of the answer, please be explicit as to how and where to enter it.
Thanks.

 

[Guest]

Hi Rich,

You can do this using the After_Update event procedure for the combo box.
Suppose the name of this control is cboCustomers, and the names of the fields
on your form are Name, Address, City and ZipCode, respectively. The column
count property should be set to match (ie. 4). The After_Update event
procedure would look like this:

Option Compare Database
Option Explicit

Private Sub cboCustomers_AfterUpdate()
On Error GoTo ProcError

Me.Name = cboCustomers.Column(0)
Me.Address = cboCustomers.Column(1)
Me.City = cboCustomers.Column(2)
Me.ZipCode = cboCustomers.Column(3)


ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cboCustomers_AfterUpdate..."
Resume ExitProc
End Sub


I recommend that you consider a different field name instead of "Name" in
your table. The reason is that Name is considered a reserved word. You should
avoid naming anything in Access with a reserved word. Here is a handy list of
reserved words for your reference:

Problem names and reserved words in Access
http://allenbrowne.com/AppIssueBadWord.html


Allen has a very nice (free) utility that you can download to test your
database for the use of reserved words in field and table names (I don't
believe this version catches the use of reserved words for the names of
controls on forms and reports).

Since you are located in Redmond, I'd like to extend an invitation to you to
attend the SeattleAccess and/or Pacific NW Access Developer's Group (PNWADG)
meetings. Both meetings are held in Building 34 of Microsoft's main campus in
Redmond. The Seattle Access Group is geared more towards beginners to
intermediate level, and meets on the second Tuesday evening of each month. I
am the featured speaker tomorrow night, and my topic involves becoming
familiar with VBA code:

http://www.seattleaccess.org/

The PNWADG is a more advanced group, and meets on the third Tuesday evening
of each month in the same building:

http://www.pnwadg.org/


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[BruceM]

You shouldn't store the Address information unless there is a specific
reason for doing so. For instance, a shipping address may need to be stored
as the address to which something was shipped rather than the current
address, if the two are different. On the other hand, a billing question
needs to go to the current address, not the one at the time of the billing.
There are at least a couple of possible approaches. In order to know which
one to suggest, it will help if you describe the combo box row source table.
Do the name records have a primary key field that won't change (autonumber,
for instance)? Are FirstName and LastName separate fields in a table that
are combined in a query (preferred), or is the full name stored in a single
table field? Do you need the address information to appear in the combo box
drop-down list, or just the name?