A different list in the same combo box based on another field 1

[Chummly66]

hey guys,

Not sure if this is something that can be done, but hopefully I can explain what it is I want to try.

Right now I have two combo boxes; "Make" and "Model".

depending on the value I select in "Make", the "Model" combo box displays only the items associated with that particular "Make". This works perfectly.

Now here's the monkey wrench. I have one specific customer, in which if this customer is who I am entering a record for, then the "Model" combo box would have a replacement set of values in its drop list.

So, in other words, for all customers but one, if I select Make = A, then my drop list for Models would show 1,2, or 3.

But if I am entering a record for this one specific customer, and put Make = A, then the dop list would change for Models, and it would show an alternate list of 4, 5, or 6.

Is that something that is possible?

Currently, the code for my dependent boxes are as follows:

Make - just runs a query listing all items from a table called IManufacturers.

Model - runs a query:
SELECT Models.StrModelName AS Expr1, Models.strNokiacode AS Expr3, Models.strCaresID AS Expr4, Models.LngManufacturerID AS Expr2
FROM Models
WHERE (((Models.LngManufacturerID)=[Forms]![Workorders]![cboManufacturer]))
ORDER BY Models.StrModelName;

Thanks for any help on this one guys!

Richard

 

[TheAceMan1]

How are ya Chummly66 . . .

. . . and what would be the difference in the SQL?

What you need is a common routine that would handle switching the SQL in the [blue]rowsource[/blue] of Model.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

 

[Chummly66]

Hey Ace,

I was wondering also about the use of DLookup. I am experimenting by adding a yes/no column titled "alternate" in my Model table, where the items on the alternate drop list are checked, and the rest aren't.

Basically, the difference maker is the customerID field.

If the custoemr ID = 2608 then I need the alternate drop list. Anything other than 2608, can use the standard drop list.

Maybe I can do something where if customerid = 2608 then only show the models with the "Alternate" checkbox set to true...

Do you think thats a good way to go?

Richard

 

[TheAceMan1]

Chummly66 . . .

Forget DLookup, your trying to populate a combo.

The yes/no is a good idea. With [blue]a copy of the same query[/blue] for model you just need to add the additional criteria of the yes/no and switch the rowsource as necessary . . .

BTW: why did you find it necessary to use Expr1 . . . Expr4 in the SQL of model?

See Ya! . . . . . .

 

[Chummly66]

Hey Ace,

Thanks for the feedback.

I have also two otehr locked text boxes that depending on what make and model are selected, two other boxes auto fill will information related to that make and model.

for example, I do cell-phone repairs, and if the Make is Nokia, then each model has a Nokia ID and a unique ID we assign. the two locked boxes autopopulate with the corresponding unique id and nokia id. However, if the make is not a Nokia, then only the textbox that shows the unique id populates.

Probably not the smoothest thing but it worked

Thanks again for the assistance- u da man!

Richard