Search Form Help

G

Guest

I have a search form which currenlty searchs one table and opens a recordset
to another form for editing etc. This works great!

I now need to modify this search function to also search 2 other tables. I
am having trouble getting this to work.

I've included the original code I used for the 1st described search. The
ASONumber is the primary key in my tblJobs, the other 2 tables (tblCOMPO &
tblCustomerPO) also include this same ASONumber.Can anyone help me with the
code to modify this to satisfy the 2nd described search function?

Thanks in Advance

Private Sub Search_Click()
On Error GoTo Err_Search_Click

Dim strDocName As String
Dim strCriteria As String
Dim varWhere As Variant

strDocName = "frmJobEntry"

' If specified a ASO # value
If Not IsNothing(Me.ASONumber) Then
strCriteria = "[ASONumber]=" & Me.[ASONumber]
End If
' Do Job Name next
If Not IsNothing(Me.JobName) Then
strCriteria = "[JobName] Like'" & Me.[JobName] & "*'"
End If
' Do Quote # next
If Not IsNothing(Me.QuoteNumber) Then
strCriteria = "[QuoteNumber]= """ & Me.[QuoteNumber] & """"
End If

DoCmd.OpenForm strDocName, acNormal, , strCriteria
' and close this form
DoCmd.Close acForm, Me.Name

Exit_Search_Click:
Exit Sub

Err_Search_Click:
MsgBox Err.Description
Resume Exit_Search_Click

End Sub
 
A

Allen Browne

Presumably the form you are opening is bound to the man ASONumber table. To
filter that form based on values in 2 other tables, use a subquery in the
Filter.

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

This example assumes you want to filter the form to only those records that
have a match in tblCompo, based on the CompoType field containing the text
found in the text box named txtCompoType:

If Not IsNull(Me.txtCompoType) Then
strCriteria = "EXISTS (SELECT tblCompo.ASONumber FROM tblCompo " & _
"WHERE CompoType = """ & Me.txtCompoType& """"
End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

JeffT said:
I have a search form which currenlty searchs one table and opens a
recordset
to another form for editing etc. This works great!

I now need to modify this search function to also search 2 other tables. I
am having trouble getting this to work.

I've included the original code I used for the 1st described search. The
ASONumber is the primary key in my tblJobs, the other 2 tables (tblCOMPO &
tblCustomerPO) also include this same ASONumber.Can anyone help me with
the
code to modify this to satisfy the 2nd described search function?

Thanks in Advance

Private Sub Search_Click()
On Error GoTo Err_Search_Click

Dim strDocName As String
Dim strCriteria As String
Dim varWhere As Variant

strDocName = "frmJobEntry"

' If specified a ASO # value
If Not IsNothing(Me.ASONumber) Then
strCriteria = "[ASONumber]=" & Me.[ASONumber]
End If
' Do Job Name next
If Not IsNothing(Me.JobName) Then
strCriteria = "[JobName] Like'" & Me.[JobName] & "*'"
End If
' Do Quote # next
If Not IsNothing(Me.QuoteNumber) Then
strCriteria = "[QuoteNumber]= """ & Me.[QuoteNumber] & """"
End If

DoCmd.OpenForm strDocName, acNormal, , strCriteria
' and close this form
DoCmd.Close acForm, Me.Name

Exit_Search_Click:
Exit Sub

Err_Search_Click:
MsgBox Err.Description
Resume Exit_Search_Click
End Sub
Click to expand...
 
G

Guest

Thanks Allen. I think I'm almost there. I tried your code but I was getting a
message "missing ),] or item... Not sure if I placed ) or ] correctly but now
the form opens, only it's blank. Here's what I have...

If Not IsNull(Me.COMPO) Then
strCriteria = "EXISTS (SELECT [tblCOMPO.ASONumber] FROM [tblCOMPO] " & _
"WHERE [COMPO])= """ & Me.COMPO & """"

Allen Browne said:
Presumably the form you are opening is bound to the man ASONumber table. To
filter that form based on values in 2 other tables, use a subquery in the
Filter.

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

This example assumes you want to filter the form to only those records that
have a match in tblCompo, based on the CompoType field containing the text
found in the text box named txtCompoType:

If Not IsNull(Me.txtCompoType) Then
strCriteria = "EXISTS (SELECT tblCompo.ASONumber FROM tblCompo " & _
"WHERE CompoType = """ & Me.txtCompoType& """"
End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

JeffT said:
I have a search form which currenlty searchs one table and opens a
recordset
to another form for editing etc. This works great!

I now need to modify this search function to also search 2 other tables. I
am having trouble getting this to work.

I've included the original code I used for the 1st described search. The
ASONumber is the primary key in my tblJobs, the other 2 tables (tblCOMPO &
tblCustomerPO) also include this same ASONumber.Can anyone help me with
the
code to modify this to satisfy the 2nd described search function?

Thanks in Advance

Private Sub Search_Click()
On Error GoTo Err_Search_Click

Dim strDocName As String
Dim strCriteria As String
Dim varWhere As Variant

strDocName = "frmJobEntry"

' If specified a ASO # value
If Not IsNothing(Me.ASONumber) Then
strCriteria = "[ASONumber]=" & Me.[ASONumber]
End If
' Do Job Name next
If Not IsNothing(Me.JobName) Then
strCriteria = "[JobName] Like'" & Me.[JobName] & "*'"
End If
' Do Quote # next
If Not IsNothing(Me.QuoteNumber) Then
strCriteria = "[QuoteNumber]= """ & Me.[QuoteNumber] & """"
End If

DoCmd.OpenForm strDocName, acNormal, , strCriteria
' and close this form
DoCmd.Close acForm, Me.Name

Exit_Search_Click:
Exit Sub

Err_Search_Click:
MsgBox Err.Description
Resume Exit_Search_Click
End Sub
Click to expand...
Click to expand...
 
A

Allen Browne

Omit the square brackets, or place them around each table name and field
name independently. Also, your closing bracket is too early:

strCriteria = "EXISTS (SELECT [tblCOMPO].[ASONumber] FROM [tblCOMPO] " &
_
"WHERE [COMPO] = """ & Me.COMPO & """)"

You may need to add more criteria to the subquery. For example, you might
need to limit it to the same ASONumber as the one in your form's table,
e.g.:

strCriteria = "EXISTS (SELECT tblCOMPO.ASONumber " & _
"FROM tblCOMPO WHERE (COMPO = """ & Me.COMPO & _
""") AND (tblJobs.ASONumber = tblCOMPO.ASONumber))"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

JeffT said:
Thanks Allen. I think I'm almost there. I tried your code but I was
getting a
message "missing ),] or item... Not sure if I placed ) or ] correctly but
now
the form opens, only it's blank. Here's what I have...

If Not IsNull(Me.COMPO) Then
strCriteria = "EXISTS (SELECT [tblCOMPO.ASONumber] FROM [tblCOMPO] " &
_
"WHERE [COMPO])= """ & Me.COMPO & """"

Allen Browne said:
Presumably the form you are opening is bound to the man ASONumber table.
To
filter that form based on values in 2 other tables, use a subquery in the
Filter.

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

This example assumes you want to filter the form to only those records
that
have a match in tblCompo, based on the CompoType field containing the
text
found in the text box named txtCompoType:

If Not IsNull(Me.txtCompoType) Then
strCriteria = "EXISTS (SELECT tblCompo.ASONumber FROM tblCompo " & _
"WHERE CompoType = """ & Me.txtCompoType& """"
End If

JeffT said:
I have a search form which currenlty searchs one table and opens a
recordset
to another form for editing etc. This works great!

I now need to modify this search function to also search 2 other
tables. I
am having trouble getting this to work.

I've included the original code I used for the 1st described search.
The
ASONumber is the primary key in my tblJobs, the other 2 tables
(tblCOMPO &
tblCustomerPO) also include this same ASONumber.Can anyone help me with
the
code to modify this to satisfy the 2nd described search function?

Thanks in Advance

Private Sub Search_Click()
On Error GoTo Err_Search_Click

Dim strDocName As String
Dim strCriteria As String
Dim varWhere As Variant

strDocName = "frmJobEntry"

' If specified a ASO # value
If Not IsNothing(Me.ASONumber) Then
strCriteria = "[ASONumber]=" & Me.[ASONumber]
End If
' Do Job Name next
If Not IsNothing(Me.JobName) Then
strCriteria = "[JobName] Like'" & Me.[JobName] & "*'"
End If
' Do Quote # next
If Not IsNothing(Me.QuoteNumber) Then
strCriteria = "[QuoteNumber]= """ & Me.[QuoteNumber] & """"
End If

DoCmd.OpenForm strDocName, acNormal, , strCriteria
' and close this form
DoCmd.Close acForm, Me.Name

Exit_Search_Click:
Exit Sub

Err_Search_Click:
MsgBox Err.Description
Resume Exit_Search_Click
End Sub
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

It Works!!!
Thanks so much for all your help. This is going to make Monday a whole lot
better.

Allen Browne said:
Omit the square brackets, or place them around each table name and field
name independently. Also, your closing bracket is too early:

strCriteria = "EXISTS (SELECT [tblCOMPO].[ASONumber] FROM [tblCOMPO] " &
_
"WHERE [COMPO] = """ & Me.COMPO & """)"

You may need to add more criteria to the subquery. For example, you might
need to limit it to the same ASONumber as the one in your form's table,
e.g.:

strCriteria = "EXISTS (SELECT tblCOMPO.ASONumber " & _
"FROM tblCOMPO WHERE (COMPO = """ & Me.COMPO & _
""") AND (tblJobs.ASONumber = tblCOMPO.ASONumber))"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

JeffT said:
Thanks Allen. I think I'm almost there. I tried your code but I was
getting a
message "missing ),] or item... Not sure if I placed ) or ] correctly but
now
the form opens, only it's blank. Here's what I have...

If Not IsNull(Me.COMPO) Then
strCriteria = "EXISTS (SELECT [tblCOMPO.ASONumber] FROM [tblCOMPO] " &
_
"WHERE [COMPO])= """ & Me.COMPO & """"

Allen Browne said:
Presumably the form you are opening is bound to the man ASONumber table.
To
filter that form based on values in 2 other tables, use a subquery in the
Filter.

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

This example assumes you want to filter the form to only those records
that
have a match in tblCompo, based on the CompoType field containing the
text
found in the text box named txtCompoType:

If Not IsNull(Me.txtCompoType) Then
strCriteria = "EXISTS (SELECT tblCompo.ASONumber FROM tblCompo " & _
"WHERE CompoType = """ & Me.txtCompoType& """"
End If

I have a search form which currenlty searchs one table and opens a
recordset
to another form for editing etc. This works great!

I now need to modify this search function to also search 2 other
tables. I
am having trouble getting this to work.

I've included the original code I used for the 1st described search.
The
ASONumber is the primary key in my tblJobs, the other 2 tables
(tblCOMPO &
tblCustomerPO) also include this same ASONumber.Can anyone help me with
the
code to modify this to satisfy the 2nd described search function?

Thanks in Advance

Private Sub Search_Click()
On Error GoTo Err_Search_Click

Dim strDocName As String
Dim strCriteria As String
Dim varWhere As Variant

strDocName = "frmJobEntry"

' If specified a ASO # value
If Not IsNothing(Me.ASONumber) Then
strCriteria = "[ASONumber]=" & Me.[ASONumber]
End If
' Do Job Name next
If Not IsNothing(Me.JobName) Then
strCriteria = "[JobName] Like'" & Me.[JobName] & "*'"
End If
' Do Quote # next
If Not IsNothing(Me.QuoteNumber) Then
strCriteria = "[QuoteNumber]= """ & Me.[QuoteNumber] & """"
End If

DoCmd.OpenForm strDocName, acNormal, , strCriteria
' and close this form
DoCmd.Close acForm, Me.Name

Exit_Search_Click:
Exit Sub

Err_Search_Click:
MsgBox Err.Description
Resume Exit_Search_Click
End Sub
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Checking Yes or No before going to Form 3
Find record in subform 5
Trying to open a form by double clicking a field on another.... 1
LIKE search help needed 3
Run time error 6
Run time error 3077 2
Problem with form opening From List Box 2
Set focus not working properly 3

Top