If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Access
I would like to consecutively number an Access report each time it runs so
that I know how many times this report has run. Can anyone help? |
#2
|
|||
|
|||
Access
You'll need to store the number of times each report is opened in a table,
ReportLog say, with columns such as ReportName (text) and RunNumber (number). Initially enter a row for each relevant report with a zero RunNumber value. Each time the report is opened you can increment the number in the table. This could be done in the report's Open event procedure, but that would mean the code would execute every time the report is opened, so if you were amending its design for instance and switching between design view and print preview view the code would execute each time and the number would be incremented. Probably better would be to execute the code only when the report is 'legitimately' opened by a user, so you'll have to control how this is done of course, but the code would be along these lines: Const conREPORTNAME = "YourReportNameGoesHere" Dim cmd As ADODB.Command Dim strSQL As String Set cmd = New ADODB.Command cmd.ActiveConnection = CurrentProject.Connection cmd.CommandType = adCmdText strSQL = "UPDATE ReportLog " & _ "SET RunNumber = RunNumber+1 " & _ "WHERE ReportName = """ & conREPORTNAME & """" cmd.CommandText = strSQL cmd.Execute DoCmd.OpenReport conREPORTNAME To show the RunNumber value in the report add the ReportLog table to the report's underlying query but do not join it to any other tables. Include the ReportName and RunNumber columns in the query and enter the name of the report in the 'criteria' row of the ReportName column and uncheck the 'show' check box for the same column in query design view. You can then include a text box anywhere in the report with RunNumber as its ControlSource property. Ken Sheridan Stafford, England bcraigmiles wrote: I would like to consecutively number an Access report each time it runs so that I know how many times this report has run. Can anyone help? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/201005/1 |
#3
|
|||
|
|||
Access
I tried this and got the following error:
Compile Error: Invalid outside procedure. There is something about the "Set" statement that didn't work. Got any other ideas? Thanks. KenSheridan wrote: You'll need to store the number of times each report is opened in a table, ReportLog say, with columns such as ReportName (text) and RunNumber (number). Initially enter a row for each relevant report with a zero RunNumber value. Each time the report is opened you can increment the number in the table. This could be done in the report's Open event procedure, but that would mean the code would execute every time the report is opened, so if you were amending its design for instance and switching between design view and print preview view the code would execute each time and the number would be incremented. Probably better would be to execute the code only when the report is 'legitimately' opened by a user, so you'll have to control how this is done of course, but the code would be along these lines: Const conREPORTNAME = "YourReportNameGoesHere" Dim cmd As ADODB.Command Dim strSQL As String Set cmd = New ADODB.Command cmd.ActiveConnection = CurrentProject.Connection cmd.CommandType = adCmdText strSQL = "UPDATE ReportLog " & _ "SET RunNumber = RunNumber+1 " & _ "WHERE ReportName = """ & conREPORTNAME & """" cmd.CommandText = strSQL cmd.Execute DoCmd.OpenReport conREPORTNAME To show the RunNumber value in the report add the ReportLog table to the report's underlying query but do not join it to any other tables. Include the ReportName and RunNumber columns in the query and enter the name of the report in the 'criteria' row of the ReportName column and uncheck the 'show' check box for the same column in query design view. You can then include a text box anywhere in the report with RunNumber as its ControlSource property. Ken Sheridan Stafford, England I would like to consecutively number an Access report each time it runs so that I know how many times this report has run. Can anyone help? |
#4
|
|||
|
|||
Access
Where have you entered the code? The error you are getting normally means
that code has been entered in a module, be it a standard module or a form or report's class module, outside the procedure declaration line and the End Sub line which marks the end of the procedure. The code must be between these lines. The error occurs on the 'Set cmd = New ADODB.Command' line because the lines prior to that are merely constant or variable declarations, which are permissible outside a procedure, so this line is the first to raise an error. If you are opening the report from a button on a form for instance, select the button in form design view. In its properties sheet select the On Click even property and then click the 'build' button (the one on the right with 3 dots). Select 'Code Builder' in the next dialogue and OK out of the dialogue. The VBA window should open at the control's Click event procedure with the first and last lines in place. Paste the code between these two lines. Ken Sheridan Stafford, England bcraigmiles wrote: I tried this and got the following error: Compile Error: Invalid outside procedure. There is something about the "Set" statement that didn't work. Got any other ideas? Thanks. You'll need to store the number of times each report is opened in a table, ReportLog say, with columns such as ReportName (text) and RunNumber (number). [quoted text clipped - 39 lines] I would like to consecutively number an Access report each time it runs so that I know how many times this report has run. Can anyone help? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/201005/1 |
#5
|
|||
|
|||
Access
Thanks.
KenSheridan wrote: Where have you entered the code? The error you are getting normally means that code has been entered in a module, be it a standard module or a form or report's class module, outside the procedure declaration line and the End Sub line which marks the end of the procedure. The code must be between these lines. The error occurs on the 'Set cmd = New ADODB.Command' line because the lines prior to that are merely constant or variable declarations, which are permissible outside a procedure, so this line is the first to raise an error. If you are opening the report from a button on a form for instance, select the button in form design view. In its properties sheet select the On Click even property and then click the 'build' button (the one on the right with 3 dots). Select 'Code Builder' in the next dialogue and OK out of the dialogue. The VBA window should open at the control's Click event procedure with the first and last lines in place. Paste the code between these two lines. Ken Sheridan Stafford, England I tried this and got the following error: Compile Error: Invalid outside procedure. There is something about the [quoted text clipped - 5 lines] I would like to consecutively number an Access report each time it runs so that I know how many times this report has run. Can anyone help? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/201005/1 |
#6
|
|||
|
|||
Access
As Ken points out, it will be up to you to determine what constitutes
"running a report". For instance, if someone tries to print a report but the printer fails, does that count? Or if someone opened the report in Print Preview mode, jotted down the figures and closed it, does that count? How would you know?! Good luck! Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "bcraigmiles" u59763@uwe wrote in message news:a774515bf3c91@uwe... I would like to consecutively number an Access report each time it runs so that I know how many times this report has run. Can anyone help? |
#7
|
|||
|
|||
Access
It sounds like you might be a manager.
You have probably asked the wrong question. There may be better ways to achieve what you want. Why do you want to know this? What use will be made of this information? -- Dorian "Give someone a fish and they eat for a day; teach someone to fish and they eat for a lifetime". "unknown" wrote: |
#8
|
|||
|
|||
Access
No one will be opening the report but me, but each time I open it, something
is altered because end-users are constantly putting in data. I need to number the report each time I print it. Jeff Boyce wrote: As Ken points out, it will be up to you to determine what constitutes "running a report". For instance, if someone tries to print a report but the printer fails, does that count? Or if someone opened the report in Print Preview mode, jotted down the figures and closed it, does that count? How would you know?! Good luck! Regards Jeff Boyce Microsoft Access MVP I would like to consecutively number an Access report each time it runs so that I know how many times this report has run. Can anyone help? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/201005/1 |
#9
|
|||
|
|||
Access
bcraigmiles wrote:
No one will be opening the report but me, but each time I open it, something is altered because end-users are constantly putting in data. I need to number the report each time I print it. As Ken points out, it will be up to you to determine what constitutes "running a report". For instance, if someone tries to print a report but [quoted text clipped - 12 lines] I would like to consecutively number an Access report each time it runs so that I know how many times this report has run. Can anyone help? FWIW, here's how I got it to work... On my report, I have a textbox with the following ControlSource: =DMax("RunNumber","ReportRuns","[ReportName]='" & [Name] & "'") I also have a table, ReportRuns(ReportName (text), RunDateStamp (date/Time, general date), RunNumber (int)) PK is ReportName, RunDateStamp Code... Option Compare Database Option Explicit Private Sub Report_Open(Cancel As Integer) Dim strInsertSQL As String Dim intRunNumber As Integer 'grab the next run number from the table. intRunNumber = Nz(DMax("RunNumber", "ReportRuns", "[ReportName]='" & Me. Name & "'"), 0) + 1 ' create the insert statement strInsertSQL = "INSERT INTO ReportRuns(ReportName, RunNumber) VALUES ('" & Me.Name & "' ," & intRunNumber & ")" DBEngine(0)(0).Execute strInsertSQL End Sub -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/201005/1 |
#10
|
|||
|
|||
Access
Thanks. This does work.
PieterLinden wrote: No one will be opening the report but me, but each time I open it, something is altered because end-users are constantly putting in data. I need to [quoted text clipped - 5 lines] I would like to consecutively number an Access report each time it runs so that I know how many times this report has run. Can anyone help? FWIW, here's how I got it to work... On my report, I have a textbox with the following ControlSource: =DMax("RunNumber","ReportRuns","[ReportName]='" & [Name] & "'") I also have a table, ReportRuns(ReportName (text), RunDateStamp (date/Time, general date), RunNumber (int)) PK is ReportName, RunDateStamp Code... Option Compare Database Option Explicit Private Sub Report_Open(Cancel As Integer) Dim strInsertSQL As String Dim intRunNumber As Integer 'grab the next run number from the table. intRunNumber = Nz(DMax("RunNumber", "ReportRuns", "[ReportName]='" & Me. Name & "'"), 0) + 1 ' create the insert statement strInsertSQL = "INSERT INTO ReportRuns(ReportName, RunNumber) VALUES ('" & Me.Name & "' ," & intRunNumber & ")" DBEngine(0)(0).Execute strInsertSQL End Sub -- Message posted via http://www.accessmonster.com |
Thread Tools | |
Display Modes | |
|
|