Hello,

Trying to get the VBA bit to count to 6 (1 every time a button on the form is clicked). Got as far as

Dim countNO As Integer

When a name is selected it needs to zero the number to start again. They then are presented with words to read. Answer is either yes or no (there is a button cmdNo_Click and cmdYes_Click)

When yes is clicked it updates a field in a table with True. When no just moves to next record. I want to count these no's and when they get to 6 then display a msg box. also i need the no words to be updated into a table linked to the name of the person being tested. Can anybody please help me to get started. I am a teacher of five year olds and it is to test keywords they should know. Thanks

Matthew

In a procedure you can use a Static variable. A Static variable retains its value as long as the code is running.
Call this procedure every time your student clicks no.
Place the following code in your cmdNo_Click event



Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Static CountNo


CountNo = CountNo+ 1
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(tblName)
With rst
.AddNew
![StudentID] = Me.ctlStudentID
![MissedKeyword] = Me.ctlKeyword
.Update
End With


If CountNo = 6 then
Msgbox "Your Message Goes Here!"
CountNo = 0
End if

set rst = Nothing
set dbs = Nothing



In the cmdYes_Click Event use the following code



Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(tblName)
With rst
.Edit
![Field] = True
.Update
End With

Set rst = Nothing
Set dbs = Nothing





Note: If your Static variable needs to be controlled by both the cmdYes and cmdNo events (or any other event) you can place the Static variable in the (Declarations) section instead of the cmdNo event.


Option Compare Database
Option Explicit
Private CountNo As Integer


Hope this is what you need!

-----------

Quote: "When a name is selected it needs to zero the number to start again. They then are presented with words to read. Answer is either yes or no (there is a button cmdNo_Click and cmdYes_Click)"

It appears that declaring the Static variable in the Declarations section would be more appropriate.

Use the following code when a new name (Student) is entered.



Private Sub ctlStudentName_AfterUpdate()
CountNo = 0
End Sub


---------------
Richard

Thanks for that Richard. It has worked a treat! I appreciate you taking the time to answer a basic question.

I seem to have lots of good ideas but not the skills to back them up, so once again, thanks

Matthew

Your welcome!

Don't Give Up The Computer! We Have Just Begun To Program!

Richard

Matthew

Sorry, I need to make a correction! Yikes!

You CANNOT place a Static Variable in the (Declarations) section of your code. However you can use...


Option Compare Database
Option Explicit
Public CountNo As Integer


... and then manipulate the value of CountNo in all procedures used on your form.

I apologize for not checking this part of the code. You will get an error if you try placing a Static Variable in the (Declarations) section. A Static Variable can only be used in sub and functions.

Richard