By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,484 Members | 1,811 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,484 IT Pros & Developers. It's quick & easy.

Searching a Form / Table

P: 4
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
Jul 24 '07 #1
Share this Question
Share on Google+
1 Reply


JConsulting
Expert 100+
P: 603
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

One suggestion might be to create a query initially to "join" the tables thus making them one recordset. Any search results after that would reference the query and return all records for a given value on both sides of the join.
J
Jul 31 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.