Basic question

[nickolm]

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

 

[rapsr59]

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
[COLOR=red]Private CountNo As Integer[/COLOR]

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

 

[nickolm]

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

 

[rapsr59]

Your welcome!

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

Richard

 

[rapsr59]

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