Combo Box

D

Duane

Hello Group,

I have a newbie question about using a combo box as a lookup of four fields
from one table and insert them into another table.

I have a table in my database that is linked to another database. Of that
table there is information that I would like to insert into my table. Here
is a run down of what I have. The linked table has prisoner information
i.e. Number, LastName, Lock, Unit. This table is automatically updated at
the state level, whenever there is changed data. I have a database at the
Housing Unit level which keeps track of prisoner sanctions received from
misconducts. I would like to have a form with a combo box that uses the
linked table data and insert that data into my sanctions table, plus all the
other fields that I have on my form.

Is it possible to create a combo box control and bind it to those four
fields and then somehow insert that data into the sanctions table? I can do
it easily enough by only looking up one field, but can it be done with a
group of fields? I can seem to get it figured out.

I hope I have explained myself well enough!

Thanks in advance.
 
J

John Vinson

Hello Group,

I have a newbie question about using a combo box as a lookup of four fields
from one table and insert them into another table.
Click to expand...
I have a table in my database that is linked to another database. Of that
table there is information that I would like to insert into my table. Here
is a run down of what I have. The linked table has prisoner information
i.e. Number, LastName, Lock, Unit. This table is automatically updated at
the state level, whenever there is changed data. I have a database at the
Housing Unit level which keeps track of prisoner sanctions received from
misconducts. I would like to have a form with a combo box that uses the
linked table data and insert that data into my sanctions table, plus all the
other fields that I have on my form.

Is it possible to create a combo box control and bind it to those four
fields and then somehow insert that data into the sanctions table? I can do
it easily enough by only looking up one field, but can it be done with a
group of fields? I can seem to get it figured out.
Click to expand...

In general, it's a Bad Idea to store data redundantly like this.
Suppose the LastName or the Unit changes in the other database? Can
you be ASSURED that the change will be reflected in your table? In
general, you cannot! Is the other database readily accessible? Could
you perhaps just link to the prisoner information table in the other
database and use it directly, as the rowsource of the combo box and in
queries linked to the sanctions table by prisoner ID?

If not, then yes, you can do this, in a couple of ways. The simplest
would be to "push" the data into bound controls using VBA code in the
AfterUpdate event of the combo box. If you have textboxes txtNumber,
txtLastName, txtLock and txtUnit then you can use code like:

Private Sub comboboxname_AfterUpdate()
Me!txtNumber = Me!comboboxname.Column(0)
Me!txtNumber = Me!comboboxname.Column(1)

etc., using your actual combo box name. The subscript is a zero-based
index to the fields in the combo's rowsource query - that is, the
third column in the query will be Column(2).

Just be aware that when the state table is updated, your table will
NOT be updated to match; and you will need some procedure (an Update
Query comes to mind) to ensure that your table stays in synch.

John W. Vinson[MVP]
 
Top