Report - user message

[Gordon]

I am designing a report which lists a schedule of visits for a period
of 2 weeks (defined by user input to a dialog box), with the data
presented by week. The code for the report is configured "on the fly"
in the 'on open' event of the report. All of this works fine on its
own. However, I am seeking to make some enhancements to this.

1. If there is no data for either week, I want a message to appear to
say just this. I am using a count of the underlying recordset to
determine this. Can't get the "On No data" report event to work with
the code below.
2. If there is no data for one or other week, I want a message to pop
up saying this, the report to print anyway and for an appropriate
caption to appear in the report header.

The coding I have so far is:
......lots of code here setting up variables etc
......strSQL1 is the string I use for selecting the records for the 2
week period and rst1 is the corresponding recordset
......strSQL2 is the string I use for selecting records for the second
week, rst 2 the recordset and so on for strSQL3 for week 3

Set dbs = currentdb
Set rst1 = dbs.OpenRecordset(strSQL1, dbOpenSnapshot)
If rst1.RecordCount = 0 Then
Call MsgBox("There is no data for this report@@ Please select new or
valid_
criteria", 16, "Message")
Cancel = True
Exit Sub
End If
Set rst2 = dbs.OpenRecordset(strSQL2, dbOpenSnapshot)
Set rst3 = dbs.OpenRecordset(strSQL3, dbOpenSnapshot)
If rst2.RecordCount = 0 or rst3.RecordCount = 0 Then
NoDataLabel.Visible = True 'Report header label
Call MsgBox("There is no data for the second week of this schedule.",
16,_

"Message")
End If

'Initialize SELECT statement.
MyRecordsource = strSQL
Set RecordSource property of Details Report.
Reports!rptBookingSchedule.RecordSource = MyRecordsource
DoCmd.Maximize

End Sub

The reporting of the basic data by week or 2 weeks works fine but not
the recordset criteria. Where am I going wrong?

Thanks

Gordon

 

[GeoffG]

Gordon:

I know of a solution. But it would be more than a tweak to your existing
setup.

You would need a new table of dates (which can be created instantly by VBA),
a couple of new queries, and a report that uses Grouping and Sorting. If you
want to consider this approach, I'd be happy to walk you through it and post
the code you need. The code that runs behind the report would then be very
simple.

Geoff

 

[Gordon]

Geoff,

Thanks for picking this up. I'd almost given up! I'd be delighted to
learn of anyway I can achieve the end obective I descibed in my
original post.

For info, I am already using a table of dates (week commencing/week
no). I use that behind a form which allows the user to select the
report parameters (financial year/week commencing). Selection of the
week commencing allows me to then define the 2 week period that I need
as the period for the report. But what did you have in mind?

Gordon

 

[GeoffG]

Gordon:

It's just gone midnight here in London.
I'll get back to you tomorrow.

Regards
Geoff

 

[GeoffG]

Gordon:

Below is an Explanation and a Step-by-Step procedure.

I've had to make some guesses and assumptions. These will be evident to you.
You will need to make your own adaptations.

Sorry it's longer than I thought it would be. I wasn't sure how much detail
you would need. And it takes longer to explain something than to do it!


EXPLANATION:
-------------------

The RecordSource of your report needs to be a query that is formed from two
source queries.

The first source query will list data from your table of Week-Commencing
dates. I'll assume that this table contains a date field called
"WeekCommencing" and that there is one record in the table for each date
that begins each week.

The second source query will be a copy of the query you're currently using
as the RecordSource for the report. This second query will have a new
calculated field called "WeekStarting" (to differentiate it from the
"WeekCommencing" field). The WeekStarting field will be calculated by the
query calling a function (the code for which is given below). The function
will calculate the date of the first day in the week of a visit - for
example, if a visit occurs on 26 April 2007 and, if Monday is the first day
of the week, then the "WeekStarting" field will show 23 April 2007. (If your
week starts on a different weekday, then you can easily amend the function
to calculate the date of the weekday you want.)

The query acting as the RecordSource for the report will join the
"WeekCommencing" and "WeekStarting" fields. The join will be edited to
include all records from the "WeekCommencing" table and only those records
that match in the "WeekStarting" table.

The RecordSource query will get its start-date and end-date criteria for the
WeekCommencing field from the criteria form, which will be displayed to the
user by the Report's Open event.


STEP-BY-STEP PROCEDURE
----------------------------------


SOURCE QUERY 1:

1. Create source query 1 so that it lists all records in the
Week-Commencing table in date order.


SOURCE QUERY 2:

2. Create source query 2. This will probably be a copy of your current
query that is acting as the RecordSource for the report. Open the query in
design view and ensure it includes all fields required for the report.

3. Include a field that will always contain data (eg the primary key
field). This field will be used to count the number of visits in each week.

4. To create the calculated "WeekStarting" field, enter the following
expression in the Field row of the next blank column in the design grid:

WeekStarting: StartOfWeek([VisitDate])

Change [VisitDate] to the name of your Visit-Date field.

5. Sort the WeekStarting field in ascending order.

6. Drag your VisitDate field to the Field row of the next blank column to
the right of the WeekStarting column. If the VisitDate field is already in
the grid, uncheck the "Show" box in the new column. Sort the new column in
ascending order.

7. Save and close the query.


STARTOFWEEK FUNCTION

8. Create a new standard module.

9. Paste in the following function:


Public Function StartOfWeek(vntVisit As Variant) As Date

' IN:
'
' vntVisit is either a visit date or Null
' (when there are no visits in a given week).

' RETURNS:
'
' If the incoming vntVisit is a date, this function
' returns the date of the first day in the week of
' the visit; otherwise, returns zero.

' CALLED BY:
'
' Query for Schedule report.


' Let MONDAY be first day of week:
Const FIRSTDAYOFWEEK As Integer = vbMonday

Dim fIsDate As Boolean
Dim intWeekday As Integer
Dim intDiff As Integer
Dim datWeekStart As Date
Dim RetVal As Date


' See if a date has been passed in:
fIsDate = IsDate(vntVisit)
If Not fIsDate Then
RetVal = 0
GoTo Bye
End If

' Get Weekday of visit:
intWeekday = Weekday(vntVisit, FIRSTDAYOFWEEK)

' Get difference between Weekday of visit
' and first Weekday of week:
intDiff = intWeekday - 1

' Subtract difference from visit date:
RetVal = DateAdd("d", -intDiff, vntVisit)

Bye:

StartOfWeek = RetVal
Exit Function

End Function


10. Close the module.


FORM FOR REPORT CRITERIA

11. You said you had created a form on which the user can select the
start date for the report.

12. I created an unbound form as follows:

Form Properties:
----------------
PopUp = Yes
Modal = Yes
Scroll Bars = Neither
Record Selector = No
Navigation Buttons = No
Dividing Lines = No
Auto Resize = Yes
Auto Center = Yes
Border Style = Dialog
Control Box = Yes
Max Min Buttons = None
Close Button = Yes


13. ComboBox in which the user can select a start date:

ComboBox Properties:
-----------------------
Name = "cboStart"
RowSource = Source Query 1 (mentioned above)
AfterUpdate Event = [Event Procedure]


14. TextBox for the end date. The end date is calculated by the
ComboBox's AfterUpdate event.

TextBox Properties:
--------------------
Name = txtEnd
Locked = Yes


15. OK button for the user to click after selecting a two-week period.

OK Button Properties:
----------------------
Name = cmdOK
Enabled = No
Default = Yes


16. Cancel button for the user to abort opening the report.

Cancel Button Properties:
-------------------------
Name = cmdCancel
Cancel = Yes


17. The code behind the criteria form:


Option Compare Database
Option Explicit

Private Sub cboStart_AfterUpdate()

Dim datEnd As Date

' Add 13 days to the start date:
datEnd = DateAdd("d", 13, Me.cboStart)

' Put end date in Textbox:
Me.txtEnd = datEnd

' Enable OK button:
Me.cmdOK.Enabled = True

End Sub

Private Sub cmdCancel_Click()

DoCmd.Close

End Sub

Private Sub cmdOK_Click()

' Hide form to allow report to get
' at data on form:
Me.Visible = False

End Sub


RECORD SOURCE QUERY FOR REPORT

18. Create a new query in Design View.

19. In the Show Table dialog, click the Query tab. Add Source Query 1 and
Source Query 2.

20. Drag the WeekCommencing field to the WeekStarting field to join them.

21. Point to the join line, right-click the line, and select Join
Properties.

22. Click the Option Button for "Include all records from [Source Query
1, the WeekCommencing query] and only those records from [Source Query 2,
the WeekStarting query] where the joined fields are equal".

23. Click OK to close the Join Properties dialog. An arrow should appear
pointing to Source Query 2.

24. Drag the WeekCommencing field (from Source Query 1) to the first
column of the grid. Set its Sort to ascending. (This is for convenience
when testing.)

25. Set the criteria for this field to:

Between [Forms]![frmScheduleCriteria]![cboStart] And
[Forms]![frmScheduleCriteria].[txtEnd]

Amend the above line to reflect the name of your criteria form.

26. Drag the asterisk from Query Source 2 down into the grid to get all
fields.

27. Additionally, drag the VisitDate field from Query Source 2 to the
field row of the last column of the grid. Uncheck its Show property. Set its
Sort row to Ascending.

28. Save the query.


REPORT

29. Create a new report. Select the above RecordSource query as the data
source.

30. In Report Design View, click the Sorting and Grouping button on the
Report-Design toolbar. The Sorting and Grouping dialog opens.

31. In the dialog, click in the first row of the Field/Expression column.
Click the down-arrow and select the WeekCommencing field. In the Group
Properties section in the bottom half of the dialog, click in the Group
Header property and select Yes. Similarly, click in the Group Footer
property and select Yes. Click in the Keep Together property and select
"Whole Group" or "With First Detail".

32. Click in the second row of the Field/Expression column. Click the
down-arrow and select your VisitDate field. You don't need a Group Header
or Footer for this field.

33. The dialog should default to Ascending order for the WeekCommencing
and the VisitDate fields.

34. Close the Sorting and Grouping dialog.

35. Click the Field List button on the Report Design toolbar to open the
Field List.

36. Drag the WeekCommencing field from the Field List to the
WeekCommencing GroupHeader Section of the report. You'll want to select a
suitable font, eg Arial 14 point bold.

37. In the WeekCommencing Group Header, create an unbound Textbox.

TextBox Properties:
--------------------
Name = txtGroupCount
Visible = No
Control Source = =Count([ID])
where [ID] is the name of the field that always contains data, eg the
Primary Key field.
Notice the leading equals sign before Count.


38. In the WeekCommencing Group Header, create a label.

Label Properties:
-----------------
Name = lblNoRecords
Caption = No Records!
Font size/weight as appropriate.


39. Drag the other fields you need to the Detail Section of the report.

40. Drag the bottom of the WeekCommencing Group Footer up or down to
create space between each week of the Schedule.


41. Copy and paste the following code into the Report's class module:


Option Compare Database
Option Explicit

' Name of the criteria form:
Private Const ScheduleCriteriaForm As String = "frmScheduleCriteria"


Private Sub Report_Open(Cancel As Integer)

Dim objFRM As Access.Form

' Open criteria form in dialog mode to suspend
' code execution (and report opening):
DoCmd.OpenForm ScheduleCriteriaForm, acNormal, , , , acDialog

' See if criteria form is still open:
On Error Resume Next
Set objFRM = Forms(ScheduleCriteriaForm)

' Stop report from opening if criteria for is closed:
If Err.Number <> 0 Then
Cancel = True
End If

End Sub

Private Sub Report_Close()

' We're done with criteria form:
DoCmd.Close acForm, ScheduleCriteriaForm, acSaveNo

End Sub

Private Sub Report_NoData(Cancel As Integer)

' Show no data message:
MsgBox "No data for schedule. Report won't open.", _
vbOKOnly + vbInformation, "Information"

' Stop report from opening:
Cancel = True

End Sub

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)

Dim fShow As Boolean

' Evaluate count of records in current group:
fShow = (Me.txtGroupCount = 0)

' Show/hide the "No Records" label:
Me.lblNoRecords.Visible = fShow

' Show message to user:
If fShow Then
MsgBox Me.txtWeekCommencing _
& vbNewLine & vbNewLine _
& "No records for above week!", _
vbOKOnly + vbExclamation, "Information"
End If

End Sub


42. Check the property sheet for the report. The following event
properties should say [Event Procedure]:

On Open
On Close
On No Data


That's it! I hope I've not left anything out. Please post back if you need
further explanation.

Geoff

 

[Gordon]

Hey Geoff,

What a star you are! That was some walk through. I've just finished
building all the code but it fell at the first hurdle. When I select
a date (even if it's a date for which there are or are not records), I
get a run time error (code 438) "Object doesn't support this properety
or method". When I hit the debug button, it points to the line "fShow
= (Me!txtGroupCount = 0)" from your code. Hovering the cursor over
the "fshow" indicates that "fshow = false" if that helps.

Whilst I can see the logic of your code here, I can't figure out what
the problem is.

Regards

Gordon

 

[GeoffG]

Gordon,

I built the database I posted and it works OK. So, I'm surprised and
perplexed that your version is experiencing a problem.

The Boolean variable fShow would be initialised by VBA to False. It would
only be evaluated and given a meaningful value of True or False if the line
of code that reports the error had executed. This is just a way of saying
that, unfortunately, hovering the mouse on the left side of the equation
when code execution has stopped on that line doesn't reveal anything at this
stage.

Are you quite sure:

1. that you created a Textbox in the GroupHeader;
2. that it's Name property was set to "txtGroupCount";
3. that it's ControlSource property was set to:

=Count([ID])

4. that you changed "[ID]" to the name of a field (eg the primary key
field) that always contains data; and
5. that the field was in the query?

Before posting, I did wonder:

(a) whether it was important to have the "[ID]" field on the report as
well as in the query; and

(b) whether, if you didn't want to show the "[ID]" field on the report,
whether you needed to put it on the report but make it invisible. My tests
seemed to show that, so long as the field is in the underlying recordset, it
doesn't matter whether it is visible or invisible in the Detail Section or
whether it's on the report at all.

But this is a side issue that isn't relevant to the error message you're
receiving. The error message "Object doesn't support this properety or
method" seems to suggest a problem with the Textbox - or perhaps more
accurately, the object you created. The above checks should help identify
the problem.

Post back the answers and let's see where that leads.

Regards
Geoff

 

[GeoffG]

Gordon,

I ran some more tests and managed to get the same error as you - Error 438
Object doesn't support this property or method.

I changed the Textbox to a LABEL and put:

=Count([ID])

in the CAPTION property.

Do you think you might have done that?

If so, open the report in design view, right-click the label to open the
shortcut menu, select CHANGE TO and select TEXTBOX. Then put the above Count
expression into the CONTROL SOURCE property.

Hope that solves it.

Regards
Geoff

PS - Sorry about my pathetic spelling. You'd think I'd know how to spell
"its" by now. Just shows what the fingers will do when the brain is
concentrating on something else! Perhaps you had a similar lapse?

 

[Gordon]

Hi Geoff,

You will not believe it but I was just about to respond to your post
of yesterday having worked through the guide once again and actually
found the error when I discovered your latest post. You are
absolutely correct - I made the txtGroupCount a 'label' and not a
unbound 'text' box. Duh!

Anyway, after that all the dominoes fell in sequence and everything is
working just the way I wanted it. Thank you very much for going that
extra mile - it's people like you that give this newsgroup a good
name !

Ciao

Gordon

 

[GeoffG]

Gordon:

I guessed it was something simple and that you'd figure it out.

After posting the solution, I remembered one or two more tweaks that I
hadn't mentioned. For example, if a ComboBox is used to select the start
date, then you'd want to set a few more properties of the ComboBox, eg:

Limit To List = Yes
List Rows = 25 (or something larger depending on screen appearance).

Also, I wondered if it were strictly necessary to add 13 days to capture two
weeks' worth of the schedule, given that the start and end criteria apply to
the WeekCommencing field, not the WeekStarting field. Perhaps 7 days would
have been enough to embrace the beginning week and the next week's starting
date. But I didn't test this. It is now only of academic interest.

I take it you've developed some code to create the WeekCommencing dates
automatically. If you're interested, there's a good example of how to
develop solutions that need a calendar table (that contains public holiday
dates, the start/end dates of fiscal years, which days are working days,
etc) in the book:

"Fixing Access Annoyances"
by Phil Mitchell and Evan Callaghan
(see page 180)

Also, it's generally accepted that the best book around is:

"Access Developers Handbook"
by Getz, Litwin & Gunderloy/Gilbert
(see www.developershandbook.com)


Thanks for the thanks. I'm glad you are pleased with the result.

Regards
Geoff
Stay in touch:
geoffcg at talktalk.net