Form data to print on Report

[Jan Il]

Hi all - Access 2002, WinME

I have a form that displays data as selected from a filter form, which can
include a combination of date periods (From-To) and various other selections
to filter the data to be displayed. I need to have just the selected data
on the form printed on a report. Yet, I can't seem to get the report to
print out just the data in the form. Instead, it prints everything. There
are text box controls on the report that are based upon information in the
respective form controls, such as date periods, calculations, etc., and
these display the correct information on the report. But, the report is
printing all the records, not just the ones selected in the form that the
date period and calculations are based upon.

I know it's something simple I have overlooked, or set up incorrectly, but,
I can't seem to find it. All else is working perfectly with the form and
report, except this one thing.

I would truly appreciate any suggestions for areas to troubleshoot on this,
and I'm a bit dry on ideas of where to look, or what I should be looking
for.

Best regards,
Jan

 

[JohnR]

You need to use the criteria from the form to filter the
data in the record source for the report. There are two
ways to do this.
One is to use the form to set criteria in the query for
the report. Under a date in the query you would use
Between Forms!DataForm!FromDate and Forms!DataForm!ToDate
or for a record ID you woudld use Forms!DataForm!ID

The second way is to pass the criteria to the report when
opening it from the form.

stCriteria = "ReportDate = Between " & Forms!DataForm!
FromDate & " AND " & Forms!DataForm!ToDate & " AND ID = "
& Forms!DataForm!ID
DoCmd.OpenReport "ReportName",
acViewNormal, "ReportQuery", stCriteria

 

[Jan Il]

Hi JohnR!

You need to use the criteria from the form to filter the
data in the record source for the report. There are two
ways to do this.
One is to use the form to set criteria in the query for
the report. Under a date in the query you would use
Between Forms!DataForm!FromDate and Forms!DataForm!ToDate
or for a record ID you woudld use Forms!DataForm!ID

Yes..this I do have have in place now in the query for the JobDate.
Between [Forms]![frmLaborFilter]![Text62] And
[Forms]![frmLaborFilter]![Text64]

Text62 being the From Date and Text64 the To Date controls on the filter
form. This seems to be in good order.

The second way is to pass the criteria to the report when
opening it from the form.

stCriteria = "ReportDate = Between " & Forms!DataForm!
FromDate & " AND " & Forms!DataForm!ToDate & " AND ID = "
& Forms!DataForm!ID
DoCmd.OpenReport "ReportName",
acViewNormal, "ReportQuery", stCriteria

I have copy and pasted the above code into the On Click event of the Preview
Report command button on the form to preview the report. I have made sure
that all the code is on one line as appropriate, and made the changes to the
correct form and control names.

stCriteria = "JobDate" = Between " & Forms!frmLaborFilter!Text62 & " AND " &
Forms!frmLaborFilter!Text64

However, the debugger is barking at the part above and says there is a
Compile error: 'Syntax error' , but, does not say where. Obviously I've
missed something somewhere, but just can't see it.

Thank you very much for your time and help, I really appreciate it.

Best regards,
Jan

 

[Dirk Goldgar]

Hi JohnR!

You need to use the criteria from the form to filter the
data in the record source for the report. There are two
ways to do this.
One is to use the form to set criteria in the query for
the report. Under a date in the query you would use
Between Forms!DataForm!FromDate and Forms!DataForm!ToDate
or for a record ID you woudld use Forms!DataForm!ID

Yes..this I do have have in place now in the query for the JobDate.
Between [Forms]![frmLaborFilter]![Text62] And
[Forms]![frmLaborFilter]![Text64]

Text62 being the From Date and Text64 the To Date controls on the
filter form. This seems to be in good order.

The second way is to pass the criteria to the report when
opening it from the form.

stCriteria = "ReportDate = Between " & Forms!DataForm!
FromDate & " AND " & Forms!DataForm!ToDate & " AND ID = "
& Forms!DataForm!ID
DoCmd.OpenReport "ReportName",
acViewNormal, "ReportQuery", stCriteria

I have copy and pasted the above code into the On Click event of the
Preview Report command button on the form to preview the report. I
have made sure that all the code is on one line as appropriate, and
made the changes to the correct form and control names.

stCriteria = "JobDate" = Between " & Forms!frmLaborFilter!Text62 & "
AND " & Forms!frmLaborFilter!Text64

However, the debugger is barking at the part above and says there is a
Compile error: 'Syntax error' , but, does not say where. Obviously
I've missed something somewhere, but just can't see it.

Thank you very much for your time and help, I really appreciate it.

PMFJI, but there's a syntax error in the code John posted. Also, the
start and end dates should be formatted into proper #mm/dd/yyyy#
literals. Try this:

stCriteria = "JobDate Between " & _
Format(Forms!frmLaborFilter!Text62, "\#mm/dd/yyyy\#") & _
" AND " & _
Format(Forms!frmLaborFilter!Text64, "\#mm/dd/yyyy\#")

 

[Jan Il]

Hi Dirk!

Hi JohnR!

You need to use the criteria from the form to filter the
data in the record source for the report. There are two
ways to do this.
One is to use the form to set criteria in the query for
the report. Under a date in the query you would use
Between Forms!DataForm!FromDate and Forms!DataForm!ToDate
or for a record ID you woudld use Forms!DataForm!ID

Yes..this I do have have in place now in the query for the JobDate.
Between [Forms]![frmLaborFilter]![Text62] And
[Forms]![frmLaborFilter]![Text64]

Text62 being the From Date and Text64 the To Date controls on the
filter form. This seems to be in good order.

The second way is to pass the criteria to the report when
opening it from the form.

stCriteria = "ReportDate = Between " & Forms!DataForm!
FromDate & " AND " & Forms!DataForm!ToDate & " AND ID = "
& Forms!DataForm!ID
DoCmd.OpenReport "ReportName",
acViewNormal, "ReportQuery", stCriteria

I have copy and pasted the above code into the On Click event of the
Preview Report command button on the form to preview the report. I
have made sure that all the code is on one line as appropriate, and
made the changes to the correct form and control names.

stCriteria = "JobDate" = Between " & Forms!frmLaborFilter!Text62 & "
AND " & Forms!frmLaborFilter!Text64

However, the debugger is barking at the part above and says there is a
Compile error: 'Syntax error' , but, does not say where. Obviously
I've missed something somewhere, but just can't see it.

Thank you very much for your time and help, I really appreciate it.

PMFJI, but there's a syntax error in the code John posted. Also, the
start and end dates should be formatted into proper #mm/dd/yyyy#
literals. Try this:

stCriteria = "JobDate Between " & _
Format(Forms!frmLaborFilter!Text62, "\#mm/dd/yyyy\#") & _
" AND " & _
Format(Forms!frmLaborFilter!Text64, "\#mm/dd/yyyy\#")

I have done a copy and paste of this code into the command button on the
frmLaborCosts that should open the rptLaborCosts report for preview. But,
it is printing it instead and I can't preview it. Although, it is still
printing out all the records, not just those for the criteria as entered
from the frmLaborFilter or the frmLaborCosts for that specific set of
records to print out.

I thought it might have something to do with the way the query was set up so
I posted a question regarding this aspect on the queries groups (Query for
report won't sort - 12/24/03 7:40p), and some of the things Steve Schapel
has suggested about =Null's in the code behind the button on the
frmLaborFilter that opens the frmLaborCosts that more or less clears the
criteria from the controls when the frmLaborCosts is opened, may have some
bearing on the problem of the criteria being passed to the report as well,
but, I'm really not sure how it works, or does not work. It is a test db at
this point, and I have created reports before with no problems. But, for
some reason, this one is not cooperating.

Thank you for your help, I really apperciate it.

Jan

 

[Dirk Goldgar]

Hi Dirk!

Hi, Jan! Merry Christmas!

[...] Try this:

stCriteria = "JobDate Between " & _
Format(Forms!frmLaborFilter!Text62, "\#mm/dd/yyyy\#") & _
" AND " & _
Format(Forms!frmLaborFilter!Text64, "\#mm/dd/yyyy\#")

I have done a copy and paste of this code into the command button on
the frmLaborCosts that should open the rptLaborCosts report for
preview. But, it is printing it instead and I can't preview it.
Although, it is still printing out all the records, not just those
for the criteria as entered from the frmLaborFilter or the
frmLaborCosts for that specific set of records to print out.

I can't help thinking that either the code isn't being executed at all,
or there's something else wrong with it. I haven't followed this thread
from top to bottom, so I don't know what-all has gone before. Please
post the complete code from the command button's Click event procedure.
Set a breakpoint and verify, if you would, that this code is actually
being executed. Also post any code that may exist in the report's Open
event.

 

[Jan Il]

Hi Dirk!

Hi, Jan! Merry Christmas!

[...] Try this:

stCriteria = "JobDate Between " & _
Format(Forms!frmLaborFilter!Text62, "\#mm/dd/yyyy\#") & _
" AND " & _
Format(Forms!frmLaborFilter!Text64, "\#mm/dd/yyyy\#")

I have done a copy and paste of this code into the command button on
the frmLaborCosts that should open the rptLaborCosts report for
preview. But, it is printing it instead and I can't preview it.
Although, it is still printing out all the records, not just those
for the criteria as entered from the frmLaborFilter or the
frmLaborCosts for that specific set of records to print out.

I can't help thinking that either the code isn't being executed at all,
or there's something else wrong with it. I haven't followed this thread
from top to bottom, so I don't know what-all has gone before. Please
post the complete code from the command button's Click event procedure.
Set a breakpoint and verify, if you would, that this code is actually
being executed. Also post any code that may exist in the report's Open
event.

There is no code in the Open Event of the rptLabotCosts form.

Here is the code in the On click event for the command button that is on the
frmLaborCosts to preview the report. I recreated the button, and it will now
preview, but, all records are shown, not the selected ones as is on the
frmLaborCosts where the button is located:
**********************Begin Code************************
Private Sub cmdRptPreview_Click()
On Error GoTo Err_cmdRptPreview_Click

Dim stDocName As String

stCriteria = "JobDate Between " & _
Format(Forms!frmLaborFilter!Text62, "\#mm/dd/yyyy\#") & _
" AND " & _
Format(Forms!frmLaborFilter!Text64, "\#mm/dd/yyyy\#")

stDocName = "rptLaborCosts"
DoCmd.OpenReport stDocName, acPreview

Exit_cmdRptPreview_Click:
Exit Sub

Err_cmdRptPreview_Click:
MsgBox Err.Description
Resume Exit_cmdRptPreview_Click

End Sub
******************End Code*********************

The code behind the command button on the frmLaborFilter that opens the
frmLaborCosts after the criteria has been entered in the controls is:
***************************Begin Code**********************
Private Sub cmdExpResearch_Click()
On Error GoTo Err_cmdExpResearch_Click

Dim strWhere As String

Dim stDocName As String
Dim stLinkCriteria As String

strWhere = ""
If IsNull(Me!cmbJobLocation) = False Then
strWhere = "[JobLocation]=" & "'" & Me![cmbJobLocation] & "'"
End If

Me![cmbJobLocation] = Null

If IsNull(Me!cmbJobType) = False Then
If strWhere <> "" Then strWhere = strWhere & " and "
strWhere = strWhere & "[JobType]=" & "'" & Me![cmbJobType] & "'"
End If

Me![cmbJobType] = Null

If IsNull(Me!cmbContractorName) = False Then
If strWhere <> "" Then strWhere = strWhere & " and "
strWhere = strWhere & "[ContractorName]=" & "'" & Me![cmbContractorName]
& "'"
End If

Me![cmbContractorName] = Null

If IsNull(Me!cmbEmpName) = False Then
If strWhere <> "" Then strWhere = strWhere & " and "
strWhere = strWhere & "[EmpName]=" & "'" & Me![cmbEmpName] & "'"
End If

Me![cmbEmpName] = Null

stDocName = "frmLaborCosts"

stLinkCriteria = strWhere
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdExpResearch_Click:
Exit Sub

Err_cmdExpResearch_Click:
MsgBox Err.Description
Resume Exit_cmdExpResearch_Click

End Sub
*************************End Code********************

I have run the breakpoint and the code appears to be firing.

Don't know if you need this information, but, the query behind the
frmLabotCosts and rptLaborCosts is based upon a table where records are
entered from a data entry form. The query SQL is;

SELECT tblLaborCosts.EmpName, tblLaborCosts.EmpID, tblLaborCosts.JobDate,
tblLaborCosts.ContractorName, tblLaborCosts.JobType,
tblLaborCosts.JobLocation, tblLaborCosts.Manhours, tblLaborCosts.HourlyRate,
Sum([Manhours]*[HourlyRate]) AS TotalCost
FROM tblLaborCosts
GROUP BY tblLaborCosts.EmpName, tblLaborCosts.EmpID, tblLaborCosts.JobDate,
tblLaborCosts.ContractorName, tblLaborCosts.JobType,
tblLaborCosts.JobLocation, tblLaborCosts.Manhours, tblLaborCosts.HourlyRate
HAVING (((tblLaborCosts.JobDate) Between [Forms]![frmLaborFilter]![Text62]
And [Forms]![frmLaborFilter]![Text64])) OR
((([Forms]![frmLaborFilter]![Text62]) Is Null)) OR
((([Forms]![frmLaborFilter]![Text64]) Is Null));

If you wish, as a ref of the basic structure of the frmLaborFilter form, you
can see the Purchasing Filter form in the MOW db. The structure is
basically the same, with different names and data sources, and I added the
command button with the research code on the frmLaborFilter. This may give
you an idea of how the criteria is entered in the controls for the sets of
records to be viewed and printed by the report.

Thank you,
Jan

 

[Dirk Goldgar]

Hi Dirk!

Hi, Jan! Merry Christmas!

[...] Try this:

stCriteria = "JobDate Between " & _
Format(Forms!frmLaborFilter!Text62, "\#mm/dd/yyyy\#")
& _ " AND " & _
Format(Forms!frmLaborFilter!Text64, "\#mm/dd/yyyy\#")

I have done a copy and paste of this code into the command button on
the frmLaborCosts that should open the rptLaborCosts report for
preview. But, it is printing it instead and I can't preview it.
Although, it is still printing out all the records, not just those
for the criteria as entered from the frmLaborFilter or the
frmLaborCosts for that specific set of records to print out.

I can't help thinking that either the code isn't being executed at
all, or there's something else wrong with it. I haven't followed
this thread from top to bottom, so I don't know what-all has gone
before. Please post the complete code from the command button's
Click event procedure. Set a breakpoint and verify, if you would,
that this code is actually being executed. Also post any code that
may exist in the report's Open event.

There is no code in the Open Event of the rptLabotCosts form.

Here is the code in the On click event for the command button that is
on the frmLaborCosts to preview the report. I recreated the button,
and it will now preview, but, all records are shown, not the selected
ones as is on the frmLaborCosts where the button is located:
**********************Begin Code************************
Private Sub cmdRptPreview_Click()
On Error GoTo Err_cmdRptPreview_Click

Dim stDocName As String

stCriteria = "JobDate Between " & _
Format(Forms!frmLaborFilter!Text62, "\#mm/dd/yyyy\#") & _
" AND " & _
Format(Forms!frmLaborFilter!Text64, "\#mm/dd/yyyy\#")

stDocName = "rptLaborCosts"
DoCmd.OpenReport stDocName, acPreview

Exit_cmdRptPreview_Click:
Exit Sub

Err_cmdRptPreview_Click:
MsgBox Err.Description
Resume Exit_cmdRptPreview_Click

End Sub
******************End Code*********************

And now the problem is clear. Although the procedure builds a criteria
string, it doesn't use it in opening the report. Also, it never
declares the variable stCriteria -- presumably you don't have Option
Explicit specified, so Access automatically defines the variable as a
Variant. I suggest you revise the command procedure as follows:

**********************Begin Code************************
Private Sub cmdRptPreview_Click()
On Error GoTo Err_cmdRptPreview_Click

Dim stDocName As String
Dim stCriteria As String

stCriteria = "JobDate Between " & _
Format(Forms!frmLaborFilter!Text62, "\#mm/dd/yyyy\#") & _
" AND " & _
Format(Forms!frmLaborFilter!Text64, "\#mm/dd/yyyy\#")

stDocName = "rptLaborCosts"

DoCmd.OpenReport stDocName, acPreview, , stCriteria

Exit_cmdRptPreview_Click:
Exit Sub

Err_cmdRptPreview_Click:
MsgBox Err.Description
Resume Exit_cmdRptPreview_Click

End Sub
******************End Code*********************

That should settle that problem. I also strongly suggest that you set
the "Require Variable Declaration" option in the VB Editor, so that in
all new modules you are forced to declare any variables. This will
prevent the kind of problems later on that are caused by misspelled or
mistyped variable names.

 

[Jan Il]

Hi Dirk!

[...] Try this:

stCriteria = "JobDate Between " & _
Format(Forms!frmLaborFilter!Text62, "\#mm/dd/yyyy\#")
& _ " AND " & _
Format(Forms!frmLaborFilter!Text64, "\#mm/dd/yyyy\#")

I have done a copy and paste of this code into the command button on
the frmLaborCosts that should open the rptLaborCosts report for
preview. But, it is printing it instead and I can't preview it.
Although, it is still printing out all the records, not just those
for the criteria as entered from the frmLaborFilter or the
frmLaborCosts for that specific set of records to print out.

I can't help thinking that either the code isn't being executed at
all, or there's something else wrong with it. I haven't followed
this thread from top to bottom, so I don't know what-all has gone
before. Please post the complete code from the command button's
Click event procedure. Set a breakpoint and verify, if you would,
that this code is actually being executed. Also post any code that
may exist in the report's Open event.

There is no code in the Open Event of the rptLabotCosts form.

Here is the code in the On click event for the command button that is
on the frmLaborCosts to preview the report. I recreated the button,
and it will now preview, but, all records are shown, not the selected
ones as is on the frmLaborCosts where the button is located:
**********************Begin Code************************
Private Sub cmdRptPreview_Click()
On Error GoTo Err_cmdRptPreview_Click

Dim stDocName As String

stCriteria = "JobDate Between " & _
Format(Forms!frmLaborFilter!Text62, "\#mm/dd/yyyy\#") & _
" AND " & _
Format(Forms!frmLaborFilter!Text64, "\#mm/dd/yyyy\#")

stDocName = "rptLaborCosts"
DoCmd.OpenReport stDocName, acPreview

Exit_cmdRptPreview_Click:
Exit Sub

Err_cmdRptPreview_Click:
MsgBox Err.Description
Resume Exit_cmdRptPreview_Click

End Sub
******************End Code*********************

And now the problem is clear. Although the procedure builds a criteria
string, it doesn't use it in opening the report. Also, it never
declares the variable stCriteria -- presumably you don't have Option
Explicit specified, so Access automatically defines the variable as a
Variant. I suggest you revise the command procedure as follows:

**********************Begin Code************************
Private Sub cmdRptPreview_Click()
On Error GoTo Err_cmdRptPreview_Click

Dim stDocName As String
Dim stCriteria As String

stCriteria = "JobDate Between " & _
Format(Forms!frmLaborFilter!Text62, "\#mm/dd/yyyy\#") & _
" AND " & _
Format(Forms!frmLaborFilter!Text64, "\#mm/dd/yyyy\#")

stDocName = "rptLaborCosts"

DoCmd.OpenReport stDocName, acPreview, , stCriteria

Exit_cmdRptPreview_Click:
Exit Sub

Err_cmdRptPreview_Click:
MsgBox Err.Description
Resume Exit_cmdRptPreview_Click

End Sub
******************End Code*********************

I'm sorry..but, it's still no go. I copy and pasted the revised code above
into the command button as you instructed so that the code would be exactly
as you wrote it, but, when I click the preview button after opening the
frmLaborCosts, it still shows and prints all of the records.

Additionally, there are 4 combo box controls to make selections from for the
record criteria on the frmLaborFilter, such as by ContractorName,
JobLocation, EmpName, and the JobType, with or without specified date
criteria in the Text62 and Text64 controls for the From-To dates. Date
periods are used most of the time, but, not always.

I have tried the process of making a selection from the ContractorName combo
box, entering the date ranges, then clicking on a newly created command
button that has very simple code to open the frmLaborCosts with the selected
criteria, bypassing the other command button, to see if there was perhaps a
problem associated with the code in that button that might be preventing the
correct record information from being passed to the report. But, when I
preview the report, it is still showing all the records. I have checked the
other working reports in one of the other databases to see if there is
something that I have missed in the structure or set up somewhere, and all
seems as it should be in line with this one. I'm trying to troubleshoot and
research as best I can, but, I don't know what else to look for now. There
seems to be a piece of the puzzle missing, or not put in place correctly.

That should settle that problem. I also strongly suggest that you set
the "Require Variable Declaration" option in the VB Editor, so that in
all new modules you are forced to declare any variables. This will
prevent the kind of problems later on that are caused by misspelled or
mistyped variable names.

I have set the "Require Variable Declaration" as you instructed.

Thank you,
Jan

 

[Dirk Goldgar]

I'm sorry..but, it's still no go. I copy and pasted the revised code
above into the command button as you instructed so that the code
would be exactly as you wrote it, but, when I click the preview
button after opening the frmLaborCosts, it still shows and prints all
of the records.

Additionally, there are 4 combo box controls to make selections from
for the record criteria on the frmLaborFilter, such as by
ContractorName, JobLocation, EmpName, and the JobType, with or
without specified date criteria in the Text62 and Text64 controls for
the From-To dates. Date periods are used most of the time, but, not
always.

I have tried the process of making a selection from the
ContractorName combo box, entering the date ranges, then clicking on
a newly created command button that has very simple code to open the
frmLaborCosts with the selected criteria, bypassing the other command
button, to see if there was perhaps a problem associated with the
code in that button that might be preventing the correct record
information from being passed to the report. But, when I preview the
report, it is still showing all the records. I have checked the
other working reports in one of the other databases to see if there
is something that I have missed in the structure or set up somewhere,
and all seems as it should be in line with this one. I'm trying to
troubleshoot and research as best I can, but, I don't know what else
to look for now. There seems to be a piece of the puzzle missing, or
not put in place correctly.

I'm puzzled, Jan. Looking over what you wrote before about the report's
recordsource query -- which I overlooked before -- I see that the query
itself also applies criteria based on the text boxes. So it's hard for
me to see how the report can be showing all records if the form is open
and the text boxes are filled. I don't suppose you'd like to send me a
cut-down, compacted and zipped copy to have a look at? You know where
to send it.

 

[Jan Il]

I'm puzzled, Jan. Looking over what you wrote before about the report's
recordsource query -- which I overlooked before -- I see that the query
itself also applies criteria based on the text boxes. So it's hard for
me to see how the report can be showing all records if the form is open
and the text boxes are filled. I don't suppose you'd like to send me a
cut-down, compacted and zipped copy to have a look at? You know where
to send it.

I'd appreciate it very much. On it's way.

Jan

 

[Dirk Goldgar]

I'm sorry..but, it's still no go. I copy and pasted the revised code
above into the command button as you instructed so that the code
would be exactly as you wrote it, but, when I click the preview
button after opening the frmLaborCosts, it still shows and prints
all of the records.

Additionally, there are 4 combo box controls to make selections from
for the record criteria on the frmLaborFilter, such as by
ContractorName, JobLocation, EmpName, and the JobType, with or
without specified date criteria in the Text62 and Text64 controls
for the From-To dates. Date periods are used most of the time, but,
not always.

I have tried the process of making a selection from the
ContractorName combo box, entering the date ranges, then clicking on
a newly created command button that has very simple code to open the
frmLaborCosts with the selected criteria, bypassing the other
command button, to see if there was perhaps a problem associated
with the code in that button that might be preventing the correct
record information from being passed to the report. But, when I
preview the report, it is still showing all the records. I have
checked the other working reports in one of the other databases to
see if there is something that I have missed in the structure or
set up somewhere, and all seems as it should be in line with this
one. I'm trying to troubleshoot and research as best I can, but, I
don't know what else to look for now. There seems to be a piece of
the puzzle missing, or not put in place correctly.

[I wrote]:
I'm puzzled, Jan. Looking over what you wrote before about the
report's recordsource query -- which I overlooked before -- I see
that the query itself also applies criteria based on the text boxes.
So it's hard for me to see how the report can be showing all records
if the form is open and the text boxes are filled. I don't suppose
you'd like to send me a cut-down, compacted and zipped copy to have
a look at? You know where to send it.

Now I've had a look at the database you sent me, Jan, and I can see that
I've been misinterpreting your statement of the problem. The fact is,
the *date* criteria are working on the report; it's all the *other*
criteria that aren't working. The date criteria are working because,
among other things, they are built into the report's recordsource query
(so you didn't need to build a where-condition for them at all). But
not only are the other criteria -- EmployeeName, ContractorName, etc. --
not being applied to the report, but your code on the filter form is
clearing those combo boxes as soon as it has built the criteria for
opening frmLaborCosts, so you can't even get those values from the
filter form once frmLaborCosts is open.

However, there's a good way to solve this problem. On frmLaborCosts,
change the code behind the "Preview Report" and "Print Report" buttons
to pick up and apply the same Filter that was applied to the form.
Here's code for their Click events that works for me in my tests:

'----- start of revised code -----
Private Sub cmdPrtReport_Click()
On Error GoTo Err_cmdPrtReport_Click

Dim stDocName As String
Dim stCriteria As String

If Me.FilterOn Then
stCriteria = Me.Filter
End If

stDocName = "rptLaborCosts"
DoCmd.OpenReport stDocName, acNormal, , stCriteria

Exit_cmdPrtReport_Click:
Exit Sub

Err_cmdPrtReport_Click:
MsgBox Err.Description
Resume Exit_cmdPrtReport_Click

End Sub


Private Sub cmdRptPreview_Click()
On Error GoTo Err_cmdRptPreview_Click

Dim stDocName As String
Dim stCriteria As String

If Me.FilterOn Then
stCriteria = Me.Filter
End If

stDocName = "rptLaborCosts"

DoCmd.OpenReport stDocName, acPreview, , stCriteria

Exit_cmdRptPreview_Click:
Exit Sub

Err_cmdRptPreview_Click:
MsgBox Err.Description
Resume Exit_cmdRptPreview_Click

End Sub
'----- end of revised code -----

There are a couple of additional points I'd like to bring up. First,
there's a problem with applying criteria on EmpName, as the data are
currently entered. On frmLaborFilter, the combo box "cmbEmpName" is
based on a query that returns employee names in "last name, first name"
format. But the table you're filtering, tblLaborCosts, has only the
last name entered in the EmpName field. So you're never going to find a
match. You'll have to either change cmbEmpName to select only last name
(but then what about multiple employees with the same last name?) or --
preferred -- control the entry of data in tblLaborCosts so that the
EmpName stored is the same "last name, first name" combination as is
returned to the combo box by "qryEmpName". Even better (since names are
so changeable and frequently duplicated) would be to store and filter on
the EmployeeID, rather than the employee name. You could set up the
combo box to let the user *choose* employees by name, but actually
store/filter on the employee ID.

Second, it seems inconsistent to have the recordsource query,
qryLaborCosts, always filtering by date while all other criteria are
applied only in the OnClick code of a command button. I think probably
it would be better to leave the date criteria out of the query itself,
and build them in the Click event of the button on frmLaborFilter. I
wasn't willing to go ahead and present you with example code for this,
because I'm not entirely sure how you want the date criteria to work.
For example, if the user fills in only one date, either the start date
or the end date, would you want to pick all records from that date
forward (or backward), or would you want to pick records for only the
date that was entered? Also, I'm not sure of the logic in the way the
query is summing the data. So I'll leave it to you to decide whether
you want to change any of this or not.

 

[Jan Il]

Hi Dirk!

I'm sorry..but, it's still no go. I copy and pasted the revised code
above into the command button as you instructed so that the code
would be exactly as you wrote it, but, when I click the preview
button after opening the frmLaborCosts, it still shows and prints
all of the records.

Additionally, there are 4 combo box controls to make selections from
for the record criteria on the frmLaborFilter, such as by
ContractorName, JobLocation, EmpName, and the JobType, with or
without specified date criteria in the Text62 and Text64 controls
for the From-To dates. Date periods are used most of the time, but,
not always.

I have tried the process of making a selection from the
ContractorName combo box, entering the date ranges, then clicking on
a newly created command button that has very simple code to open the
frmLaborCosts with the selected criteria, bypassing the other
command button, to see if there was perhaps a problem associated
with the code in that button that might be preventing the correct
record information from being passed to the report. But, when I
preview the report, it is still showing all the records. I have
checked the other working reports in one of the other databases to
see if there is something that I have missed in the structure or
set up somewhere, and all seems as it should be in line with this
one. I'm trying to troubleshoot and research as best I can, but, I
don't know what else to look for now. There seems to be a piece of
the puzzle missing, or not put in place correctly.
[I wrote]:
I'm puzzled, Jan. Looking over what you wrote before about the
report's recordsource query -- which I overlooked before -- I see
that the query itself also applies criteria based on the text boxes.
So it's hard for me to see how the report can be showing all records
if the form is open and the text boxes are filled. I don't suppose
you'd like to send me a cut-down, compacted and zipped copy to have
a look at? You know where to send it.

Now I've had a look at the database you sent me, Jan, and I can see that
I've been misinterpreting your statement of the problem. The fact is,
the *date* criteria are working on the report; it's all the *other*
criteria that aren't working. The date criteria are working because,
among other things, they are built into the report's recordsource query
(so you didn't need to build a where-condition for them at all). But
not only are the other criteria -- EmployeeName, ContractorName, etc. --
not being applied to the report, but your code on the filter form is
clearing those combo boxes as soon as it has built the criteria for
opening frmLaborCosts, so you can't even get those values from the
filter form once frmLaborCosts is open.

Understood. Since I was unable to actually see what was going on at the
time the frmLaborCosts was opened, it did not click in my mind that this was
happening in this manner. That, there was in fact, literally no longer any
criteria in the controls for the report at the moment the frmLaborCosts was
opened.

That part of the code was intended to clear the previous criteria in the
controls of the frmLaborFilter so that when you returned to that form on
closing the frmLaborCosts, the controls would be clear and ready for new
criteria to be entered for the next set of records. Guess I somehow managed

However, there's a good way to solve this problem. On frmLaborCosts,
change the code behind the "Preview Report" and "Print Report" buttons
to pick up and apply the same Filter that was applied to the form.
Here's code for their Click events that works for me in my tests:

'----- start of revised code -----
Private Sub cmdPrtReport_Click()
On Error GoTo Err_cmdPrtReport_Click

Dim stDocName As String
Dim stCriteria As String

If Me.FilterOn Then
stCriteria = Me.Filter
End If

stDocName = "rptLaborCosts"
DoCmd.OpenReport stDocName, acNormal, , stCriteria

Exit_cmdPrtReport_Click:
Exit Sub

Err_cmdPrtReport_Click:
MsgBox Err.Description
Resume Exit_cmdPrtReport_Click

End Sub


Private Sub cmdRptPreview_Click()
On Error GoTo Err_cmdRptPreview_Click

Dim stDocName As String
Dim stCriteria As String

If Me.FilterOn Then
stCriteria = Me.Filter
End If

stDocName = "rptLaborCosts"

DoCmd.OpenReport stDocName, acPreview, , stCriteria

Exit_cmdRptPreview_Click:
Exit Sub

Err_cmdRptPreview_Click:
MsgBox Err.Description
Resume Exit_cmdRptPreview_Click

End Sub
'----- end of revised code -----

I have copied and pasted the above codes into the respective buttons as you
instructed, and now the Preview Report is returning only the selected
records, and properly printing only those select records, as they should.

There are a couple of additional points I'd like to bring up. First,
there's a problem with applying criteria on EmpName, as the data are
currently entered. On frmLaborFilter, the combo box "cmbEmpName" is
based on a query that returns employee names in "last name, first name"
format. But the table you're filtering, tblLaborCosts, has only the
last name entered in the EmpName field. So you're never going to find a
match. You'll have to either change cmbEmpName to select only last name
(but then what about multiple employees with the same last name?) or --
preferred -- control the entry of data in tblLaborCosts so that the
EmpName stored is the same "last name, first name" combination as is
returned to the combo box by "qryEmpName". Even better (since names are
so changeable and frequently duplicated) would be to store and filter on
the EmployeeID, rather than the employee name. You could set up the
combo box to let the user *choose* employees by name, but actually
store/filter on the employee ID.

Agreed. In giving this some deeper thought based on your recommendations,
and in further consideration of how the Accounting dept. will be processing
this information as well, you are indeed correct that it would be best to
use the company Employee ID number for recording and filtering the
employees. All company ID numbers are unique, and only used once, so there
could never be a duplication. Accounting actually uses the employee ID for
their billing purposes, thus, it would make it more efficient for our
departments to cross-reference this data if we also utilized this method in
our recording procedures.
Consider it so.

Second, it seems inconsistent to have the recordsource query,
qryLaborCosts, always filtering by date while all other criteria are
applied only in the OnClick code of a command button. I think probably
it would be better to leave the date criteria out of the query itself,
and build them in the Click event of the button on frmLaborFilter. I
wasn't willing to go ahead and present you with example code for this,
because I'm not entirely sure how you want the date criteria to work.
For example, if the user fills in only one date, either the start date
or the end date, would you want to pick all records from that date
forward (or backward), or would you want to pick records for only the
date that was entered? Also, I'm not sure of the logic in the way the
query is summing the data. So I'll leave it to you to decide whether
you want to change any of this or not.

This was initially set up as a standard format for all the filter forms
where
date range criteria was is required to filter some records. However, if no
date range was needed, then the date boxes could be left empty. Normally,
the standard date range format is From a specific date To a specific date
'forward' of the start/From date. However, the To date can be either before
or after the From date entry. If only one specific date was needed, the date
could be entered in both of the date boxes.

But, yes, I can see the benefit of having all the necessary filtering
criteria contained in the code of the ExpResearch button on this form as you
suggest, and then the extended criteria in the query could be eliminated.
This would simplify the process, and make it more efficient. As I have not
set up criteria in this manner before, I am still experimenting with both
tried and new methods. I like to stretch what I know and learn new methods
to make things more efficient and less cumbersome. Thus... the hole in my
foot here. <g>

I am not real sure what you are referring to in regards to the way the query
is summing the data....the dates? Or overall?

Thank you very much for all your time and help, I really appreciate it. It
has clarified several areas regarding filtering and processing criteria in
relation to the form and report functions I was not aware of before.

Jan