Access 2007 Report generation in VBA

[Preston]

Hi,
I have a question about making a report in VBA. I have a db of tech hours
that I need to make timesheets from once a week. In making the timesheets I
have generated a recordset of selected techs that the code loops through and
makes a timesheet for each one by changing the reports recordsource to a new
sql statement. The problem I am having is that the code doesn't wait for any
input or user interaction before wiping the previous report and generating
the next one. I want the code to wait in preview mode until the report is
closed or printed and then generate the next timesheet. Is there a way to do
this? I have used a msgbox in the loop for each tech but that seems like a
duct tape solution and the msgbox pops up in the middle of the screen. Can i
have the code wait for the report to be closed before generating the next
report in the series?
Thank you for any help you can give on this problem.
Preston

 

[Albert D. Kallal]

I think this is in one case where I would produce a list and display the
result(s) in a continuous form.

The user then would be able to free to look at all the names in this list,
use the page up/down to browse through the list, and then if they want more
details for a particular person, then they simply click on a button
provided.

There is some screen shots of continuous forms here, and you can see that
often a button is provided to launch a form that displays additional
details.

I also agree that having a msgbox is not only a kluge, but that approach
also does not allow the user to any way to quit, and go on to
do something else if they are interrupted by the phone. furthermore, I have
to assume that since the user will preview a report before
printing, then the number of reports can't be very large.
And, once again it thus makes sense to display the "list" of people's
here, and then each user's name can be clicked on to display the report.

So, use a continues form, and place a In beside each persons name.

In addition, in place of a continues form, you can also use a report in
access 2007 to achieve the same goal. (you use a hyperlink field for the
name).

Thus, your initial report would display all possible names, and then
clicking
on a name in the report would "drill" down further.

 

[Klatuu]

This was an interesting enough post that I had to set up a test environment
and see what it would take. Here is what I came up with.
First, you don't need a different SQL statement for each employee. You just
use one unfiltered query. The filtering is done using the Where arguemnet of
the OpenReport method. You do need a list of all the employees that will be
included so the code can loop through all of them. They way I did that was
using a query as a one column recordset. The column is the name of the field
that I want the reports ordered by. If there is other filtering criteria,
you would need to add it to this query and remove it from the report's record
source query.

The code is behind a command button that starts the process and opens the
first report filtered by the first employee. It then just sits and loops
until you close the report, then it moves to the next record and opens the
report for the next query. It does that until it has presented each report
or if you click another button the form to cancel the reports.
****************************************
Dim blnCancelReport As Boolean

Private Sub Command2_Click()
Dim rst As DAO.Recordset
Dim rpts As AllReports

blnCancelReport = False

Set rst = CurrentDb.OpenRecordset("SELECT MainName, " & _
"ClientID FROM tblClient ORDER BY MainName;")
Set rpts = CurrentProject.AllReports
With rst
Do While Not .EOF And Not blnCancelReport
DoCmd.OpenReport "Report2", acViewPreview, , "[ClientID] = " &
!ClientID
Do Until Not rpts("Report2").IsLoaded
DoEvents
If blnCancelReport Then
DoCmd.Close acReport, "Report2", acSaveNo
Exit Do
End If
Loop
.MoveNext
Loop
.Close
End With
Set rst = Nothing
Set rpts = Nothing
End Sub

Private Sub Command4_Click()
blnCancelReport = True
End Sub
************************************
If you have any questions on this, post back.

 

[Preston]

Thank you both very much for your help. I kind of like your solution Klatuu.
I just didn't know about the idea of having the code loop while waiting for
user input. Is this acceptable in developing? Does it chew system resources?
Ultimately I ended up adding a column to the tech table as boolean that gets
selected whenever the tech is selected on the "create timesheets" form and
cleared back to false whenever the form is closed. Then based on that i
generate a recordset and create a report for each record. Here is the code
incase you would like to see it:


Sub CreateTimeSheets(constView As AcView)
On Error GoTo ErrorHandler

Dim cnn1 As ADODB.Connection
Dim myRecordSet As New ADODB.Recordset
Dim mySQLtech As String
Dim varTechName As String
Dim datWeekEnd As Date
Dim strPay As String

'create connection
Set cnn1 = CurrentProject.Connection

'create recordset
myRecordSet.ActiveConnection = cnn1

'create SQL string to select only desired technicians
Dim mySQL As String
mySQL = "SELECT [tblTechs].[techID], [tblTechs].[First Name],
[tblTechs].[Last Name] FROM tblTechs"
mySQL = mySQL & " WHERE tblTechs.CreateTimeSheet = True"

'open recordset with desired technician ids included
myRecordSet.Open Source:=mySQL, CursorType:=adOpenStatic

'check for error condition and exit sub if error will result
If myRecordSet.RecordCount = 0 Then
MsgBox "No technicians are selected", vbOKOnly
Exit Sub
End If

'get date from form and decide whether or not to filter
'on the date in the txtbox
If IsNull([Forms]![frmtimesheet].[txtWeekEnd]) Then
mySQLtech = "SELECT [tblSvcOrdersDet].[paid],
[tblSvcOrdersDet].[DateWorked], [tblSvcOrdersDet].[SvcOrder], " _
& "[tblSvcOrders].[ClassID], [tblSvcOrders].[Customer],
[tblSvcOrdersDet].[Reg Hours], " _
& "[tblSvcOrdersDet].[OT Hours], [tblSvcOrders].[JobNumber],
[tblSvcOrders].[SODate], [tblSvcOrdersDet].[tech] " _
& "FROM tblSvcOrders INNER JOIN tblSvcOrdersDet ON
tblSvcOrders.SvcOrder=tblSvcOrdersDet.SvcOrder " _
& "WHERE [tblSvcOrdersDet].[tech] = "
Else
datWeekEnd = [Forms]![frmtimesheet].[txtWeekEnd]
mySQLtech = "SELECT [tblSvcOrdersDet].[paid],
[tblSvcOrdersDet].[DateWorked], [tblSvcOrdersDet].[SvcOrder], " _
& "[tblSvcOrders].[ClassID], [tblSvcOrders].[Customer],
[tblSvcOrdersDet].[Reg Hours], " _
& "[tblSvcOrdersDet].[OT Hours], [tblSvcOrders].[JobNumber],
[tblSvcOrders].[SODate], [tblSvcOrdersDet].[tech] " _
& "FROM tblSvcOrders INNER JOIN tblSvcOrdersDet ON
tblSvcOrders.SvcOrder=tblSvcOrdersDet.SvcOrder " _
& "WHERE [tblSvcOrdersDet].[DateWorked] BETWEEN #" & CDate(DateAdd("d",
-6, datWeekEnd)) & "# AND #" _
& CDate(datWeekEnd) & "# And [tblSvcOrdersDet].[tech] = "
End If

'set variable for filtering based on paid or unpaid status
If [Forms]![frmtimesheet].[chkUnpaid] = False And
[Forms]![frmtimesheet].[chkPaid] = False Then
MsgBox "No records selected. Please select either Unpaid or Paid or
Both.", vbOKOnly
Exit Sub
ElseIf [Forms]![frmtimesheet].[chkUnpaid] = True And
[Forms]![frmtimesheet].[chkPaid] = False Then
strPay = " AND [tblsvcordersdet].[paid] = false"
ElseIf [Forms]![frmtimesheet].[chkUnpaid] = False And
[Forms]![frmtimesheet].[chkPaid] = True Then
strPay = " AND [tblsvcordersdet].[paid] = true"
Else
strPay = ""
End If

For i = 0 To myRecordSet.RecordCount - 1

DoCmd.OpenReport "rptTimeSheet", acViewDesign, windowmode:=acHidden

Reports("rptTimeSheet").RecordSource = mySQLtech & myRecordSet.Fields(0)
& strPay & ";"
Reports("rptTimeSheet").txtTechName.ControlSource = "=DLookup(""[First
Name]"", " _
& """[tblTechs]"", ""[techid]=" & myRecordSet.Fields(0) & """) & "" ""
& DLookup(""[Last Name]"", " _
& """[tblTechs]"", ""[techid]=" & myRecordSet.Fields(0) & """)"

DoCmd.Close acReport, "rptTimeSheet", acSaveYes

DoCmd.OpenReport "rptTimeSheet", constView, windowmode:=acDialog

myRecordSet.MoveNext

Next


'remove used objects from memory
Set myRecordSet = Nothing
Set cnn1 = Nothing
If IsOpen("rptTimeSheet", acReport) Then DoCmd.Close acReport,
"rptTimeSheet"

Exit Sub

ErrorHandler:
' Display error information.
MsgBox "Error number " & Err.Number & ": " & Err.Description
' Resume with statement following occurrence of error.
Exit Sub
End Sub

This was an interesting enough post that I had to set up a test environment
and see what it would take. Here is what I came up with.
First, you don't need a different SQL statement for each employee. You just
use one unfiltered query. The filtering is done using the Where arguemnet of
the OpenReport method. You do need a list of all the employees that will be
included so the code can loop through all of them. They way I did that was
using a query as a one column recordset. The column is the name of the field
that I want the reports ordered by. If there is other filtering criteria,
you would need to add it to this query and remove it from the report's record
source query.

The code is behind a command button that starts the process and opens the
first report filtered by the first employee. It then just sits and loops
until you close the report, then it moves to the next record and opens the
report for the next query. It does that until it has presented each report
or if you click another button the form to cancel the reports.
****************************************
Dim blnCancelReport As Boolean

Private Sub Command2_Click()
Dim rst As DAO.Recordset
Dim rpts As AllReports

blnCancelReport = False

Set rst = CurrentDb.OpenRecordset("SELECT MainName, " & _
"ClientID FROM tblClient ORDER BY MainName;")
Set rpts = CurrentProject.AllReports
With rst
Do While Not .EOF And Not blnCancelReport
DoCmd.OpenReport "Report2", acViewPreview, , "[ClientID] = " &
!ClientID
Do Until Not rpts("Report2").IsLoaded
DoEvents
If blnCancelReport Then
DoCmd.Close acReport, "Report2", acSaveNo
Exit Do
End If
Loop
.MoveNext
Loop
.Close
End With
Set rst = Nothing
Set rpts = Nothing
End Sub

Private Sub Command4_Click()
blnCancelReport = True
End Sub
************************************
If you have any questions on this, post back.
--
Dave Hargis, Microsoft Access MVP

Hi,
I have a question about making a report in VBA. I have a db of tech hours
that I need to make timesheets from once a week. In making the timesheets I
have generated a recordset of selected techs that the code loops through and
makes a timesheet for each one by changing the reports recordsource to a new
sql statement. The problem I am having is that the code doesn't wait for any
input or user interaction before wiping the previous report and generating
the next one. I want the code to wait in preview mode until the report is
closed or printed and then generate the next timesheet. Is there a way to do
this? I have used a msgbox in the loop for each tech but that seems like a
duct tape solution and the msgbox pops up in the middle of the screen. Can i
have the code wait for the report to be closed before generating the next
report in the series?
Thank you for any help you can give on this problem.
Preston

 

[Klatuu]

Duing my experimenting, I tried using the acDialog when opening the report to
stop the loop. The reason I dropped the idea is because you can't resize the
report window or select printing options for the report.

If you notice the DoEvents in my loop, it is there to be sure other
processes get a turn. There is no real downside to that approach.

The only problem I have with your code is opening the report in design mode
to make changes to it on the fly. I would avoid that if I could. As I
stated before, I don't need a separate record source for each employee. As I
designed it, you only need the one query. What you are settin up is a
maintenance nightmare. Every time you add or loose an employee, you have to
change your application. Your application should not be data dependant.
That is a serious design flaw.

--
Dave Hargis, Microsoft Access MVP

Thank you both very much for your help. I kind of like your solution Klatuu.
I just didn't know about the idea of having the code loop while waiting for
user input. Is this acceptable in developing? Does it chew system resources?
Ultimately I ended up adding a column to the tech table as boolean that gets
selected whenever the tech is selected on the "create timesheets" form and
cleared back to false whenever the form is closed. Then based on that i
generate a recordset and create a report for each record. Here is the code
incase you would like to see it:


Sub CreateTimeSheets(constView As AcView)
On Error GoTo ErrorHandler

Dim cnn1 As ADODB.Connection
Dim myRecordSet As New ADODB.Recordset
Dim mySQLtech As String
Dim varTechName As String
Dim datWeekEnd As Date
Dim strPay As String

'create connection
Set cnn1 = CurrentProject.Connection

'create recordset
myRecordSet.ActiveConnection = cnn1

'create SQL string to select only desired technicians
Dim mySQL As String
mySQL = "SELECT [tblTechs].[techID], [tblTechs].[First Name],
[tblTechs].[Last Name] FROM tblTechs"
mySQL = mySQL & " WHERE tblTechs.CreateTimeSheet = True"

'open recordset with desired technician ids included
myRecordSet.Open Source:=mySQL, CursorType:=adOpenStatic

'check for error condition and exit sub if error will result
If myRecordSet.RecordCount = 0 Then
MsgBox "No technicians are selected", vbOKOnly
Exit Sub
End If

'get date from form and decide whether or not to filter
'on the date in the txtbox
If IsNull([Forms]![frmtimesheet].[txtWeekEnd]) Then
mySQLtech = "SELECT [tblSvcOrdersDet].[paid],
[tblSvcOrdersDet].[DateWorked], [tblSvcOrdersDet].[SvcOrder], " _
& "[tblSvcOrders].[ClassID], [tblSvcOrders].[Customer],
[tblSvcOrdersDet].[Reg Hours], " _
& "[tblSvcOrdersDet].[OT Hours], [tblSvcOrders].[JobNumber],
[tblSvcOrders].[SODate], [tblSvcOrdersDet].[tech] " _
& "FROM tblSvcOrders INNER JOIN tblSvcOrdersDet ON
tblSvcOrders.SvcOrder=tblSvcOrdersDet.SvcOrder " _
& "WHERE [tblSvcOrdersDet].[tech] = "
Else
datWeekEnd = [Forms]![frmtimesheet].[txtWeekEnd]
mySQLtech = "SELECT [tblSvcOrdersDet].[paid],
[tblSvcOrdersDet].[DateWorked], [tblSvcOrdersDet].[SvcOrder], " _
& "[tblSvcOrders].[ClassID], [tblSvcOrders].[Customer],
[tblSvcOrdersDet].[Reg Hours], " _
& "[tblSvcOrdersDet].[OT Hours], [tblSvcOrders].[JobNumber],
[tblSvcOrders].[SODate], [tblSvcOrdersDet].[tech] " _
& "FROM tblSvcOrders INNER JOIN tblSvcOrdersDet ON
tblSvcOrders.SvcOrder=tblSvcOrdersDet.SvcOrder " _
& "WHERE [tblSvcOrdersDet].[DateWorked] BETWEEN #" & CDate(DateAdd("d",
-6, datWeekEnd)) & "# AND #" _
& CDate(datWeekEnd) & "# And [tblSvcOrdersDet].[tech] = "
End If

'set variable for filtering based on paid or unpaid status
If [Forms]![frmtimesheet].[chkUnpaid] = False And
[Forms]![frmtimesheet].[chkPaid] = False Then
MsgBox "No records selected. Please select either Unpaid or Paid or
Both.", vbOKOnly
Exit Sub
ElseIf [Forms]![frmtimesheet].[chkUnpaid] = True And
[Forms]![frmtimesheet].[chkPaid] = False Then
strPay = " AND [tblsvcordersdet].[paid] = false"
ElseIf [Forms]![frmtimesheet].[chkUnpaid] = False And
[Forms]![frmtimesheet].[chkPaid] = True Then
strPay = " AND [tblsvcordersdet].[paid] = true"
Else
strPay = ""
End If

For i = 0 To myRecordSet.RecordCount - 1

DoCmd.OpenReport "rptTimeSheet", acViewDesign, windowmode:=acHidden

Reports("rptTimeSheet").RecordSource = mySQLtech & myRecordSet.Fields(0)
& strPay & ";"
Reports("rptTimeSheet").txtTechName.ControlSource = "=DLookup(""[First
Name]"", " _
& """[tblTechs]"", ""[techid]=" & myRecordSet.Fields(0) & """) & "" ""
& DLookup(""[Last Name]"", " _
& """[tblTechs]"", ""[techid]=" & myRecordSet.Fields(0) & """)"

DoCmd.Close acReport, "rptTimeSheet", acSaveYes

DoCmd.OpenReport "rptTimeSheet", constView, windowmode:=acDialog

myRecordSet.MoveNext

Next


'remove used objects from memory
Set myRecordSet = Nothing
Set cnn1 = Nothing
If IsOpen("rptTimeSheet", acReport) Then DoCmd.Close acReport,
"rptTimeSheet"

Exit Sub

ErrorHandler:
' Display error information.
MsgBox "Error number " & Err.Number & ": " & Err.Description
' Resume with statement following occurrence of error.
Exit Sub
End Sub

This was an interesting enough post that I had to set up a test environment
and see what it would take. Here is what I came up with.
First, you don't need a different SQL statement for each employee. You just
use one unfiltered query. The filtering is done using the Where arguemnet of
the OpenReport method. You do need a list of all the employees that will be
included so the code can loop through all of them. They way I did that was
using a query as a one column recordset. The column is the name of the field
that I want the reports ordered by. If there is other filtering criteria,
you would need to add it to this query and remove it from the report's record
source query.

The code is behind a command button that starts the process and opens the
first report filtered by the first employee. It then just sits and loops
until you close the report, then it moves to the next record and opens the
report for the next query. It does that until it has presented each report
or if you click another button the form to cancel the reports.
****************************************
Dim blnCancelReport As Boolean

Private Sub Command2_Click()
Dim rst As DAO.Recordset
Dim rpts As AllReports

blnCancelReport = False

Set rst = CurrentDb.OpenRecordset("SELECT MainName, " & _
"ClientID FROM tblClient ORDER BY MainName;")
Set rpts = CurrentProject.AllReports
With rst
Do While Not .EOF And Not blnCancelReport
DoCmd.OpenReport "Report2", acViewPreview, , "[ClientID] = " &
!ClientID
Do Until Not rpts("Report2").IsLoaded
DoEvents
If blnCancelReport Then
DoCmd.Close acReport, "Report2", acSaveNo
Exit Do
End If
Loop
.MoveNext
Loop
.Close
End With
Set rst = Nothing
Set rpts = Nothing
End Sub

Private Sub Command4_Click()
blnCancelReport = True
End Sub
************************************
If you have any questions on this, post back.
--
Dave Hargis, Microsoft Access MVP

Hi,
I have a question about making a report in VBA. I have a db of tech hours
that I need to make timesheets from once a week. In making the timesheets I
have generated a recordset of selected techs that the code loops through and
makes a timesheet for each one by changing the reports recordsource to a new
sql statement. The problem I am having is that the code doesn't wait for any
input or user interaction before wiping the previous report and generating
the next one. I want the code to wait in preview mode until the report is
closed or printed and then generate the next timesheet. Is there a way to do
this? I have used a msgbox in the loop for each tech but that seems like a
duct tape solution and the msgbox pops up in the middle of the screen. Can i
have the code wait for the report to be closed before generating the next
report in the series?
Thank you for any help you can give on this problem.
Preston

 

[Preston]

Dave,
I agree with you. Opening the report in design mode to edit the
recordsource is not my favorite part. However, the operation requires several
other filters that I didn't see as easy to filter on. First of all, text
boxes on the report update with data based on which tech the report is being
run on. There is also a date filter and a paid/unpaid filter which need to be
filtered on as well. Also, there is a text box on the report that will say
the date range which is being filtered upon. Can all of this functionality be
replicated by using a where filter on the opening of the report? I like your
code though. It is very clean and concise. I am completely new at this and am
actually learning it through doing it so I imagine my methods aren't the
greatest. Thank you again for the help you've given me.
Preston

 

[Klatuu]

The date and paid/unpaid filters could be done at the record source query
level.
For the text boxes that show the selected date ranges, you can reference the
form form which you run the report:
=Forms!FormName!TextBoxName in the control source property of the text box
on the report.

This sentence I don't quite understand:
text boxes on the report update with data based on which tech the report is
being
run on.

Wouln't those be fields in the query, or am I missing something here?

It is reasonable to use a combination of query level filtering and Where
filtering in the OpenReport. When you have filtering that is common to all
records, then the filtering should be in the query. The Where argument is
best used to specific filtering at run time. I think this is what you need
to consider for this situation.

 

[Preston]

ok thank you,
i understand what you are saying and will give it a go. what i was trying
to say is that the text box displays the name of the tech that the report is
being generated for. i guess i could update that with the .value = [First
name] & " " & [Last Name] from the recordset. I am having trouble running
your code though. When it starts it opens the create time sheet form (the
form that has the button i click to initiate the code) in modal (even though
this property is not set) and locks me out. it then opens the report in the
background and seems to fail. i will keep trying to get it to work. thank you
Preston

 

[Klatuu]

i could update that with the .value = [First name] & " " & [Last Name] from
the recordset
Just put = [First name] & " " & [Last Name] in the control source of the
text box on your report where you want it.

The code I originally posted does not open any form, so that is happening
somewhere else. My code should be in the click event of a command button
that does nothing more than run the report repeatedly until all reports have
been run or you cancel the reports. The form opening in dialog mode is
happening somewhere else, unless you have changed the code I posted to
include opening a form which would not be correct.

 

[Preston]

dave,
thank you again for all of your help. i wish i had an architectural view of
what is happening because i don't know what is available to the report and
what isn't. where the dataset comes from, etc. something kind of like a
network layer diagram would be nice. is there anything out there that i can
look at or see the flow of information? that would lead me to be able to draw
my own conclusions about what data is available where and when.
preston

 

[Preston]

wow,
what a sweet change from the bulky thing i was trying to run. i got it
working nice and since the techs names aren't part of the query (and would
require two inner joins which i don' t have the skill to write) i used
dlookup in the text box to display their names. the code works very well and
is exactly what i wanted from the program in the first place. thank you for
your help. now all i have to do is add the two other filters to filter based
on paid/unpaid and date and it should work very well. thank you again
Preston