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

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
 
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: 242
  • error2.png
    error2.png
    51.1 KB · Views: 214
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") & "') "

...
...
 
arnelgp said:
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") & "') "

...
...
Click to expand...
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
 
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
 
arnelgp said:
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
Click to expand...
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 said:
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.
Click to expand...
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_
 
you do not need to add "IN" on Status because you only need 1 value for it.
 
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 :)
 
You must log in or register to reply here.

Similar threads

L
Continuous form with different rowsource in same combobox
Replies
4
Views
484
linux_s2002
L
tmd63
How to use a combo box to filter a forms data
Replies
3
Views
560
Uncle Gizmo
Uncle Gizmo
A
How to Properly Query and Search Multi-Valued Fields in a Form?
2
Replies
21
Views
1,296
MajP
MajP
A
Search box to find parts to add to job instead of a combo box
2 3
Replies
40
Views
2,011
Gasman
Gasman
A
Filtering on a crosstab queried subform
Replies
7
Views
728
DHookom
DHookom

Users who are viewing this thread

Total: 2 (members: 0, guests: 2)
Back
Top Bottom