Get data from a Combo Box on a form

[Guest]

I am very new to Access and VB, but I have had success in creating several
Forms.
This is what I want to do...

I have a combo box on my form that displays a listing of Streets names from
a table named "Wells_Roads". This table has a 'key' ID field along with 3
data fields
The first field contains the complete road name i.e. "New Harbor Rd"
the second field contains just the name itself i.e. "New Harbor"
the third field contains just the suffix "Rd".

From selecting the desired record from the dropdown list I want to populate
3 Textboxs or ultimately 3 database fields in another database.

Its easy enough to get the data from any SINGLE column field but is there a
way to 'bind' the combo box to more than one?

Can I use some SQL to set the values to variables which in turn I can set my
required field names from?

I havn't used any SQL from within my VB code yet, but I was thinking it
could be done... since My combo box can tell me what record ID I want... Its
just a matter at that point of being able to get the 3 individual field
values after that..

I hope this is simple, sorry if my post was long

 

[ruralguy via AccessMonster.com]

I believe your fields are redundant because they can always be put together
but if must, a ComboBox has a Column property such that Me.ComboBoxName.
Column(2) will reference the 3rd column, visible or not. Using your
ComboBoxName of course. The column index is zero based so the 1st column
would be column(0).

 

[Steve]

You need the rowsource of the combobox to include all four fields. Set the
Bound column property of the combobox to 1. Set the Column Width property of
the combobox to 0;1.5;0;0. The combobox will display "New Harbor Rd" but
assign "ID" to the combobox field. For the three textboxes, use these
expressions:
Me!NameOfTextbox1 = Me!NameOfCombobox.Column(1)
Me!NameOfTextbox2 = Me!NameOfCombobox.Column(2)
Me!NameOfTextbox3 = Me!NameOfCombobox.Column(3)
You can put these expressions in the AfterUpdate event of the combobox.
TextBox1 will contain "New Harbor Rd"
TextBox2 will contain "New Harbor"
TextBox3 will contain "Rd"

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[Guest]

Thats perfect! Thank you. After continuing to look around I found the
..column(#) trick... But I like the idea of surpressing the redundant columns.
And Yes I realize that this is a rather redundant exercise, however it isn't
the concatenation I want to avoid it is the more difficult (at least for me)
parsing of a road name that could be a single word such as I-95 or four or
more words like New Harbor Road West. So if I can fill 3 fields with one
click that is going to save a LOT of time! Thanks again!