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
...
...
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 againyou 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") & "') " ... ...
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
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 nowtry:
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
Tried to make Location Code and Production Order status cbm selections, but I get a missing operator error.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.
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_