A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Access



 
 
Thread Tools Display Modes
  #1  
Old May 3rd, 2010, 03:13 PM posted to microsoft.public.access.gettingstarted
bcraigmiles
external usenet poster
 
Posts: 2
Default 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  
Old May 3rd, 2010, 04:07 PM posted to microsoft.public.access.gettingstarted
KenSheridan via AccessMonster.com
external usenet poster
 
Posts: 1,610
Default 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  
Old May 3rd, 2010, 04:49 PM posted to microsoft.public.access.gettingstarted
bcraigmiles
external usenet poster
 
Posts: 2
Default 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  
Old May 3rd, 2010, 05:04 PM posted to microsoft.public.access.gettingstarted
KenSheridan via AccessMonster.com
external usenet poster
 
Posts: 1,610
Default 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  
Old May 4th, 2010, 03:35 PM posted to microsoft.public.access.gettingstarted
bcraigmiles via AccessMonster.com
external usenet poster
 
Posts: 3
Default 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  
Old May 4th, 2010, 05:36 PM posted to microsoft.public.access.gettingstarted
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default 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  
Old May 5th, 2010, 05:10 PM posted to microsoft.public.access.gettingstarted
Dorian
external usenet poster
 
Posts: 542
Default 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  
Old May 7th, 2010, 08:48 PM posted to microsoft.public.access.gettingstarted
bcraigmiles via AccessMonster.com
external usenet poster
 
Posts: 3
Default 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  
Old May 7th, 2010, 09:43 PM posted to microsoft.public.access.gettingstarted
PieterLinden via AccessMonster.com
external usenet poster
 
Posts: 307
Default 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  
Old May 11th, 2010, 04:14 PM posted to microsoft.public.access.gettingstarted
bcraigmiles via AccessMonster.com
external usenet poster
 
Posts: 3
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:50 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.