Get data from a Combo Box on a form

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

WoodWP - 07 May 2007 21:44 GMT

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

Reply to this Message

ruralguy - 07 May 2007 23:20 GMT

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).

>I am very new to Access and VB, but I have had success in creating several
>Forms.
[quoted text clipped - 22 lines]
>
>I hope this is simple, sorry if my post was long

Signature

HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.

Reply to this Message

Steve - 07 May 2007 23:34 GMT

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
resource@pcdatasheet.com

>I am very new to Access and VB, but I have had success in creating several
> Forms.
[quoted text clipped - 27 lines]
>
> I hope this is simple, sorry if my post was long

Reply to this Message

WoodWP - 08 May 2007 14:28 GMT

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!

> 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
[quoted text clipped - 45 lines]
> >
> > I hope this is simple, sorry if my post was long

Reply to this Message