Search Form with a SubForm

P

PJ

I have a main Search Criteria Form with a SubForm with the following coding
is a command button on the main form:


Private Sub Command62_Click()
Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yy#\"

If Not IsNull(Me.Issuer) Then
strWhere = strWhere & "([Issuer] = """ & Me.Issuer & """) AND "
End If

If Not IsNull(Me.StartDate) Then
strWhere = strWhere & "([Launch]>= " & Format(Me.StartDate,
conJetDate) & ") And "
End If

If Not IsNull(Me.EndDate) Then
strWhere = strWhere & "([Launch]<= " & Format(Me.EndDate,
conJetDate) & ") AND "
End If

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the "
AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints to
Immediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If

When I click the Search button it shows at the bottom how many filtered but
I am trying to get it to output to results to the subform named
"frmSearchFunction"

Any ideas??

Thanks in advance.
 
D

Daryl S

PJ -

You have build the filter criteria, but applied it to the main form instead
of the subform. Assuming the filter criteria will fit the subform (that is
the fieldnames are in the subform recordsource), then set the filter to the
subform and apply it there:

Me.[frmSearchFunction].Form.Filter = strWhere
Me.[frmSearchFunction].Form.FilterOn = True
 
P

PJ

Thanks Daryl that makes sense. Is there an easier way to output my results.
I just want a check box and a customer name to output form the filter so a
person can check the box and output only those customers to a report. I am
guessing a query so I can output to a report ??

Daryl S said:
PJ -

You have build the filter criteria, but applied it to the main form instead
of the subform. Assuming the filter criteria will fit the subform (that is
the fieldnames are in the subform recordsource), then set the filter to the
subform and apply it there:

Me.[frmSearchFunction].Form.Filter = strWhere
Me.[frmSearchFunction].Form.FilterOn = True

--
Daryl S


PJ said:
I have a main Search Criteria Form with a SubForm with the following coding
is a command button on the main form:


Private Sub Command62_Click()
Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yy#\"

If Not IsNull(Me.Issuer) Then
strWhere = strWhere & "([Issuer] = """ & Me.Issuer & """) AND "
End If

If Not IsNull(Me.StartDate) Then
strWhere = strWhere & "([Launch]>= " & Format(Me.StartDate,
conJetDate) & ") And "
End If

If Not IsNull(Me.EndDate) Then
strWhere = strWhere & "([Launch]<= " & Format(Me.EndDate,
conJetDate) & ") AND "
End If

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the "
AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints to
Immediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If

When I click the Search button it shows at the bottom how many filtered but
I am trying to get it to output to results to the subform named
"frmSearchFunction"

Any ideas??

Thanks in advance.
 
D

Daryl S

PJ -

You can use the same filter for a report. Since you stored the criteria in
the subform filter, use it when you open the report. Create the report
(without a filter), then add a button to your form to open the report. Then
change the code to add the filter - something like this:

DoCmd.OpenReport YourReportName, acPreview, ,
Me.[frmSearchFunction].Form.Filter

--
Daryl S


PJ said:
Thanks Daryl that makes sense. Is there an easier way to output my results.
I just want a check box and a customer name to output form the filter so a
person can check the box and output only those customers to a report. I am
guessing a query so I can output to a report ??

Daryl S said:
PJ -

You have build the filter criteria, but applied it to the main form instead
of the subform. Assuming the filter criteria will fit the subform (that is
the fieldnames are in the subform recordsource), then set the filter to the
subform and apply it there:

Me.[frmSearchFunction].Form.Filter = strWhere
Me.[frmSearchFunction].Form.FilterOn = True

--
Daryl S


PJ said:
I have a main Search Criteria Form with a SubForm with the following coding
is a command button on the main form:


Private Sub Command62_Click()
Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yy#\"

If Not IsNull(Me.Issuer) Then
strWhere = strWhere & "([Issuer] = """ & Me.Issuer & """) AND "
End If

If Not IsNull(Me.StartDate) Then
strWhere = strWhere & "([Launch]>= " & Format(Me.StartDate,
conJetDate) & ") And "
End If

If Not IsNull(Me.EndDate) Then
strWhere = strWhere & "([Launch]<= " & Format(Me.EndDate,
conJetDate) & ") AND "
End If

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the "
AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints to
Immediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If

When I click the Search button it shows at the bottom how many filtered but
I am trying to get it to output to results to the subform named
"frmSearchFunction"

Any ideas??

Thanks in advance.
 

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


Top