Help with Combo Box

[JAJansenJr]

I have two combo boxes which display both a manufacturer ID and manufacturer
name. The first box has column 2 bound (manufacturer ID) and the second box
has column 3 bound (manufacturer name). The first combo box successfully
selects and delivers the manufacturer ID to a textbox on the data entry form
(and from there into the mfgr ID field in the table); likewise the second
combo box delivers the manufacturer name to a textbox on the data entry form
(and from there into the mfgr name field in the table). So everything is
working, but it seems silly to have two combo boxes when one should be able
to do the job, i.e. the ID and name both show in the combo box. I can live
with what I have but it wastes space - is there a way to just use one combo
box?

 

[Dennis]

Yes you can have 1 combo box because you can reference each column using
something like this

TextBox1 = cmbComboName.Column(2)
TextBox2 = cmbComboName.Column(3)

 

[John Spencer]

If the Manufacturer ID and Manufacturer Name are always paired, then your
design is flawed. You should have a table of manufacturers with
ManufacturerID and ManufacturerName fields. Which it seems you might since
you have comboboxes with this information.

I would suggest one combobox on the form and storing the Manufacturer id in
your table linked to this form. Add a control to the form with its source set to
= ManufacturerIDCombobox.Column(2)

This should automatically display the value in the third column of the
combobox. (Columns are numbered starting with zero so Column(2) is the third
column.)

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[JAJansenJr]

This sounds like what I want, but I'm totally new to Access 2007 and I'm not
sure how to operate the Visual Basic machinery to get a simple subroutine
associated with the form associated with the combo box. Can you aim me in
the right direction?

 

[JAJansenJr]

I was uncertain about how to integrate the VB code with the Combo control. I
found that I needed to invoke the "after update" event and select the coding
option, which opened a VB subroutine. I inserted the code

ManufacturerID = Combo55=Column(1)
ManufacturerName = Combo55 = Column(2)

I ran the program and, initially, it didn't work. But I then noticed I had
not opened "Security Warning - Options" and selected "Enable This Content".
Once that was done everything worked perfectly.

As to the relational aspects of the database I am primarily only using one
table and am not seeking to create a relational database. The volume of data
(relatively low) is such that I don't see any advantage to going with a
relational database. A relational database makes sense when there are
storage issues for a very large amount of data. Frankly I'm more comfortable
with a flatfile database because the programming issues seem more
straightforward.

Thanks to all who posted and offered suggestions.