Function to Count the number of times a button is clicked

S

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
 
S

Steve

That worked. Thanks.

Chris O'C via AccessMonster.com said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top