473,320 Members | 1,839 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Searching a Form / Table

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
1 1372
JConsulting
603 Expert 512MB
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

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

Similar topics

0
by: Jason | last post by:
I would like to be able to place a command button on a primary (parent) form that opens up a subform. I want to use this subform to search for or limit the recordset of data in the primary form. ...
0
by: Jason | last post by:
I would like to be able to place a command button on a primary (parent) form that opens up a subform. I want to use this subform to search for or limit the recordset of data in the primary form. ...
2
by: Tim Pollard | last post by:
Hi I'm hoping someone can help me with an access problem I just can't get my head around. I normally use access as a back end for asp pages, just to hold data in tables, so queries within access...
33
by: Geoff Jones | last post by:
Hiya I have a DataTable containing thousands of records. Each record has a primary key field called "ID" and another field called "PRODUCT" I want to retrieve the rows that satisy the following...
7
by: john | last post by:
In my form I have a master table and a details table linked 1xM. I can search through the whole parent table but I also like to be able to search through the child table fields to find parent...
3
by: Aaron | last post by:
I'm trying to parse a table on a webpage to pull down some data I need. The page is based off of information entered into a form. when you submit the data from the form it displays a...
21
by: JHNielson | last post by:
I have a table that I would like to be able for the users to search/filter. I have multiple fields to search: I built a form, and I would like them to be able to choose form a set of...
1
by: morangi | last post by:
<?php session_start(); error_reporting(E_ERROR && ~E_NOTICE && ~E_WARNING); ob_start(); $link = mysql_connect('localhost', 'root', 'test'); if (!$link) { die('Could not connect: ' ....
0
by: Kassimu | last post by:
Hi guys out there, There is this database Iam creating, I have a table with 40 fields among which there are Date/time, Text, Number, Memo and Yes/No fields and I have created the form bound to that...
20
by: tkip | last post by:
Hello everyone. I have been working on this DB for quite some time and still can't figure out a way to do what I want to do. It's a database that keep track of drawings and engineering change etc....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.