Autocounter

[an]

Hi!

I would like to insert a counter, for exemple in F_Main, to count how many
times the DB was loaded.
Is it possible in Acc2k7, please?

Thanks in advance.
an

 

[KARL DEWEY]

One way is to build a table with number field and an update query that adds
one to the number field. Create a macro named Autoexec with action to open
the query.
Every time someone opens the database the macro will run and increment the
field.

 

[strive4peace]

Hi an,

you need to store the information somewhere -- for instance, in a table
or a database property. I will give you the solution for counting how
many times your main form is opened and store the result in a table.

make a table:

*Defaults*
NumMainOpen, number, long

put one record in the table with NumMainOpen = 0

then, on the Open event of f_main:

'~~~~~~~~~
dim strSQL as string
strSQL = "UPDATE Defaults " _
& " SET NumMainOpen = NumMainOpen + 1;"

currentdb.execute strSQL
'~~~~~~~~~

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[Arvin Meyer [MVP]]

Understand that each user must have their own copy of the front-end of the
database. Sharing a single fron-end is a leading cause of corruption.

Build a 1 record table named tblDBOpen, with a single field named CountDB.
Add the following code to a standard or form module and call it from the
form's open or load event:

Function CountOpen() As Long
On Error GoTo Error_Handler

Dim rst As DAO.Recordset
Dim lngMax As Long
Dim db As DAO.Database

Set db = CurrentDb

Set rst = db.OpenRecordset("Select Max(CountDB) As MaxNum FROM
tblDBOpen")
If IsNull(rst!MaxNum) Then
'no records yet, start with one
lngMax = 1
Else
lngMax = rst!MaxNum + 1
End If

CountOpen = lngMax

Exit_Here:
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Function

Error_Handler:
MsgBox Err.Number & ": " & Err.Description
Resume Exit_Here

End Function

 

[an]

Thanks for your reply.

When debug run: Ok.
When form open return next error: CurrentDb.Execute strSQL

Thanks
an

 

[strive4peace]

did you make the table?


** debug.print ***

debug.print strSQL

--> this prints a copy of the SQL statement to the debug window (CTRL-G)

After you execute your code, open the Debug window
CTRL-G to Goto the debuG window -- look at the SQL statement

If the SQL statement has an error

1. Make a new query (design view)

2. choose View, SQL from the menu
(or SQL from the toolbar, first icon)

3. cut the SQL statement from the debug window
(select, CTRL-X)

4. paste into the SQL window of the Query
(CTRL-V)

5. run ! from the SQL window
-- Access will tell you where the problem is in the SQL


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[strive4peace]

Hi Arvie <smile> ... hope you don't mind my affectionate name for you

Warm Regards,
Crystal

*
have an awesome day
*

 

[an]

Thank you for replay.

One difficult: After insert your the function code in form, how call it
from's open or load event, please?

an

 

[an]

Yes, it is...
Thanks

did you make the table?


** debug.print ***

debug.print strSQL

--> this prints a copy of the SQL statement to the debug window (CTRL-G)

After you execute your code, open the Debug window
CTRL-G to Goto the debuG window -- look at the SQL statement

If the SQL statement has an error

1. Make a new query (design view)

2. choose View, SQL from the menu
(or SQL from the toolbar, first icon)

3. cut the SQL statement from the debug window
(select, CTRL-X)

4. paste into the SQL window of the Query
(CTRL-V)

5. run ! from the SQL window
-- Access will tell you where the problem is in the SQL


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[an]

Yes, it is.
I go to try!!!
an

did you make the table?


** debug.print ***

debug.print strSQL

--> this prints a copy of the SQL statement to the debug window (CTRL-G)

After you execute your code, open the Debug window
CTRL-G to Goto the debuG window -- look at the SQL statement

If the SQL statement has an error

1. Make a new query (design view)

2. choose View, SQL from the menu
(or SQL from the toolbar, first icon)

3. cut the SQL statement from the debug window
(select, CTRL-X)

4. paste into the SQL window of the Query
(CTRL-V)

5. run ! from the SQL window
-- Access will tell you where the problem is in the SQL


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[strive4peace]

did you make a record in the table and set the value of NumMainOpen to
zero?

add the
debug.print strSQL

statement to your code before you execute the SQL

then, if it does not work, follow the instructions to cp[y the SQL from
the debug window and test it ... did you find the problem?

~~~
btw, this table will only have one record <smile>


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[Arvin Meyer [MVP]]

In the form's Open event property sheet, you can simply put:

=CountOpen()
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[an]

AM, sorry for my delay.

Now, after write =CountOpen() in Open event, return:
Compile error: Expected: line nunber or label or statement or end of statment.

an

 

[an]

Ok, strive4peace, sorry my delay.

The Table name was incorrect :-(
Now is perfect. Work very fine!

Thank you very much

an

did you make a record in the table and set the value of NumMainOpen to
zero?

add the
debug.print strSQL

statement to your code before you execute the SQL

then, if it does not work, follow the instructions to cp[y the SQL from
the debug window and test it ... did you find the problem?

~~~
btw, this table will only have one record <smile>


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Yes, it is.
I go to try!!!
an

 

[strive4peace]

you're welcome, an happy to help

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Ok, strive4peace, sorry my delay.

The Table name was incorrect :-(
Now is perfect. Work very fine!

Thank you very much

an

did you make a record in the table and set the value of NumMainOpen to
zero?

add the
debug.print strSQL

statement to your code before you execute the SQL

then, if it does not work, follow the instructions to cp[y the SQL from
the debug window and test it ... did you find the problem?

~~~
btw, this table will only have one record <smile>


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Yes, it is.
I go to try!!!
an

:

did you make the table?


** debug.print ***

debug.print strSQL

--> this prints a copy of the SQL statement to the debug window (CTRL-G)

After you execute your code, open the Debug window
CTRL-G to Goto the debuG window -- look at the SQL statement

If the SQL statement has an error

1. Make a new query (design view)

2. choose View, SQL from the menu
(or SQL from the toolbar, first icon)

3. cut the SQL statement from the debug window
(select, CTRL-X)

4. paste into the SQL window of the Query
(CTRL-V)

5. run ! from the SQL window
-- Access will tell you where the problem is in the SQL


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




an wrote:
Thanks for your reply.

When debug run: Ok.
When form open return next error: CurrentDb.Execute strSQL

Thanks
an

:

Hi an,

you need to store the information somewhere -- for instance, in a table
or a database property. I will give you the solution for counting how
many times your main form is opened and store the result in a table.

make a table:

*Defaults*
NumMainOpen, number, long

put one record in the table with NumMainOpen = 0

then, on the Open event of f_main:

'~~~~~~~~~
dim strSQL as string
strSQL = "UPDATE Defaults " _
& " SET NumMainOpen = NumMainOpen + 1;"

currentdb.execute strSQL
'~~~~~~~~~

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




an wrote:
Hi!

I would like to insert a counter, for exemple in F_Main, to count how many
times the DB was loaded.
Is it possible in Acc2k7, please?

Thanks in advance.
an

 

[Arvin Meyer [MVP]]

That example was drawn from working code that's been running for years, so
I'd need to see your exact implementation to debug the error. Try setting a
breakpoint at the Set db line and stepping through the code to find the line
producing the error.

If Crystal's code is working for you, I'd just use that to avoid wasting any
time.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[an]

AM, thank you for help.

When run code, return message:

Compile error: Syntax error

on line

= CountOpen()

an

 

[Arvin Meyer [MVP]]

= CountOpen()

goes directly in the property sheet in the On Open or On Load event, not in
a code window.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[an]

AM, thank you for reply.

Exactly, in OnLoad or in OnOpen:

Private Sub Form_Open(Cancel As Integer)
= CountOpen()
End Sub

Return too:
Compile Error: Sintax Error

an

 

[an]

AM,
Problem solved with:

Private Sub Form_Load()
Me.CountDB = CountOpen()
End Sub

In OnLoad, only.
In OnOpen return: Run-time error '2448'

Thank you more one time.
an