Filtered records in subform to report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all,
I have a form that is use to filter records in the subform. In the main
form, they are all unbound textbox, and they are used to search records in
the subform.
This form have been done with the help of this forum.
But now i need to bring the filtered records in the subform to the report
for printing.
I have no idea on how to get started, should i create a report 1st?
or use of command button in the form?
I'm really new to access, and know nothing of coding in access.
Pls help me with this problem.
Your help will be greatly appreciated!

Regards
Green
 
I have a form that is use to filter records in the subform. In the main
form, they are all unbound textbox, and they are used to search records in
the subform.
This form have been done with the help of this forum.
But now i need to bring the filtered records in the subform to the report
for printing.
I have no idea on how to get started, should i create a report 1st?
or use of command button in the form?
Click to expand...

Create a Query first, using the unbound textboxes on the Form as
criteria. You may well have already done so as the recordsource for
the subform; if not, create a Query based on your table, selecting the
fields that you want on the report (and those that you need for
selecting records). On the Criteria of the query put

=[Forms]![NameOfYourForm]![NameOfATextbox]

using your own form and textbox names of course.

Create a Report based on this Query, and use the command button
toolbox wizard to create a button to open the Report. It will
automatically use the query, which will in turn look on the form for
its criteria.

John W. Vinson[MVP]
 
Hi John,

Thanks for the quick reply!
I did try your way of creating a query for the form. However, i faced some
problems in it. As i have more than 20 unbound textboxes. It will based on
how many textboxes that the user filled in, to filter.
But in query, if i put , =[Forms]![RecordSearchform]![txtOrigin] and
[Forms]![RecordSearchform]![txtDestination] and ........... the user will
have to key in all to get the filtered records.
If i put, =[Forms]![RecordSearchform]![txtOrigin] or
[Forms]![RecordSearchform]![txtdestination] or ......... the user will only
have to key in 1 criteria to get the filtered records.
Is there any way to combine multiple criteria together?
Sorry for my poor explanation.
Hope is understandable.

Thanks
Green

John Vinson said:
I have a form that is use to filter records in the subform. In the main
form, they are all unbound textbox, and they are used to search records in
the subform.
This form have been done with the help of this forum.
But now i need to bring the filtered records in the subform to the report
for printing.
I have no idea on how to get started, should i create a report 1st?
or use of command button in the form?
Click to expand...

Create a Query first, using the unbound textboxes on the Form as
criteria. You may well have already done so as the recordsource for
the subform; if not, create a Query based on your table, selecting the
fields that you want on the report (and those that you need for
selecting records). On the Criteria of the query put

=[Forms]![NameOfYourForm]![NameOfATextbox]

using your own form and textbox names of course.

Create a Report based on this Query, and use the command button
toolbox wizard to create a button to open the Report. It will
automatically use the query, which will in turn look on the form for
its criteria.

John W. Vinson[MVP]
Click to expand...
 
Hi John,

Thanks for the quick reply!
I did try your way of creating a query for the form. However, i faced some
problems in it. As i have more than 20 unbound textboxes. It will based on
how many textboxes that the user filled in, to filter.
But in query, if i put , =[Forms]![RecordSearchform]![txtOrigin] and
[Forms]![RecordSearchform]![txtDestination] and ........... the user will
have to key in all to get the filtered records.
If i put, =[Forms]![RecordSearchform]![txtOrigin] or
[Forms]![RecordSearchform]![txtdestination] or ......... the user will only
have to key in 1 criteria to get the filtered records.
Is there any way to combine multiple criteria together?
Sorry for my poor explanation.
Hope is understandable.
Click to expand...

Well, it's more complicated then... <g>

What you need to do is write VBA code in the button's Click event to
poll through the multiple controls on the form, building up a SQL
string which will be the report's recordsource. You'ld have an initial
SQL string such as:

Dim strSQL As String
strSQL = "SELECT this, that, theother FROM mytable WHERE TRUE"

which will (initially) return all records.

Your code would then step through the controls on the form; if the
control contains anything, you would add a criterion:

strSQL = strSQL & " AND [fieldname] = '" & Me!controlname & "'"

(using quotes for a text type field; use # for date fields and no
delimiter for numbers).

to the growing SQL string. If the control is empty, just leave strSQL
alone. Finally you'ld set the Report's Recordsource property to this
SQL string.


John W. Vinson[MVP]
 
Hi John,
Just to add on...
I have figure out that i can filter by using this code:
DoCmd.OpenReport stDocName, acPreview, , "[Origin]= '" & Me.[txtOrigin] &
"'"

but how do i add on with the increase of field?
Your help will be greatly appreciated!

Thanks
Green

'green' said:
Sorry John,
I don't quite understand what you mean... I'm a novice on codes...
In the form i had a button to search records which is shown as below:
--------------------------------code
starts-------------------------------------------
Private Sub Search_Click()

Dim strFilter As String
Dim bolAnd As Boolean
bolAnd = False
strFilter = ""
If Len(Me.txtOrigin.Value) > 0 Then
If bolAnd Then
strFilter = strFilter & " and "
End If
strFilter = strFilter & " Origin Like '*" & Me.txtOrigin.Value & "*'"
bolAnd = True
End If
.............................. (and other unbound textboxes)
With Me.[Daily_Report_subform].Form
If .Dirty Then
.Dirty = False
End If
If strFilter = "" Then
.FilterOn = False 'Nothing entered: show all records.
Else
.Filter = strFilter
.FilterOn = True
End If
End With
End Sub
-----------------------------------code
ends-------------------------------------------


and this is the code i use for the preview of report using the wizard:
----------------------------------------code
starts-------------------------------------
Private Sub Preview_Report_Click()
On Error GoTo Err_Preview_Report_Click

Dim stDocName As String

stDocName = "Find_Records"
DoCmd.OpenReport stDocName, acPreview

Exit_Preview_Report_Click:
Exit Sub

Err_Preview_Report_Click:
MsgBox Err.Description
Resume Exit_Preview_Report_Click

End Sub
------------------------------------code
ends-------------------------------------------
So where should i put this code: strSQL = strSQL & " AND [fieldname] = '" &
Me!controlname & "'" ?
As for the control name it is the textbox name right?
So do we still need the query for the report?
Sorry to trouble you...

Thanks
Green

John Vinson said:
Hi John,

Thanks for the quick reply!
I did try your way of creating a query for the form. However, i faced some
problems in it. As i have more than 20 unbound textboxes. It will based on
how many textboxes that the user filled in, to filter.
But in query, if i put , =[Forms]![RecordSearchform]![txtOrigin] and
[Forms]![RecordSearchform]![txtDestination] and ........... the user will
have to key in all to get the filtered records.
If i put, =[Forms]![RecordSearchform]![txtOrigin] or
[Forms]![RecordSearchform]![txtdestination] or ......... the user will only
have to key in 1 criteria to get the filtered records.
Is there any way to combine multiple criteria together?
Sorry for my poor explanation.
Hope is understandable.
Click to expand...

Well, it's more complicated then... <g>

What you need to do is write VBA code in the button's Click event to
poll through the multiple controls on the form, building up a SQL
string which will be the report's recordsource. You'ld have an initial
SQL string such as:

Dim strSQL As String
strSQL = "SELECT this, that, theother FROM mytable WHERE TRUE"

which will (initially) return all records.

Your code would then step through the controls on the form; if the
control contains anything, you would add a criterion:

strSQL = strSQL & " AND [fieldname] = '" & Me!controlname & "'"

(using quotes for a text type field; use # for date fields and no
delimiter for numbers).

to the growing SQL string. If the control is empty, just leave strSQL
alone. Finally you'ld set the Report's Recordsource property to this
SQL string.


John W. Vinson[MVP]
Click to expand...
Click to expand...
 
Sorry John,
I don't quite understand what you mean... I'm a novice on codes...
In the form i had a button to search records which is shown as below:
--------------------------------code
starts-------------------------------------------
Private Sub Search_Click()

Dim strFilter As String
Dim bolAnd As Boolean
bolAnd = False
strFilter = ""
If Len(Me.txtOrigin.Value) > 0 Then
If bolAnd Then
strFilter = strFilter & " and "
End If
strFilter = strFilter & " Origin Like '*" & Me.txtOrigin.Value & "*'"
bolAnd = True
End If
.............................. (and other unbound textboxes)
With Me.[Daily_Report_subform].Form
If .Dirty Then
.Dirty = False
End If
If strFilter = "" Then
.FilterOn = False 'Nothing entered: show all records.
Else
.Filter = strFilter
.FilterOn = True
End If
End With
End Sub
-----------------------------------code
ends-------------------------------------------


and this is the code i use for the preview of report using the wizard:
----------------------------------------code
starts-------------------------------------
Private Sub Preview_Report_Click()
On Error GoTo Err_Preview_Report_Click

Dim stDocName As String

stDocName = "Find_Records"
DoCmd.OpenReport stDocName, acPreview

Exit_Preview_Report_Click:
Exit Sub

Err_Preview_Report_Click:
MsgBox Err.Description
Resume Exit_Preview_Report_Click

End Sub
------------------------------------code
ends-------------------------------------------
So where should i put this code: strSQL = strSQL & " AND [fieldname] = '" &
Me!controlname & "'" ?
As for the control name it is the textbox name right?
So do we still need the query for the report?
Sorry to trouble you...

Thanks
Green

John Vinson said:
Hi John,

Thanks for the quick reply!
I did try your way of creating a query for the form. However, i faced some
problems in it. As i have more than 20 unbound textboxes. It will based on
how many textboxes that the user filled in, to filter.
But in query, if i put , =[Forms]![RecordSearchform]![txtOrigin] and
[Forms]![RecordSearchform]![txtDestination] and ........... the user will
have to key in all to get the filtered records.
If i put, =[Forms]![RecordSearchform]![txtOrigin] or
[Forms]![RecordSearchform]![txtdestination] or ......... the user will only
have to key in 1 criteria to get the filtered records.
Is there any way to combine multiple criteria together?
Sorry for my poor explanation.
Hope is understandable.
Click to expand...

Well, it's more complicated then... <g>

What you need to do is write VBA code in the button's Click event to
poll through the multiple controls on the form, building up a SQL
string which will be the report's recordsource. You'ld have an initial
SQL string such as:

Dim strSQL As String
strSQL = "SELECT this, that, theother FROM mytable WHERE TRUE"

which will (initially) return all records.

Your code would then step through the controls on the form; if the
control contains anything, you would add a criterion:

strSQL = strSQL & " AND [fieldname] = '" & Me!controlname & "'"

(using quotes for a text type field; use # for date fields and no
delimiter for numbers).

to the growing SQL string. If the control is empty, just leave strSQL
alone. Finally you'ld set the Report's Recordsource property to this
SQL string.


John W. Vinson[MVP]
Click to expand...
 
Hi John,

I had did the code already with the help of others!
Dim strFilter As String
Dim bolAnd As Boolean
bolAnd = False
strFilter = ""
If Len(Me.txtOrigin.Value) > 0 Then
If bolAnd Then
strFilter = strFilter & " and "
End If
strFilter = strFilter & " Origin Like '*" & Me.txtOrigin.Value & "*'"
bolAnd = True
End If
...........................................
DoCmd.OpenReport "Find_Records", acPreview, , strWhere

Thanks for your help these few days :)

Regards
Green

'green' said:
Sorry John,
I don't quite understand what you mean... I'm a novice on codes...
In the form i had a button to search records which is shown as below:
--------------------------------code
starts-------------------------------------------
Private Sub Search_Click()

Dim strFilter As String
Dim bolAnd As Boolean
bolAnd = False
strFilter = ""
If Len(Me.txtOrigin.Value) > 0 Then
If bolAnd Then
strFilter = strFilter & " and "
End If
strFilter = strFilter & " Origin Like '*" & Me.txtOrigin.Value & "*'"
bolAnd = True
End If
.............................. (and other unbound textboxes)
With Me.[Daily_Report_subform].Form
If .Dirty Then
.Dirty = False
End If
If strFilter = "" Then
.FilterOn = False 'Nothing entered: show all records.
Else
.Filter = strFilter
.FilterOn = True
End If
End With
End Sub
-----------------------------------code
ends-------------------------------------------


and this is the code i use for the preview of report using the wizard:
----------------------------------------code
starts-------------------------------------
Private Sub Preview_Report_Click()
On Error GoTo Err_Preview_Report_Click

Dim stDocName As String

stDocName = "Find_Records"
DoCmd.OpenReport stDocName, acPreview

Exit_Preview_Report_Click:
Exit Sub

Err_Preview_Report_Click:
MsgBox Err.Description
Resume Exit_Preview_Report_Click

End Sub
------------------------------------code
ends-------------------------------------------
So where should i put this code: strSQL = strSQL & " AND [fieldname] = '" &
Me!controlname & "'" ?
As for the control name it is the textbox name right?
So do we still need the query for the report?
Sorry to trouble you...

Thanks
Green

John Vinson said:
Hi John,

Thanks for the quick reply!
I did try your way of creating a query for the form. However, i faced some
problems in it. As i have more than 20 unbound textboxes. It will based on
how many textboxes that the user filled in, to filter.
But in query, if i put , =[Forms]![RecordSearchform]![txtOrigin] and
[Forms]![RecordSearchform]![txtDestination] and ........... the user will
have to key in all to get the filtered records.
If i put, =[Forms]![RecordSearchform]![txtOrigin] or
[Forms]![RecordSearchform]![txtdestination] or ......... the user will only
have to key in 1 criteria to get the filtered records.
Is there any way to combine multiple criteria together?
Sorry for my poor explanation.
Hope is understandable.
Click to expand...

Well, it's more complicated then... <g>

What you need to do is write VBA code in the button's Click event to
poll through the multiple controls on the form, building up a SQL
string which will be the report's recordsource. You'ld have an initial
SQL string such as:

Dim strSQL As String
strSQL = "SELECT this, that, theother FROM mytable WHERE TRUE"

which will (initially) return all records.

Your code would then step through the controls on the form; if the
control contains anything, you would add a criterion:

strSQL = strSQL & " AND [fieldname] = '" & Me!controlname & "'"

(using quotes for a text type field; use # for date fields and no
delimiter for numbers).

to the growing SQL string. If the control is empty, just leave strSQL
alone. Finally you'ld set the Report's Recordsource property to this
SQL string.


John W. Vinson[MVP]
Click to expand...
Click to expand...
 
Back
Top