List box or combo box

[croth68]

I just started with access so I am access stupid. I am setting up a database
for training aids and have several tables. Tables consist of a training aids
table, dealers table, personnel table, shipping table, then a main entry
table. The idea is to be able to track training aids. I set up a form for the
main entry table and there are controls for each items contained in the other
tables. Here is the problem, the main table has fields for the training aid
id number and the training aid (name of the training aid). I created a list
box which pulls the training aid id numbers and the training aid names from
the training aid table. Unfortunately at this point the list box is solely a
list of inventory and the data has to be manually entered into the text boxes
on the form labeled training aid id and training aid. What I would like to do
is set up a double click function to automatically populate the text box
fields to reduce errors. I hope this is enough information. Thanks

 

[Rick Brandt]

I just started with access so I am access stupid. I am setting up a
database for training aids and have several tables. Tables consist of
a training aids table, dealers table, personnel table, shipping
table, then a main entry table. The idea is to be able to track
training aids. I set up a form for the main entry table and there are
controls for each items contained in the other tables. Here is the
problem, the main table has fields for the training aid id number and
the training aid (name of the training aid). I created a list box
which pulls the training aid id numbers and the training aid names
from the training aid table. Unfortunately at this point the list box
is solely a list of inventory and the data has to be manually entered
into the text boxes on the form labeled training aid id and training
aid. What I would like to do is set up a double click function to
automatically populate the text box fields to reduce errors. I hope
this is enough information. Thanks

What is normally done is to use the ListBox or ComboBox *instead of* a
TextBox. Just bind the ListBox to your field and delete the TextBox as you
won't need it. The selection in the ListBox will stay highlighted to
indicate which choice was made.

If you don't like the fact that the selection could be scrolled out of view
then use a ComboBox instead.

 

[croth68 via AccessMonster.com]

Thanks for the reply. When I do this it seems to only put a value in one
field of the target table. What I would like to do is populate both fields of
the target table using one list box or even a combo box. The list box shows
the training aid ID and Name of the training aid from the training aids table.
When I make a selection I want that information to be moved to the training
aid id field and name of training aid field on the master table.

 

[Rick Brandt]

Thanks for the reply. When I do this it seems to only put a value in
one field of the target table. What I would like to do is populate
both fields of the target table using one list box or even a combo
box. The list box shows the training aid ID and Name of the training
aid from the training aids table. When I make a selection I want that
information to be moved to the training aid id field and name of
training aid field on the master table.

While it is possible to do that via code it is a bad idea. You should store
ONLY the training aid ID in the table your form is bound to. Any other data
about the training aid that you want to *display* on your forms or reports
can be done via any number of lookup mechanisms and that would be the proper
way to do it. None of that requires that the name be redundantly stored.

In your case the ListBox or CombBox could use a hidden colum containing the
ID as its bound column (what gets stored in the table) and use a visible
column to display the name.

If for some reason you want the user to see the ID as well then just display
both columns in the ListBox. That is not an option for a ComboBox though.
For that you could use a TextBox with a ControlSource of...

Me.ComboBoxName.Column(n)

....where n is the zero-based index of the column you want the TextBox to
display.

 

[croth68 via AccessMonster.com]

Thanks, it makes more sense now, the problem I was struggling with is some
people would know the id and some people would know the name, I can bypass
that and have both but only store one value, ID would be the best to store.
The next thing I need to figure out is how to set up a form so someone can
put data together on a report any way they choose.