Combobox value for multiple filter criteria in a Query (1 Viewer)

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:32
Joined
May 7, 2009
Messages
19,175
you put the Name of the original query on the recordsource first.
 

zulu100

Member
Local time
Today, 16:32
Joined
Nov 3, 2021
Messages
54
When I do that I get the error attached
referring to setting the recordsource to strSQL
Code:
Me.RecordSource = strSQL

I can open the form. The error occurs when updating the combo box
 

Attachments

  • Error.png
    Error.png
    59.3 KB · Views: 104

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:32
Joined
May 7, 2009
Messages
19,175
i forgot the last ")".
see if it is fixed.
Code:
Private Sub cbm_DepartmentSelection_AfterUpdate()
Dim strSQL As String
strSQL = "SELECT [dbo_KonfAir DRIFT$Sales Header].[Bill-to Name], [dbo_KonfAir DRIFT$Item].[Item Category Code], " & _
    " dbo_x_Production_View.Prod_Ordrenr_, [dbo_KonfAir DRIFT$Production Order].No_, " & _
    " dbo_x_Production_View.Salgsordrenr_, tbl_Status.Start, " & _
    " [dbo_KonfAir DRIFT$Item$db97a7b0-945f-4089-8891-b9dd5651c769].Quality, tbl_Status.Status_Status, " & _
    " [dbo_KonfAir DRIFT$Production Order].Status, [dbo_KonfAir DRIFT$Production Order].Quantity AS Nummer, " & _
    " (Val([Nummer])) AS Antal, [dbo_KonfAir DRIFT$Production Order].[Ending Date] AS [Slut Dato], " & _
    " [dbo_KonfAir DRIFT$Production Order].[Source No_] AS [Vare nummer], " & _
    " [dbo_KonfAir DRIFT$Item].Description AS Beskrivelse, " & _
    " [dbo_KonfAir DRIFT$Production Order].[Location Code] AS Lokationskode, " & _
    " tbl_Status.Medarbejder_1Numeric, tbl_Status.Medarbejder_2Numeric, " & _
    " tbl_Status.Medarbejder_3Numeric, tbl_Status.Medarbejder_4Numeric, tbl_Status.Medarbejder_5Numeric, " & _
    " tbl_Status.Medarbejder_6Numeric, tbl_Status.Medarbejder_7Numeric, tbl_Status.Medarbejder_8Numeric, " & _
    " tbl_Status.Medarbejder_9Numeric, tbl_Status.Medarbejder_10Numeric, tbl_Status.Medarbejder_11Numeric, " & _
    " tbl_Status.Medarbejder_12Numeric, tbl_Status.Medarbejder_13Numeric, " & _
    " tbl_Status.Medarbejder_14Numeric, dbo_x_Production_View.Planlagt_afsend " & _
    " FROM ((([dbo_KonfAir DRIFT$Production Order] " & _
    " INNER JOIN tbl_Status ON [dbo_KonfAir DRIFT$Production Order].No_ = tbl_Status.StatusID) " & _
    " INNER JOIN ([dbo_KonfAir DRIFT$Item] " & _
    " INNER JOIN [dbo_KonfAir DRIFT$Item$db97a7b0-945f-4089-8891-b9dd5651c769] " & _
        " ON [dbo_KonfAir DRIFT$Item].No_ = [dbo_KonfAir DRIFT$Item$db97a7b0-945f-4089-8891-b9dd5651c769].No_) " & _
        " ON [dbo_KonfAir DRIFT$Production Order].[Source No_] = [dbo_KonfAir DRIFT$Item].No_) " & _
    " LEFT JOIN dbo_x_Production_View " & _
        " ON [dbo_KonfAir DRIFT$Production Order].No_ = dbo_x_Production_View.Prod_Ordrenr_) " & _
    " LEFT JOIN [dbo_KonfAir DRIFT$Sales Header] " & _
        " ON dbo_x_Production_View.Salgsordrenr_ = [dbo_KonfAir DRIFT$Sales Header].No_ "

strSQL = strSQL & _
        " WHERE ((tbl_Status.Status_Status)=0) " & _
        " AND (([dbo_KonfAir DRIFT$Production Order].Status)=3) " & _
        " AND (([dbo_KonfAir DRIFT$Production Order].[Location Code])=IIf([Forms]![frm_ctn_List]![txt_CountrySelection]=1,'DK','LIT'))) "

    
    If Me.cbm_DepartmentSelection.ListIndex <> -1 Then
        strSQL = strSQL & _
        " AND ([dbo_KonfAir DRIFT$Item].[Item Category Code]) IN (" & Choose(Me.cbm_DepartmentSelection, "31410,31420", "31210,31220", "32210,32220", "32100,32110", "2121") & ") "
    End If
    
strSQL = strSQL & "ORDER BY [dbo_KonfAir DRIFT$Production Order].[Ending Date];"
    Me.RecordSource = strSQL

Me.Requery

End Sub
 

zulu100

Member
Local time
Today, 16:32
Joined
Nov 3, 2021
Messages
54
Hmmm...
Still not there yet
If the record source is set to the initial Query and I open the form, it open up correctly. But when I change combo box value I get the attached error.
If I set the record source to strSQL I get "Query must have at least one destination field"
I have another combo box in the form with reference to the same query. Could that be the problem ?
I have tried to illustrate on an attached image.
The combo box value for Country selection is passed to a the text field "txtCountrySelection"
 

Attachments

  • Screendmp.png
    Screendmp.png
    233.1 KB · Views: 107
  • error2.png
    error2.png
    51.1 KB · Views: 106

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:32
Joined
Feb 19, 2002
Messages
42,987
1. there is no benefit to using embedded SQL rather than a querydef when the SQL is static
2. You have a design problem. Your repeating group should be a child table rather than 14 numbered fields in a single table.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:32
Joined
Feb 19, 2002
Messages
42,987
Once you build the SQL string in code, put a stop on the line that puts it in the RecordSource. Print it to the debug window. Copy and paste it into the QBE in SQL view and run it. That will help you to clean up typos. If you can't type SQL cleanly in VBA, you should really build it with the QBE. There is nothing "better" about using embedded SQL and in fact, for most people, it is more difficult because it is far harder to debug as you are finding out.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:32
Joined
May 7, 2009
Messages
19,175
you change the middle part to this:
Code:
...
...
strSQL = strSQL & _
        " WHERE ((tbl_Status.Status_Status)=0) " & _
        " AND (([dbo_KonfAir DRIFT$Production Order].Status)=3) " & _
        " AND (([dbo_KonfAir DRIFT$Production Order].[Location Code])='" & IIf([Forms]![frm_ctn_List]![txt_CountrySelection]=1,"DK","LIT") & "') "

...
...
 

zulu100

Member
Local time
Today, 16:32
Joined
Nov 3, 2021
Messages
54
you change the middle part to this:
Code:
...
...
strSQL = strSQL & _
        " WHERE ((tbl_Status.Status_Status)=0) " & _
        " AND (([dbo_KonfAir DRIFT$Production Order].Status)=3) " & _
        " AND (([dbo_KonfAir DRIFT$Production Order].[Location Code])='" & IIf([Forms]![frm_ctn_List]![txt_CountrySelection]=1,"DK","LIT") & "') "

...
...
Hi again
I get a data type mismatch error.

tbl_Status.Status_Status is a number
dbo_KonfAir DRIFT$Production Order].Status is a number
dbo_KonfAir DRIFT$Production Order].[Location Code] is a short text field
dbo_KonfAir DRIFT$Item].[Item Category Code] is a short text field

Code:
strSQL = strSQL & _
        " WHERE ((tbl_Status.Status_Status)=0) " & _
        " AND (([dbo_KonfAir DRIFT$Production Order].Status)=3) " & _
        " AND (([dbo_KonfAir DRIFT$Production Order].[Location Code])='" & IIf([Forms]![frm_ctn_List]![txt_CountrySelection] = 1, "DK", "LIT") & "') "
    
    If Me.cbm_DepartmentSelection.ListIndex <> -1 Then
        strSQL = strSQL & _
        " AND ([dbo_KonfAir DRIFT$Item].[Item Category Code]) IN (" & Choose(Me.cbm_DepartmentSelection, "31410,31420", "31210,31220", "32210,32220", "32100,32110", "2121") & ") "
    End If
    
strSQL = strSQL & "ORDER BY [dbo_KonfAir DRIFT$Production Order].[Ending Date];"
    Me.RecordSource = strSQL
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:32
Joined
May 7, 2009
Messages
19,175
try:
Code:
strSQL = strSQL & _
        " WHERE ((tbl_Status.Status_Status)=0) " & _
        " AND (([dbo_KonfAir DRIFT$Production Order].Status)=3) " & _
        " AND (([dbo_KonfAir DRIFT$Production Order].[Location Code])='" & IIf([Forms]![frm_ctn_List]![txt_CountrySelection] = 1, "DK", "LIT") & "') "
    
    If Me.cbm_DepartmentSelection.ListIndex <> -1 Then
        strSQL = strSQL & _
        " AND ([dbo_KonfAir DRIFT$Item].[Item Category Code]) IN (" & Choose(Me.cbm_DepartmentSelection, "'31410','31420'", "'31210','31220'", "'32210',32220", "32100','32110'", "'2121'") & ") "
    End If
    
strSQL = strSQL & "ORDER BY [dbo_KonfAir DRIFT$Production Order].[Ending Date];"
    Me.RecordSource = strSQL
 

zulu100

Member
Local time
Today, 16:32
Joined
Nov 3, 2021
Messages
54
try:
Code:
strSQL = strSQL & _
        " WHERE ((tbl_Status.Status_Status)=0) " & _
        " AND (([dbo_KonfAir DRIFT$Production Order].Status)=3) " & _
        " AND (([dbo_KonfAir DRIFT$Production Order].[Location Code])='" & IIf([Forms]![frm_ctn_List]![txt_CountrySelection] = 1, "DK", "LIT") & "') "
   
    If Me.cbm_DepartmentSelection.ListIndex <> -1 Then
        strSQL = strSQL & _
        " AND ([dbo_KonfAir DRIFT$Item].[Item Category Code]) IN (" & Choose(Me.cbm_DepartmentSelection, "'31410','31420'", "'31210','31220'", "'32210',32220", "32100','32110'", "'2121'") & ") "
    End If
   
strSQL = strSQL & "ORDER BY [dbo_KonfAir DRIFT$Production Order].[Ending Date];"
    Me.RecordSource = strSQL
It kind of semi works now :)
I still have the record source set to the original query but removed the criteria set there for the above fields. Otherwise I still get no destination fields.
However the DepartmentSelection filter does work for for some of the selections which is kind of strange. I get records returned on cbm choice 1 and 3, and I know that there should be records on cbm choice 4, but here it is empty.
Just to be sure that it reacts on the embedded query I added a filter criteria to cbm choice 3 and the additional records were found.

The IIF statement for the Location Code doesnt work, maybe this should be made with the IN condition as well.
 

zulu100

Member
Local time
Today, 16:32
Joined
Nov 3, 2021
Messages
54
It kind of semi works now :)
I still have the record source set to the original query but removed the criteria set there for the above fields. Otherwise I still get no destination fields.
However the DepartmentSelection filter does work for for some of the selections which is kind of strange. I get records returned on cbm choice 1 and 3, and I know that there should be records on cbm choice 4, but here it is empty.
Just to be sure that it reacts on the embedded query I added a filter criteria to cbm choice 3 and the additional records were found.

The IIF statement for the Location Code doesnt work, maybe this should be made with the IN condition as well.
Tried to make Location Code and Production Order status cbm selections, but I get a missing operator error.
When the actual code of an cbm selection after update event is placed in another cbm after update like this is. Could that work, or should each "If" statement be placed in the after update event of the respective Combo box ?


Code:
strSQL = strSQL & _
        " WHERE ((tbl_Status.Status_Status)=0) "

        If Me.cbm_ProductionOrderStatus.ListIndex <> -1 Then
        strSQL = strSQL & _
        " AND (([dbo_KonfAir DRIFT$Production Order].Status) IN (" & Choose(Me.cbm_ProductionOrderStatus, "0", "1", "2", "3", "4") & ") "
        End If
    
    If Me.cbm_CountrySelection.ListIndex <> -1 Then
        strSQL = strSQL & _
        " AND (([dbo_KonfAir DRIFT$Production Order].[Location Code]) IN (" & Choose(Me.cbm_CountrySelection, "'DK'", "'LIT'") & ") "
        End If
        
    If Me.cbm_DepartmentSelection.ListIndex <> -1 Then
        strSQL = strSQL & _
        " AND ([dbo_KonfAir DRIFT$Item].[Item Category Code]) IN (" & Choose(Me.cbm_DepartmentSelection, "'31410','31420'", "'31210','31220'", "'32210','32220','32230'", "'32110','32120'", "'21210'") & ") "
    End If
    
strSQL = strSQL & "ORDER BY [dbo_
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:32
Joined
May 7, 2009
Messages
19,175
you do not need to add "IN" on Status because you only need 1 value for it.
 

zulu100

Member
Local time
Today, 16:32
Joined
Nov 3, 2021
Messages
54
Actually there are values from 0 to 4 which represents different status of productions orders. I only need to work with status 3, correct, but I was just trying to change something to see if I could get it working :)
 

Users who are viewing this thread

Top Bottom