Need to Capture more than one field from a List Box

[Brad]

By using the Wizard, I have set up a List Box to provide the end-users with
an easy way to pick the product that they are working with.

The catch is that I would like to capture and save more than one field when
an item is selected in the List Box (such as both Product ID and Product
Description).

I came up with a way to do this with a little VBA code sitting behind the
List Box. Is this a good way to do this, or is there a better method?

Thanks for your advice.

 

[Jeff Boyce]

Brad

When you say "capture and save more than one field", I assume you mean
"store in a table". If this is how you've approached this, doesn't that
mean that you have the same data stored twice, once in whatever feeds the
listbox and once again where you've "captured and stored" it? That's rarely
necessary, and causes YOU considerably more programming work, since you'll
need to figure out how to keep the two separate copies in sync in case one
of the values changes.

An alternate approach would be to store the ID of the item selected in the
listbox. That way, you can 'look up' (use a query) the rest of the fields,
without having to (re-)store them.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Brad]

Jeff,

Thanks for the quick reply. You are right, I am considering storing more
than one field from a list box in another table. I appreciate your comments
about storing data in two places, and I agree with you. In this somewhat
strange situation, I need to capture two data fields at a specific point in
time and store them in a second table because they can change over time. I
was just wondering if there is a good way to do this with an Access List Box
seeing how the List Box Wizard provides a very nice way to store one field,
but not a second one. I wrote a very short VBA sub to handle what I want to
do, but I was wondering if there is a better way to accomplish this.

Thanks,

 

[Jeff Boyce]

Brad

If you've already written your VBA routines to handle this, you're done!
I'm not aware of a way to convince the (somewhat limited and linear) list
box wizard to do what you described.

And yes, if you have values that can change over time (a simplified example
is a current price list), that's one of those situations where it makes
sense to store the ID & the (then-current) price.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Brad]

Jeff,

Thanks for your help and for confirming that I am on the right track with
the VBA routine.