Hello!
I have a form that links 2 tabels together. (tbl_Shipment_Log and tbl_Sign_Out_Log) They are linked by the field Ref_No (Auto Number Primary Key) I'm trying to make a search box where as people can search for various fields, then have it populate into a subform with the results.
The problem I'm having is that I can search for information, and get a return; however, the return is only information from one table. It doesn't show all of the informaiton from the second table that is linked. (I hope this makes sense) I'll paste my code below. If anyone has any suggestions or solutions, I would greatly appreciate it.
Private Sub Search_Click()
Const cInvalidDateError As String = "You have entered an invalid date."
Dim strWhere As String
Dim strError As String
strWhere = "1=1"
' If Order_Number
If Not IsNull(Me.Order_Number) Then
'Create Predicate
strWhere = strWhere & " AND " & "tbl_Shipment_Log.[Order_Number] = " & Me.Order_Number & ""
End If
' If Shipment Type
If Not IsNull(Me.Shipment_Type) Then
'Add the predicate
strWhere = strWhere & " AND " & "tbl_Shipment_Log.[Shipment_Type] = " & Me.Shipment_Type & ""
End If
' If PO Number
If Nz(Me.PO_Number) <> "" Then
'Add it to the predicate - exact match
strWhere = strWhere & " AND " & "tbl_Shipment_Log.PO_Number = '" & Me.PO_Number & "'"
End If
' If Signed Out By
If Nz(Me.Signed_Out_By) <> "" Then
'Add it to the predicate - exact match
strWhere = strWhere & " AND " & "tbl_Sign_Out_Log.Office_Sign_Out = '" & Me.Signed_Out_By & "'"
End If
' If Shipment Date From
If IsDate(Me.ShipmentDateFrom) Then
' Add it to the predicate - exact
strWhere = strWhere & " AND " & "tbl_Shipment_Log.[Shipment_Date] >= " & GetDateFilter(Me.ShipmentDateFrom)
ElseIf Nz(Me.ShipmentDateFrom) <> "" Then
strError = cInvalidDateError
End If
' If Shipment Date To
If IsDate(Me.ShipmentDateTo) Then
' Add it to the predicate - exact
strWhere = strWhere & " AND " & "tbl_Shipment_Log.[Shipment_Date] <= " & GetDateFilter(Me.ShipmentDateTo)
ElseIf Nz(Me.ShipmentDateTo) <> "" Then
strError = cInvalidDateError
End If
' If Sign Out Date From
If IsDate(Me.SignOutDateFrom) Then
' Add it to the predicate - exact
strWhere = strWhere & " AND " & "tbl_Sign_Out_Log.[Sign_Out_Date] >= " & GetDateFilter(Me.SignOutDateFrom)
ElseIf Nz(Me.SignOutDateFrom) <> "" Then
strError = cInvalidDateError
End If
' If Sign Out Date To
If IsDate(Me.SignOutDateTo) Then
' Add it to the predicate - exact
strWhere = strWhere & " AND " & "tbl_Sign_Out_Log.[Sign_Out_Date] <= " & GetDateFilter(Me.SignOutDateTo)
ElseIf Nz(Me.SignOutDateTo) <> "" Then
strError = cInvalidDateError
End If
' If Carrier
If Nz(Me.Carrier) <> "" Then
' Add it to the predicate - match on leading characters
strWhere = strWhere & " AND " & "tbl_Shipment_Log.Carriers_List Like '*" & Me.Carrier & "*'"
End If
If strError <> "" Then
MsgBox strError
Else
'DoCmd.OpenForm "Browse Issues", acFormDS, , strWhere, acFormEdit, acWindowNormal
If Not Me.FormFooter.Visible Then
Me.FormFooter.Visible = True
DoCmd.MoveSize Height:=Me.WindowHeight + Me.FormFooter.Height
End If
Me.Subform_Search_Order_Tracking.Form.Filter = strWhere
Me.Subform_Search_Order_Tracking.Form.FilterOn = True
End If
End Sub