On NoData

[Bruce Rodtnick]

I have a report with seven subReports, Sunday thru Saturday, that display
the information for those days. Each subReport will fill out with 8 lines
even if there is only enough data for two lines, etc.

Monday
11:00
3:00
____
____
____
____
____
____

My code to make the extra blank lines is:

Private Sub Report_NoData(Cancel As Integer)
Dim Cdb As DAO.Database
Dim rsJobs As DAO.Recordset
'Rest the row counter
bRowCounter = 0
'Get the count of the max rows that have values
Set Cdb = CurrentDb
'If IsNull(TimeID) Then

' Else


Set rsJobs = Cdb.OpenRecordset("Select Count([TimeID])as Jobs from qryNoData
WHERE StaffID = " & Reports.WeeklyTimeSheet.txtStaffID)

bJobCount = Nz(rsJobs!Jobs)
rsJobs.Close
Set rsJobs = Nothing
Set Cdb = Nothing
'Reset all controls to display in black for the first Record
Me!txtDate.ForeColor = COLOR_BLACK
Me!txtFrom.ForeColor = COLOR_BLACK
Me!txtTo.ForeColor = COLOR_BLACK
'Me!ActHoursA.ForeColor = COLOR_BLACK
'Me!TasksA.ForeColor = COLOR_BLACK
End Sub


Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)

bRowCounter = bRowCounter + 1
If bRowCounter < bJobCount Then
Exit Sub
Else
If bRowCounter < MAX_ROWS Then
Me.NextRecord = False
End If
End If
If (bRowCounter > bJobCount) Then
'Note the control names have been aliased with a suffix "A"
Me!txtDate.ForeColor = COLOR_WHITE
Me!txtFrom.ForeColor = COLOR_WHITE
Me!txtTo.ForeColor = COLOR_WHITE
' Me!ActHoursA.ForeColor = COLOR_WHITE
'Me!TasksA.ForeColor = COLOR_WHITE
End If
End Sub

It works great except when there is not any data for that day and then I get
nothing, but I still want 8 blank lines. I've tried:
Making an 8 record table and changing the RecordSource of the subform on
NoData.
Making a NoData subFrom and changing the SubForm on NoData.

None have worked. Any ideas of how I can put these 8 lines on a day that
has no data?

Bruce Rodtnick

 

[Allen Browne]

Bruce, it would be better to find a way to get the 8 records into the
subreport's source query than to try to fudge it with code.

I assume that the subreport is linked to the main report by a date/time
field that has a date value only (no time), and you now want to show the 8
hours of the workday in the subreport.

1. Create a table with one Number field (size: Long Integer), named (say)
CountID. Save the table with the name tblCount. Enter 8 records that
represent the hours of your work day on a 24-hour clock, e.g. 8, 9, 10, 11,
12, 13, 14, ...

2. Create a query that contains both tblCount and your original table. In
the upper pane of query design, drag the CountID field from tblCount and
drop onto the time field in your original table.

3. You should now see a line joining the 2 tables. Doubleclick that line.
Access offers a dialog with 3 choices. Choose the one that says:
All records from tblCount, and any matches from ...
This is called an outer join, and causes all 8 numbers to be included in the
output.

Now we need to convert the time value into the actual hour, so that the
appointment shows up on the grid in the right hour, and even appointments
that are not on the hour show up. Once we do this, you will not be able to
view the query graphically and more, so add any other fields you need to the
query output grid.

4. Switch the query to SQL View (View menu.)

5. In the FROM clause, you will see something like this:
FROM tblCount LEFT JOIN Table1 ON tblCount.CountID = Table1.AppointTime
Change the last part by inserting Hour() around your table and field name,
e.g.:
... ON tblCount.CountID = Hour(Table1.AppointTime)

6. Save and close the query.

7. Open your subreport in design view, and change its Record Source to this
query.

The query will now show all of the hours you entered into tblCount, and any
appointments at matching hours. (Note that an appointment outside of those
hours will not show up.)

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

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

I have a report with seven subReports, Sunday thru Saturday, that display
the information for those days. Each subReport will fill out with 8 lines
even if there is only enough data for two lines, etc.

Monday
11:00
3:00
____
____
____
____
____
____

My code to make the extra blank lines is:

Private Sub Report_NoData(Cancel As Integer)
Dim Cdb As DAO.Database
Dim rsJobs As DAO.Recordset
'Rest the row counter
bRowCounter = 0
'Get the count of the max rows that have values
Set Cdb = CurrentDb
'If IsNull(TimeID) Then

' Else


Set rsJobs = Cdb.OpenRecordset("Select Count([TimeID])as Jobs from
qryNoData WHERE StaffID = " & Reports.WeeklyTimeSheet.txtStaffID)

bJobCount = Nz(rsJobs!Jobs)
rsJobs.Close
Set rsJobs = Nothing
Set Cdb = Nothing
'Reset all controls to display in black for the first Record
Me!txtDate.ForeColor = COLOR_BLACK
Me!txtFrom.ForeColor = COLOR_BLACK
Me!txtTo.ForeColor = COLOR_BLACK
'Me!ActHoursA.ForeColor = COLOR_BLACK
'Me!TasksA.ForeColor = COLOR_BLACK
End Sub


Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)

bRowCounter = bRowCounter + 1
If bRowCounter < bJobCount Then
Exit Sub
Else
If bRowCounter < MAX_ROWS Then
Me.NextRecord = False
End If
End If
If (bRowCounter > bJobCount) Then
'Note the control names have been aliased with a suffix "A"
Me!txtDate.ForeColor = COLOR_WHITE
Me!txtFrom.ForeColor = COLOR_WHITE
Me!txtTo.ForeColor = COLOR_WHITE
' Me!ActHoursA.ForeColor = COLOR_WHITE
'Me!TasksA.ForeColor = COLOR_WHITE
End If
End Sub

It works great except when there is not any data for that day and then I
get nothing, but I still want 8 blank lines. I've tried:
Making an 8 record table and changing the RecordSource of the subform on
NoData.
Making a NoData subFrom and changing the SubForm on NoData.

None have worked. Any ideas of how I can put these 8 lines on a day that
has no data?

Bruce Rodtnick

 

[Bruce Rodtnick]

Thanks for your response. What I'm tyrying to do is duplicate a government
time sheet that has eight rows and seven days. When I do something like
this I get a record on Monday and Tuesday goes down a line. I need Monday
and Tuesday to report on the same line. That's why I went with seven
different subs Sunday thru Saturday but as you Saturday and Sunday don't
have data and nothing shows up..

I like the idea of the Count table. Could I out together a query that will
always bring back eight records and those days that have a time will be
inserted first?

Bruce

Bruce, it would be better to find a way to get the 8 records into the
subreport's source query than to try to fudge it with code.

I assume that the subreport is linked to the main report by a date/time
field that has a date value only (no time), and you now want to show the 8
hours of the workday in the subreport.

1. Create a table with one Number field (size: Long Integer), named (say)
CountID. Save the table with the name tblCount. Enter 8 records that
represent the hours of your work day on a 24-hour clock, e.g. 8, 9, 10,
11,
12, 13, 14, ...

2. Create a query that contains both tblCount and your original table. In
the upper pane of query design, drag the CountID field from tblCount and
drop onto the time field in your original table.

3. You should now see a line joining the 2 tables. Doubleclick that line.
Access offers a dialog with 3 choices. Choose the one that says:
All records from tblCount, and any matches from ...
This is called an outer join, and causes all 8 numbers to be included in
the
output.

Now we need to convert the time value into the actual hour, so that the
appointment shows up on the grid in the right hour, and even appointments
that are not on the hour show up. Once we do this, you will not be able to
view the query graphically and more, so add any other fields you need to
the
query output grid.

4. Switch the query to SQL View (View menu.)

5. In the FROM clause, you will see something like this:
FROM tblCount LEFT JOIN Table1 ON tblCount.CountID = Table1.AppointTime
Change the last part by inserting Hour() around your table and field name,
e.g.:
... ON tblCount.CountID = Hour(Table1.AppointTime)

6. Save and close the query.

7. Open your subreport in design view, and change its Record Source to
this
query.

The query will now show all of the hours you entered into tblCount, and
any
appointments at matching hours. (Note that an appointment outside of those
hours will not show up.)

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

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

I have a report with seven subReports, Sunday thru Saturday, that display
the information for those days. Each subReport will fill out with 8 lines
even if there is only enough data for two lines, etc.

Monday
11:00
3:00
____
____
____
____
____
____

My code to make the extra blank lines is:

Private Sub Report_NoData(Cancel As Integer)
Dim Cdb As DAO.Database
Dim rsJobs As DAO.Recordset
'Rest the row counter
bRowCounter = 0
'Get the count of the max rows that have values
Set Cdb = CurrentDb
'If IsNull(TimeID) Then

' Else


Set rsJobs = Cdb.OpenRecordset("Select Count([TimeID])as Jobs from
qryNoData WHERE StaffID = " & Reports.WeeklyTimeSheet.txtStaffID)

bJobCount = Nz(rsJobs!Jobs)
rsJobs.Close
Set rsJobs = Nothing
Set Cdb = Nothing
'Reset all controls to display in black for the first Record
Me!txtDate.ForeColor = COLOR_BLACK
Me!txtFrom.ForeColor = COLOR_BLACK
Me!txtTo.ForeColor = COLOR_BLACK
'Me!ActHoursA.ForeColor = COLOR_BLACK
'Me!TasksA.ForeColor = COLOR_BLACK
End Sub


Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)

bRowCounter = bRowCounter + 1
If bRowCounter < bJobCount Then
Exit Sub
Else
If bRowCounter < MAX_ROWS Then
Me.NextRecord = False
End If
End If
If (bRowCounter > bJobCount) Then
'Note the control names have been aliased with a suffix "A"
Me!txtDate.ForeColor = COLOR_WHITE
Me!txtFrom.ForeColor = COLOR_WHITE
Me!txtTo.ForeColor = COLOR_WHITE
' Me!ActHoursA.ForeColor = COLOR_WHITE
'Me!TasksA.ForeColor = COLOR_WHITE
End If
End Sub

It works great except when there is not any data for that day and then I
get nothing, but I still want 8 blank lines. I've tried:
Making an 8 record table and changing the RecordSource of the subform on
NoData.
Making a NoData subFrom and changing the SubForm on NoData.

None have worked. Any ideas of how I can put these 8 lines on a day that
has no data?

Bruce Rodtnick

 

[Allen Browne]

The query I gave you will always generate 8 records in the subreport,
because the count table ensures those records.

I think you are saying that your main report now fails to generate records
for the Saturday and Sunday, because its RecordSource does not have these
days?

The same basic principle applies. If those dates don't exist in your data,
they have to come from somewhere, so you will need a table that contains all
possible dates, and outer-join that to your dates to ensure that every date
is present. You can programmatically add the 21 years worth of dates by
making a table named tblDate with just one a Date/Time field named TheDate
(marked as primary key), and then running this code to fill in the dates:

Function MakeDates()
Dim dt As Date
Dim rs As DAO.Recordset

Set rs = DBEngine(0)(0).OpenRecordset("tblDate")
With rs
For dt = #1/1/2000# To #12/31/2020#
.AddNew
!TheDate = dt
.Update
Next
End With
rs.Close
Set rs = Nothing
End Function

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

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

Thanks for your response. What I'm tyrying to do is duplicate a
government time sheet that has eight rows and seven days. When I do
something like this I get a record on Monday and Tuesday goes down a line.
I need Monday and Tuesday to report on the same line. That's why I went
with seven different subs Sunday thru Saturday but as you Saturday and
Sunday don't have data and nothing shows up..

I like the idea of the Count table. Could I out together a query that
will always bring back eight records and those days that have a time will
be inserted first?

Bruce

Bruce, it would be better to find a way to get the 8 records into the
subreport's source query than to try to fudge it with code.

I assume that the subreport is linked to the main report by a date/time
field that has a date value only (no time), and you now want to show the
8
hours of the workday in the subreport.

1. Create a table with one Number field (size: Long Integer), named (say)
CountID. Save the table with the name tblCount. Enter 8 records that
represent the hours of your work day on a 24-hour clock, e.g. 8, 9, 10,
11,
12, 13, 14, ...

2. Create a query that contains both tblCount and your original table. In
the upper pane of query design, drag the CountID field from tblCount and
drop onto the time field in your original table.

3. You should now see a line joining the 2 tables. Doubleclick that line.
Access offers a dialog with 3 choices. Choose the one that says:
All records from tblCount, and any matches from ...
This is called an outer join, and causes all 8 numbers to be included in
the
output.

Now we need to convert the time value into the actual hour, so that the
appointment shows up on the grid in the right hour, and even appointments
that are not on the hour show up. Once we do this, you will not be able
to
view the query graphically and more, so add any other fields you need to
the
query output grid.

4. Switch the query to SQL View (View menu.)

5. In the FROM clause, you will see something like this:
FROM tblCount LEFT JOIN Table1 ON tblCount.CountID =
Table1.AppointTime
Change the last part by inserting Hour() around your table and field
name,
e.g.:
... ON tblCount.CountID = Hour(Table1.AppointTime)

6. Save and close the query.

7. Open your subreport in design view, and change its Record Source to
this
query.

The query will now show all of the hours you entered into tblCount, and
any
appointments at matching hours. (Note that an appointment outside of
those
hours will not show up.)

I have a report with seven subReports, Sunday thru Saturday, that display
the information for those days. Each subReport will fill out with 8
lines
even if there is only enough data for two lines, etc.

Monday
11:00
3:00
____
____
____
____
____
____

My code to make the extra blank lines is:

Private Sub Report_NoData(Cancel As Integer)
Dim Cdb As DAO.Database
Dim rsJobs As DAO.Recordset
'Rest the row counter
bRowCounter = 0
'Get the count of the max rows that have values
Set Cdb = CurrentDb
'If IsNull(TimeID) Then

' Else


Set rsJobs = Cdb.OpenRecordset("Select Count([TimeID])as Jobs from
qryNoData WHERE StaffID = " & Reports.WeeklyTimeSheet.txtStaffID)

bJobCount = Nz(rsJobs!Jobs)
rsJobs.Close
Set rsJobs = Nothing
Set Cdb = Nothing
'Reset all controls to display in black for the first Record
Me!txtDate.ForeColor = COLOR_BLACK
Me!txtFrom.ForeColor = COLOR_BLACK
Me!txtTo.ForeColor = COLOR_BLACK
'Me!ActHoursA.ForeColor = COLOR_BLACK
'Me!TasksA.ForeColor = COLOR_BLACK
End Sub


Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)

bRowCounter = bRowCounter + 1
If bRowCounter < bJobCount Then
Exit Sub
Else
If bRowCounter < MAX_ROWS Then
Me.NextRecord = False
End If
End If
If (bRowCounter > bJobCount) Then
'Note the control names have been aliased with a suffix "A"
Me!txtDate.ForeColor = COLOR_WHITE
Me!txtFrom.ForeColor = COLOR_WHITE
Me!txtTo.ForeColor = COLOR_WHITE
' Me!ActHoursA.ForeColor = COLOR_WHITE
'Me!TasksA.ForeColor = COLOR_WHITE
End If
End Sub

It works great except when there is not any data for that day and then I
get nothing, but I still want 8 blank lines. I've tried:
Making an 8 record table and changing the RecordSource of the subform on
NoData.
Making a NoData subFrom and changing the SubForm on NoData.

None have worked. Any ideas of how I can put these 8 lines on a day
that
has no data?

Bruce Rodtnick