ComboBox options

[dylcon]

Hello, I am bulding a form and I have lookups in my table, so the form has appropriate listboxes. What I want is for the data within the listboxes to be conditional on what is in the other fields. For example, the first field in the form is 'Part Number'. The 'Part Number' only has one Customer, so once this field is entered, I would like for the listbox showing the 'Customer' to be completed with the respective customer.

I am relatively new to Access. Thanks in advance!

[pbaldy]

Like this?

http://www.baldyweb.com/CascadingCombo.htm

[dylcon]

Yes, this is what I want, but when I try to do alter for my circumstances, it is not working. Any ideas what I could be doing wrong?

[pbaldy]

Since I can't see what you've tried, no.

[dylcon]

Inventory Record.mdb

Ok. Here is my file. I am trying to populate the CustomerName combobox based on which part is entered. It is all in the Incoming Form.

[pbaldy]

For starters, you've got a lookup field, so the field is numeric, not text. Second, you're referring to the wrong combo when you set the row source and such. I think the column count is also off. Personally I don't use lookup fields:

http://access.mvps.org/access/lookupfields.htm

[dylcon]

So should I delete all of my lookups and reestablish relationships without them?

[pbaldy]

Oh, you're free to use them if you want. I just said I wouldn't. If you use them, you just need to be aware of the "gotchas".

[dylcon]

So you would rather have the datasheets have ID numbers that you would constantly have to look up?

[rzw0wr]

dylcon,
Lookup field are OK. sometimes. Usually never.
I will use them is special cases. Very special.
However you must be aware of the down falls.
Usually you don't want the relationship.
And you have to store some bit of data in that table you may not want or need.

Combo boxes are far easier to work with than lookup fields.
They do not require a relationship and you do not have to store any data.

If you can make a lookup field you can make a combo box.

Best practice in most cases, forget lookup field and learn to work with combo boxes.


Dale

[dylcon]

Thanks Dale, but I thought that a lookup essentially was a combobox. How do I use a combobox without building a relationship?

[rzw0wr]

dylcon,
Lookup fields are a combo box.
Combo boxes show multiple data.
Lookup fields and what you THINK combo boxes are, are not the same. Confusing.

In a form, in design view, from the the design tab, select the Combo box icon.

From the wizard select what data you want.
IF the form is bound or unbound your first option is.
Data from another table.
Just like a lookup field.

IF the form is bound to a table/query it shows another option to find a record on your form. (Good for searching and populating your form)

Select the option you want and follow the wizard.

If the data is not on your form you need to find someway to search for the data from a query/table.

Play with it in a test form awhile.
Ask questions if you hit a hard spot.

EDIT:
The query that is made by the wizard appears in the Row Source of the combo.
The WHERE clause works great here to filter. Used there all of the time.

Dale

[dylcon]

How would I use a combobox in the field of the table without creating a relationship via lookups?

[rzw0wr]

Not sure I understand.
Combo boxes are for looking up data, usually. Like from a list.
Forget the table and the table field.
See the above post.

Dale

[dylcon]

Thanks Dale