Can someone please check my cascading combo box code for me

[10 Gauge]

I know that this was working just a few days ago and I haven't been anywhere near this part of the form recently but now it's not working correctly and I can't figure it out.



It's a basic cascade. The selection from one box dictates what is displayed in the box below.

However for some reason it's no updating properly and when I change the value of one record it changes the other records.....

This is for makes and models of trucks / heavy equipment.

For example if I select Chevrolet from the combo box 1 and then Silverado from combo box 2 it's all fine and dandy.

If I go to the next record and select Caterpillar from comb box 1 and then 401 from combo box 2 it clears the previous records model (Silverado).

Here's my row source for box 1:

SELECT DISTINCT Makes.MakeName, Makes.MakeID FROM Makes ORDER BY Makes.MakeName;

And then I also have an On Change VBA event:

Me!ModelName = Null
Me!ModelName.Requery

Here's the row source for box 2:

SELECT DISTINCTROW Models.ModelID, Models.ModelName FROM Models WHERE ((Models.MakeID) Like forms![Asset Details]!MakeName) ORDER BY Models.ModelName;

Does anything there stick out as the culprit?

TIA

[10 Gauge]

I have attached a copy of the DB. Open form "Asset Details".

Then on one of the pieces of equipment try changing around the make/model and then change another one to a different make/model and see what happens.

Thx.

[10 Gauge]

Hrmmmm.... Now when I change a value it changes every other record to the same value instantly.....

What should my Control Source be set to?

[thhui]

From the link
https://www.accessforums.net/forms/c...ble-11788.html

It seems that the same approach from Maximus will help you.

Change the code as he points out.

Private Sub Grade_AfterUpdate()
Me.Rank.Requery
Me.Rank = Me.Rank.ItemData(0)
End Sub

You invoke the Change Event while he use the AfterUpdate Event
and same logic should also apply to invoke the form current event.

[10 Gauge]

Hrmmm I tried changing it to use the afterupdate event and it's acting quite the same however it's no changing all of my table records for both Make & Model instead of just the Make with a null Model field.

I want to say the problem lies within my row source code. I can't figure out why the combo box is changing data outside of the selected entry..... Somehow my code isn't specifying the changes to be made only on the selected record.

Once I choose a Make from the menu, all of the subsequent records when I click on Model show the options for the Make I last selected regardless of what record it was on.

What should my control souce be set too?

[thhui]

The logic for the afterupdate or change event is that
after you change on the 1st record,
the comboBox sql rowsource also changes for other records
but using the rowsource of 1st record.

So other records will use the 1st record rowsource due to your change in 1st record.

For example, if you change to 1 in 1st record rowsource.
When you enter the 2nd record.
the sql becomes where makename = 1 instead of what you want is the current record's makename.

That's why I suggest you should also fire the current event on entry of other records.

[thhui]

Me.Rank = Me.Rank.ItemData(0)
must be added

as your comboBox after requery would become null.

You can also try
Me.Rank = Me.Rank
so that it remains the same as beforehand.

[10 Gauge]

Ok. We've almost got it....

I added Me.ModelName.Requery to the Form_Current event and now it displays correctly for each record as I navigate them on the form and everything now WORKS.

The only problem left is that I am using a Split Form for easier record navigation and overview. The datasheet part of the form now only shows the ModelName for any record that has a selection of the same Make, if it's different the ModelName shows blank. But, if I navigate to any record that has a blank ModelName it will then show the correct ModelName upon selection of the record, and any record in the datasheet matching the newly selected records MakeName now appear, and any the others disappear.

I now need a way to hold and display all of the ModelNames regardless of the selected records MakeName.

I know I make no sense, I'm sorry, lol. Hopefully you understand what's happening here.

[thhui]

The subform may also be requeried to update the link which is now the AssetNo or MakeNo.