Drop down list box dilemma

[Guest]

I’m taking a small course of Access and got to a dilemma with the teacher. I
have an ORDERS table related to my CLIENTS table by the *ClientID field and
to the PRODUCTS table by the *OrderID field. He insists that if I need to
define a new product and choose a client to associate with it in the products
form, I need to insert a text box with its origin from the ClientID and
manually input the client number (ID). He says that it’s not possible to have
a drop down list box to select the client alphabetically with the mouse and
make the name and address to automatically appear in this product form. I
find this method very uncomfortable because you have to remember or search
for the client’s ID… although I’ve seen a list box doing exactly what I want
in the Adamastor example from Access. I simply don’t know how to achieve it.
In reality I always get a conflict with the names and the Ids. The numbers
start replacing the names from the Clients table after I choose them from the
list box. What am I doing wrong? Thanks.

 

[John Vinson]

I’m taking a small course of Access and got to a dilemma with the teacher. I
have an ORDERS table related to my CLIENTS table by the *ClientID field and
to the PRODUCTS table by the *OrderID field. He insists that if I need to
define a new product and choose a client to associate with it in the products
form, I need to insert a text box with its origin from the ClientID and
manually input the client number (ID). He says that it’s not possible to have
a drop down list box to select the client alphabetically with the mouse and
make the name and address to automatically appear in this product form.

Your instructor is simply wrong, and I really have to question whether
you interpreted his assertion correctly (the alternative is to really
question his credentials for teaching the course!!!) For a proof of
the contrary position, open the Northwind sample database; look at the
Orders form, and the CustomerID combo box.

I find this method very uncomfortable because you have to remember or search
for the client’s ID… although I’ve seen a list box doing exactly what I want
in the Adamastor example from Access. I simply don’t know how to achieve it.
In reality I always get a conflict with the names and the Ids. The numbers
start replacing the names from the Clients table after I choose them from the
list box. What am I doing wrong? Thanks.

A listbox (or combo box) *GETS* its data from one table - its Row
Source - and *STORES* a single field (usually an ID) into a field in
another table, its Control Source.

For example, if you have a ClientID field in the PRODUCTS table (which
seems rather odd: might not each product be sold to many clients??),
you could have a Form based on PRODUCTS; the control bound to the
ClientID field would be either a Listbox or a Combo Box, with its
Control Source being the ClientID; its Row Source being a query
selecting client ID's and names from the Clients table; its Bound
Column being the position of the ClientID; and its ColumnWidth
property set to have the ID of zero width (so you can store it without
seeing it) and the client's name field a suitable width for display.

You can *DISPLAY* (but should not store) the client Address by
including the address field (or other fields) in the combo's
rowsource, and putting textboxes on the form to "pull" the address and
other info from the combo: set the textbox's Control Source to

=cboClientID.Column(n)

where cboClientID is the name of the combo box, and (n) the *zero
based* subscript of the field you wish to display.

John W. Vinson[MVP]

 

[Ron2005]

A slight alternative to the last part is that in the onchange event of
the combo box code can be inserted to push the same values into the
textboxes.
The result is the same but only one place to change code later on when
for somereason you end up adding a field to the combo.

in onchange
me.txtaddress = me.comboclientID.column(n)
me.txtcity = me.comboclientid.column(nn) etc.

In Access there always seems to be more than one way to do
something....

Ron

 

[Guest]

Thank you for the replies.

About the instructor’s knowledge, I talked again with him about this and
found out that is was trying not to confuse some of the people that are
taking this course too (a couple of them can barely move around the Windows
environment…). He then spoke to me about another action on Advanced Access
and if I was interested.

My products are unique and custom manufactured for each client. That is why
I need to create a form for clients and then in the products form search for
the right client to relate with it.

I’ll take a look at your posts and play around with my forms a little more.
Thanks again to you both. And merry xmas.