This seems a bit hard (at least to me) (1 Viewer)

[atol]

Hello,

I know it has been said a lot on this topic but I could not find exactly this situation in my search.
I have a query, which pulls results from many tables. Based on that same query, I have a form with three cascading combos and a list box. The list box however should show results based on the respective “selects” in ALL three combos together.
I have a code to cascade the three combos, but I face the challenge to find out how to make the code “fire” the accurate information to the listbox based on all the three combo results.
Additionally, my list box contains a field displaying numbers. I am summing those numbers (based on the selections in the combos); so if wrong data shows up, that means wrong sum is calculated as well.
Here are the codes for the three combos; the third one is the challenging one.
--------------------------------------------------------------------------
Private Sub cboBor_AfterUpdate()
On Error Resume Next
cboFacil.RowSource = "Select Distinct qryCB.FN " & _
"FROM qryCB " & _
"WHERE qryCB.BN = '" & cboB.Value & "' " & _
"ORDER BY qryCB.FN;"
End Sub
--------------------------------------------------------------------------

Private Sub cboFacil_AfterUpdate()
On Error Resume Next
cboInv.RowSource = "Select Distinct qryCB.IN " & _
"FROM qryCB " & _
"WHERE qryCB.FN = '" & cboF.Value & "' " & _
"ORDER BY qryCB.IN;"
End Sub

Private Sub cboInv_AfterUpdate()
listposdetail.RowSource = "Select qryCB.Cu, qryCB.SumOfP " & _
"FROM qryCB " & _
"WHERE qryCB.IN= '" & cboInv.Value & "' " & _
"ORDER BY qryCB.Cu;"
listsumbal.RowSource = "Select Sum(qryCB.SumOfP) " & _
"FROM qryCB " & _
"WHERE qryCB.IN = '" & cboInv.Value & "';"

End Sub

This third private sub is what I struggle with. I believe the code should somehow combine the codes of the previous two combos. It should somehow pull only the records based on all three combos, and also sum those records up (as in the second part of the same third sub). Not sure if I am way off topic, but hope I hear back from you folks.

Any help is greatly appreciated!

Rgds.
Atol

 

[Oldsoftboss]

Maybe posting a cut down version of the Db will help.

 

[atol]

Here is an example....If you select UK, you will see that there is a city Oxford, but same name exists in USA, so in order to destingush I need to put a third combo "continent"... Thanks !

Atol