Microsoft Access forms question

By mlieberman ·
For some reason I'm just blanking on how to do this and I feel like it should be really simple.

I have two tables. One is a table with the Model and Manufacturer and the other is a Device table with Name, Model, Manufacturer etc. I have the Model table foreign key linked to the Device table so that if a protocol change in the Model table happens (i.e. HP becomes Hewlett Packard) it cascades and also so that when someone is entering the data into the device table they make sure they spell everything correctly.

I have a form set up to enter data for the Device table such that I want to have Manufacturer and Model as combo boxes. I have the source set up correctly so only Manufacturers in the Model table show up, but I can't seem to get it so that when you pick a Manufacturer in the device table it only shows the models that exist for that manufacturer. My foreign keys are set up correctly because if I choose something like a Cisco model number but listed HP as the manufacturer it still throws an exception but I want to make sure only models for that chosen manufacturer show up.

How do I go about doing this?

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

Your model table needs to make the pairing explicit

by Tony Hopkinson In reply to Microsoft Access forms qu ...

Add ModelID (int autonumber) to model, make it the primary key. Then make devices ModelID and Name etc (no model or manufactuere columns !), put the Foreign key to Model ID, and then Model and Manufacturer become lookups, your data is normalised ie you are't storing model and manufacturer twice and access will fall into a Master/Detail pattern.

Collapse -

Still having some issues

by mlieberman In reply to Your model table needs to ...

I forgot to mention that the backend database is SQL Server Express 2005 whereas the front end I'm designing using Access 2003. I did what you said, I forgot to normalize them, but I'm still running into the same problem. I create two combo boxes that are supposed to each perform a lookup, but I'm a bit confused once I create the second one. I set it up so that it should be running a query such that it only shows models where when someone picks a manufacturer from the first combo box it shows only manufacturer-model pairs but it doesn't work. It comes up with nothing, or it shows both.

Rather I got it to work so that if you pick something like a Cisco model, it automatically picks Cisco as the manufacturer and visa versa if you pick Cisco as the manufacturer and you had an HP model picked it automatically switches it, but it doesn't only show when I choose Cisco only Cisco models.

Collapse -

I get it, it's your interface that's wrong

by Tony Hopkinson In reply to Still having some issues

You can't do all that from two combo lookups.

The queries would have to be select Distinct X from Model, and then all you could do is auto selecta and then use the values as a filter on model. That isn't going to be pretty though.

Add a radio group (select By Model, select By manufacturer) so the one combo is a select distinct the other uses the selection.

I'd be tempted to use a treeview for this, you coukld break it up even more then with a type of device (printer, PC, FAx etc.

Then I'd have a find function for Model.

Collapse -

Got it to work

by mlieberman In reply to Your model table needs to ...

It took me a while and I came across the solution by accident.

What you asked me worked for the first part. Where if I chose a certain manufacturer it would automatically change the model, but it would still show other models not for that manufacturer. However if you chose a model not for that manufacturer it would change the manufacturer.

What I was then able to do was create a basic VB script that after the Manufacturer would be updated it would run a query against that manufacturer. I had to do it a bit different than the normal way of doing it since the Manufacturer combo box was bound to the ModelID not the Manufacturer field. After several attempts at different methods I just added a nested SQL statement in VB script and just queried the Model table with the ModelID in order to get the Manufacturer and then list all the models. A bit roundabout I know, but it works.

Collapse -

Try it with data like this

by Tony Hopkinson In reply to Got it to work

HP Model1
HP Model2
Dell Model1
Dell Model3

What happens if you select Model1.

Related Discussions

Related Forums