Form not cooperating

[evega6686]

I have a Form in Access 2003 which contains a List box which pulls ID's from the Main table for the form. What I want to do is have the three Combo boxes on the form to be the filter for the list box. I have tried several ways of getting this to work.
The first ComboBox uses the following VBA:

Private Sub Form_Load()
'sets the list for the first Combobox
Me.Combo142.RowSource = "SELECT [CCC].[CCode] " & " FROM CCC ORDER BY CCode;"
Me.Combo142.Requery

End Sub
Private Sub Combo142_AfterUpdate()
'Clears the second Combobox when the value of the
'first Combobox has changed
Me.Option123 = Null
End Sub
Private Sub Option123_GotFocus()
'sets the list for the second Combobox
'based on the selection in the OptionsButtons
Me.Combo142.RowSource = "SELECT [CCAH].[Year_Month] " & " FROM CCAH " & " ; "

End Sub

The second Combo:
SELECT ID, [PGCode] & " " & [PGCodeDesc] AS Expr1 FROM PGCodes ORDER BY PGCodes.PGCode;

The third Combo:
SELECT PDCodes.ID, [PDCodes] & " " & [PDCodeDesc] AS Expr1 FROM PDCodes ORDER BY PDCodes.PDCodes;

I need to use those Combo Boxes for the List box to filter. I tried setting up simple queries, but if I enter the name of the queries in the List Box Row Source they don't show up unless I click on Design View for the Form and then return to Form view. As if I need to activate the query before it will display data.

I have worked on this for several days without much success...HELP.

 

[MarkK]

This problem is called "cascading combos." I'm pretty certain that if you search using those terms you'll find an overwhelming amount of very specific assistance already available.

 

[evega6686]

I did try using the Cascading Combos method without much success. :banghead:

 

[evega6686]

I did try to break it down into a simple chunks by using a query, these two queries will give me the data I want but they don't activate automatically. I have to go into Design View on the form and then Form View and the list box will be populated.

List Box Row Source:
SELECT LastNoInSeries.[CC ID] FROM LastNoInSeries;

To transform the ID to the Code I need is this query, Name=LastInSeries:
SELECT MasterCCID.CType, PGCodes.PGCode, PDCodes.PDCodes
FROM PDCodes INNER JOIN (PDCodes INNER JOIN MasterCCID ON PGCodes.ID = MasterCCID.PGCodeID) ON PDCodes.ID = MasterCCID.PDCodeID
WHERE (((MasterCCID.CType) Like Forms![CID Gen]!CType) And ((PGCodes.ID) Like Forms![CID Gen]!PGCodeID) And ((PDCodes.ID) Like Forms![CID Gen]!PDCodeID));

...the query which pulls it into the LstBox, Named=LastNoInSeries:

SELECT DISTINCT MasterCID.[CID]
FROM MasterCID INNER JOIN LastInSeries ON (MasterCID.CType = LastInSeries.CType) AND (MasterCID.PGCode = LastInSeries.PGCode) AND (MasterCID.PDCode = LastInSeries.PDCodes)
ORDER BY MasterCID.[CID];

...but I have to go into Design View to activate the list box to pull the data

 

[vbaInet]

Requery the combo box after changing its Row Source.

 

[evega6686]

This problem is called "cascading combos." I'm pretty certain that if you search using those terms you'll find an overwhelming amount of very specific assistance already available.

Thanks but I did try that already.

 

[vbaInet]

I can only see one place where you used Requery. I'm saying that you should requery after each instance you change the Row Source.

So:
* Change Row Source
* Requery
* Change another Row Source
* Requery that control

 

[evega6686]

vbaInet: Is this what you mean?
Private Sub PDCode_GotFocus()

Me.List181.Requery

End Sub
Private Sub PGCode_GotFocus()

Me.List181.Requery

End Sub

Or should I add that to the CCode Combo Box?

Private Sub Form_Load()
'sets the list for the first Combobox
Me.Combo142.RowSource = "SELECT [CCC].[CCode] " & " FROM CCC ORDER BY CCode;"
Me.Combo142.Requery

End Sub
Private Sub Combo142_AfterUpdate()
'Clears the second Combobox when the value of the
'first Combobox has changed
Me.Option123 = Null
End Sub
Private Sub Option123_GotFocus()
'sets the list for the second Combobox
'based on the selection in the OptionsButtons
Me.Combo142.RowSource = "SELECT [CCAH].[Year_Month] " & " FROM CCAH " & " ; "

End Sub

 

[vbaInet]

Here's an example:

Private Sub Option123_GotFocus()
    'sets the list for the second Combobox
    'based on the selection in the OptionsButtons
    Me.Combo142.RowSource = "SELECT [Year_Month] FROM CCAH;"
[COLOR="Blue"]    Me.Combo142.Requery[/COLOR]
End Sub

And I also amended your SQL statement.

But where are you applying criteria in Combo142? In the query itself, is there a reference to the parent combo box?

 

[evega6686]

vbaInet:

In the queries I have
WHERE (((MasterCCID.CType) Like Forms![CID Gen]!CType)
as a call to Combo142, which is called CType.

Thanks for helping me out.

 

[vbaInet]

If you have that in the Query you don't need to alter the Row Source anymore. So here's what you do:

1. After Update event of Combo1: Requery Combo 2, Requery Combo3
2. After Update event of Combo 2: Requery Combo3

That's the only thing you need to do in those events. Remove what you have in the Got Focus events and requery as explained above. Combo1 never changes because it doesn't need to, unless it's dependent on something else.

 

[evega6686]

Combo1 does change based on Option buttons at the top.

Private Sub Form_Load()
'sets the list for the first Combobox
Me.Combo142.RowSource = "SELECT [CCode].[CCode] " & " FROM CCode ORDER BY CCode;"
Me.Combo142.Requery

End Sub
Private Sub Combo142_AfterUpdate()
'Clears the second Combobox when the value of the
'first Combobox has changed
Me.Option123 = Null
Me.Combo142.Requery

End Sub
Private Sub Option123_GotFocus()
'sets the list for the second Combobox
'based on the selection in the OptionsButtons
Me.Combo142.RowSource = "SELECT [CCAH].[Year_Month] " & " FROM CCAH " & " ; "
Me.Combo142.Requery

End Sub

I tried your suggestion of:
1. After Update event of Combo1: Requery Combo 2, Requery Combo3
2. After Update event of Combo 2: Requery Combo3

But i still need to go into Design View (don't have to run the query) and switch back to Form view for the ListBox to become active and display data.

 

[vbaInet]

Let me re-iterate:

* Remove all (not some but all) the code you have in the Got Focus event of all three combo boxes
* And requery in the After Update event as advised
* If Combo1 changes based on option button then you want to Requery Combo1 in the Ater Update event of the option button.

Looks like MarkK is back... he might have something to add.

 

[MarkK]

No, nothing to add, looks like this is headed in the right direction.

I don't use cascading selection much. I use a popup search form that takes a SQL statement, and allows substring text searches in all fields, re-running the search for every keystroke.

Sometimes I prefer to use the Combo_Click() event if the combo is not bound, rather than AfterUpdate(), but they always both run under all the same circumstances, so it doesn't matter.

I agree to not use GotFocus() at all for this purpose.

 

[evega6686]

I did follow your recommendations:

* Remove all (not some but all) the code you have in the Got Focus event of all three combo boxes
* And requery in the After Update event as advised
* If Combo1 changes based on option button then you want to Requery Combo1 in the Ater Update event of the option button.

....as follows:
Private Sub Option123_AfterUpdate()
'sets the list for the second Combobox
'based on the selection in the OptionsButtons
Me.Combo142.RowSource = "SELECT [CCAH].[Year_Month] " & " FROM CCAH " & " ; "
Me.Combo142.Requery

End Sub
Private Sub PDCode_AfterUpdate()
Me.List181.Requery
End Sub
Private Sub PGCode_AfterUpdate()
Me.List181.Requery
End Sub

but it didn't give me the results as desired.

 

[vbaInet]

Not quite, you're doing some bits so I'll explain again.

I'll start from the top so please follow this step-by-step:

1. Delete all the code you have in the After Update event, Got Focus event of all three combo boxes and the option button. Basically, don't just delete what's inside, delete the Private Sub and End Sub as well.

2. Go to the After Update event of the option button and requery Combo1 (or whatever you've called the first combo box)

3. In the After Update event of Combo1, requery Combo2 and Combo3

4. In the After Update event of Combo2, requery Combo3

 

[MarkK]

Well, checking for potentially obvious errors, these two After_Update() events do exactly the same thing. That can't be right . . .

Private Sub PDCode_AfterUpdate()
Me.List181.Requery
End Sub
Private Sub PGCode_AfterUpdate()
Me.List181.Requery
End Sub

Presumably updating different controls should have different effects, right? Also, keep in mind when reporting an error, this description . . .

didn't give me the results as desired

. . . provides us with nothing to troubleshoot. Other useless reports include, didn't work, failed, no dice, kaput. When you report a problem, report observable facts ABOUT THE PROBLEM. Knowing that the outcome differs from your desires is important too, but in a different, non-troubleshootable way
Cheers,

 

[evega6686]

Maybe if I somehow embedded the following SQL into the Row Source for the List Box it may solve all my problems:

SELECT DISTINCT MasterCID.CID
FROM MasterCID
This should be the WHERE statement:
SELECT MasterCID.CType, PGCodes.ID, PGCodes.PGCode, PDCodes.ID, PDCodes.PDCodes
FROM PDCodes INNER JOIN (PGCodes INNER JOIN MasterCID ON PGCodes.ID = MasterCID.PGCodeID) ON PDCodes.ID = MasterCID.PDCodeID
WHERE (((MasterCID.CType) Like [Forms]![CID Gen]![CType]) AND ((PGCodes.ID) Like [Forms]![CID Gen]![PGCodeID]) AND ((PDCodes.ID) Like [Forms]![CID Gen]![PDCodeID]));

 

[MarkK]

Steady vbaInet. That's a lot of bold text and bright colors, and yeah, I know, I used ALL CAPS in my post too, but . . .

 

[MarkK]

it may solve all my problems

This might be called "silver bullet thinking." I think all your problems will be solved if you take many small incremental steps, each one solving a small piece of this complex problem. Stay patient and focused, and read the details of the posts people are making. Progress is being made . . .