count breakdown

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

Guest

i need to open a report based on a query and when the user opens, i need a
pop up box to tell the user how many records there are (based on the query),
and ask the user would you like to continue with report, if no quit if yes
preview report.
Then i need the report to group alphabetically by the author and to show how
many records for all a's, b's, c's etc.
Any ideal's please
 
blackcat said:
i need to open a report based on a query and when the user opens, i need a
pop up box to tell the user how many records there are (based on the query),
and ask the user would you like to continue with report, if no quit if yes
preview report.
Then i need the report to group alphabetically by the author and to show how
many records for all a's, b's, c's etc.
Click to expand...


You can use something like this untested air code to count
the records in the report as the report is opening and
cancel it if the user says no:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset(Me.RecordSource)
rs.MoveLast
If Msgbox("report has " & rs.RecordCount & " records", _
vbYesNo) = vbNo Then
Cancel = True
End If
rs.Close : Set = Nothing
Set db = Nothing

Your second question is best handled by creating a report
specifically for the summary and then using it as a
subreport in your exisitng report.
 
this works a treat so thanks for that, only problem i have now is that my
query has a criteria selection ie [Enter Licensor Required], and this throws
the event procedure out and gives me an error message. is there any way i can
ask the user what licensor they require in the event procedure and get that
to run the query on the table and return just certain values?
 
You need to get away from those parameter prompt queries and
use a form for the user supplied values.

Create a form with a text box for the license value and a
button to open the report. The button's click event could
use the OpenReport method's WhereCondition argument to
filter the report without the query having any parameters at
all.
--
Marsh
MVP [MS Access]

this works a treat so thanks for that, only problem i have now is that my
query has a criteria selection ie [Enter Licensor Required], and this throws
the event procedure out and gives me an error message. is there any way i can
ask the user what licensor they require in the event procedure and get that
to run the query on the table and return just certain values?

Marshall Barton said:
You can use something like this untested air code to count
the records in the report as the report is opening and
cancel it if the user says no:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset(Me.RecordSource)
rs.MoveLast
If Msgbox("report has " & rs.RecordCount & " records", _
vbYesNo) = vbNo Then
Cancel = True
End If
rs.Close : Set = Nothing
Set db = Nothing

Your second question is best handled by creating a report
specifically for the summary and then using it as a
subreport in your exisitng report.
Click to expand...
Click to expand...
 
Back
Top