Function to Count the number of times a button is clicked

[Steve]

I put this code into a module to count the number of times a button is
clicked. Access calls this function when the button is clicked. I keep
getting error code 3021 when it runs and it says "Either the BOF or EOF is
true, or the current record has been deleted. Requested operation requires a
current record. The VB editor highlights the part "MyCount = MySet!Count"
when it opens. I copied this code verbatim from the web (except changing the
table/field names). I want this to increase the "count" by one each time the
button is clicked. What am I doing wrong? The table and field names are
correct... I've checked that several times.

Function CheckCount()
Dim MySet As ADODB.Recordset
Dim MyCount As Integer
Set MySet = New ADODB.Recordset
MySet.Open "tblCategoryCount", CurrentProject.Connection, , adLockOptimistic
MyCount = MySet!Count
MySet!Count = MyCount + 1
MySet.Update
End Function

 

[Chris O'C via AccessMonster.com]

Don't use a recordset for this. Use sql.

Public Function CheckCount()
Dim conn As ADODB.Connection

Set conn = CurrentProject.Connection
conn.Execute "UPDATE tblCategoryCount " _
& "SET [Count] = [Count] + 1;"
conn.Close
End Function

You shouldn't use reserved words as field names. Count is a reserved word.

Chris
Microsoft MVP

 

[Steve]

That worked. Thanks.

Don't use a recordset for this. Use sql.

Public Function CheckCount()
Dim conn As ADODB.Connection

Set conn = CurrentProject.Connection
conn.Execute "UPDATE tblCategoryCount " _
& "SET [Count] = [Count] + 1;"
conn.Close
End Function

You shouldn't use reserved words as field names. Count is a reserved word.

Chris
Microsoft MVP

I put this code into a module to count the number of times a button is
clicked. Access calls this function when the button is clicked. I keep
getting error code 3021 when it runs and it says "Either the BOF or EOF is
true, or the current record has been deleted. Requested operation requires a
current record. The VB editor highlights the part "MyCount = MySet!Count"
when it opens. I copied this code verbatim from the web (except changing the
table/field names). I want this to increase the "count" by one each time the
button is clicked. What am I doing wrong? The table and field names are
correct... I've checked that several times.

Function CheckCount()
Dim MySet As ADODB.Recordset
Dim MyCount As Integer
Set MySet = New ADODB.Recordset
MySet.Open "tblCategoryCount", CurrentProject.Connection, , adLockOptimistic
MyCount = MySet!Count
MySet!Count = MyCount + 1
MySet.Update
End Function