form question

[mitchmcc]

I am new to Access (2003), and am having a basic problem that I think
should
be easy (but it's obviously not for me).

I have two tables, lets call them customers and invoices. The invoice
table has
the customer id in it.

I want to create a new form for data entry for invoices. Since every
invoice has
to have a customer, I want to make it easy for the data entry operator
to be able
to pick from the existing customers table.

So I create an Invoice form using the Autoform, and bring over all of
the items.
Then, in design view, I delete the customer id text box with the idea
of replacing
it with a list box that will be populated from the customers table.

I use the list box (LB) wizard to place down the LB and indicate that
it should come
from the customers table. I pick (for simplicity) only LastName and
FirstName.
Now, when I run the form, I see pretty much what I expected.

My problem is that I don't understand how the form gets the customer
id from the LB.
The LB wizard asks if you want to "find a record on the form based on
what value
the user picks", but I don't see that happening. The thing the user
picks is a text
value, so you cannot save it as the customer id.

I guess what I really want is that when the end user picks one of the
customer names
from the LB, the rest of the form gets populated with that customer's
record (which
is, of course, really based on the customer id associated with the
names on the LB).

I hope this is clear, and thanks in advance!

Mitch

 

[Jeff Boyce]

Mitch

A listbox works well when you have few items and some spare space on your
form. The combobox works better if space is at a premium and you have a
fair number of 'rows'.

Also, for either listbox or combobox, you can create a query that returns
all the information you want to see, then connect that to the list/combobox
as the RowSource and hide the columns with the other information (width =
0).

Then, in the AfterUpdate event of the combobox, set the value of each of the
unbound text controls to a specific column in the combobox ... e.g.,

Me!txtYourFirstDisplayedInformation = Me!cboYourCombobox.Column(x)

where "x" is the x-th minus 1 column returned by the query that feeds your
combobox (Column is zero-based).

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP