Can I limit a list box based on previous list box data

[dturtill]

Hi

I am trying to create a simple database so that I can keep track of printer parts. I have created a table for the manufacturer as well as a table of models the model’s table has the manufacturers field also.

I am trying to create a form for inputting the parts and part codes that are linked to the manufacturer and model, Can I set up a way were I have a list box of manufacturers and depending on what is selected then only displays the relevant models

This is just for adding new records i have filtered for older records

thanks

[Ratslinger]

Hello,

There are a variety of methods to accomplish what you want. Some are more difficult that others and require macros to utilize. Best to avoid macros when possible.

Using list boxes is fairly easy. You can get other records based upon a selection by using table filtering. For an explanation on table filtering see this post → Filter/Search with Forms (leveraging SubForms)

Now, using a list box & table filter you can select a specific manufacturer and display the models in a subform table control. Selecting one of these can then display your parts entry table grid which is linked to the printer model. Now this may be confusing at first.

In my answer on this post → Refresh table control near the end is Sample #3 ---InvoicePayments3.odb which may help. Examine the form Customer1WithListBox. Your printer manufacturer would be the "Customer’ section at the top. After selecting from list box, you select it with the button. This displays all open invoices for that customer (for you it would be models of the printer). Selecting one of these shows all payments for the invoice (this would be where you enter your part items for the specific model).

So what you end up with on the Base form is a main form (Manufacturer) with a sub form (Model linked to Manufacturer) with a sub sub form (part number linked to Model).

For sake of completeness, on this post → How to use user selected value from combobox1 in combobox2 ‘SELECT’ statement. are more complex methods using macros. Sample - Dogsjohnh009.odb uses multiple list boxes in a table grid (second is based upon the first). Very touchy. The second Sample #2 - ProgressiveListboxes.odb uses fields having progressive list boxes.

Edit 2018-05-23:

So as to give you a clearer picture, using the ProgressiveListboxes.odb sample mentioned above (has macros) I modified to your product - Printers & Models. Hardly anything to change - values in tables & labels on form. But this shows what you can do. Again, you should avoid macros unless you are able to code yourself. Using filter(s) is an easier way to go.

Sample — ProgressiveListboxes.odb

If this answers your question please tick the (upper left area of answer). It helps others to know there was an accepted answer.

[dturtill]

thanks this seems to be based on existing records I need it just for adding new ones is it the same principle

[Ratslinger]

If you examine the sample(s) mentioned you will see this is for adding/modifying records. If you want a form which only accepts new records, set the property of the form (Data tab) for Add data only = “Yes”.