Access 2007 increment integer in vba

Microsoft Access 2007
July 14, 2010 at 02:30:41
Specs: Windows XP
I have a database that I store info in that makes use of a login form. What I am attempting to do is make it that if the wrong details are entered in incorrectly after three attempts that the application will close

my current code is as follows:

Private Sub SignOn_Click()

Dim msg
Dim User_key As Integer
Dim db As Database
Dim rst As Recordset
Dim BoxDate As String
Dim BoxTime As String
Dim BoxText As String
Dim Counter As Integer

BoxDate = Date
BoxTime = Time
Date = FormatDateTime(Date, vbShortDate)
BoxText = BoxDate & " " & BoxTime
Set db = CurrentDb()
Set rst = db.OpenRecordset("Users")
rst.Index = "Logon-ID"
rst.Seek "=", Logon_id
If rst.NoMatch Then
GoSub LogCounter
Else
If rst![Password] = Password Then
User_key = rst![Key_Number]
MsgBox BoxText & vbNewLine & vbNewLine & " " & "Welcome!" & vbNewLine & " " & rst![Firstname] & " " & rst![Surname], vbOKOnly, "Welcome"
DoCmd.OpenForm "Main Menu", acNormal, , , acFormPropertySettings, acWindowNormal, User_key
DoCmd.Close acForm, "Logon", acSaveNo
End If
End If

LogCounter:
If Counter < 3 Then
Counter = Val(Counter) + 1
MsgBox "Invalid Login Details!", vbCritical

ElseIf Counter = 3 Then
MsgBox "Your Login Details Have Been Incorrect 3 Times!" & vbNewLine & "Application Will Now Quit!", vbCritical
Application.Quit
End If

At present it is displaying the error message but does not appear to be incrementing as it does not display the "counter = 3" error message and does not quit, it just loops around the invalid details message

any help will be greatly appreciated


See More: Access 2007 increment integer in vba

Report •

#1
July 14, 2010 at 05:50:28
You'll have to declare Counter as Static. Also, you shouldn't use Val() on Integers.

Report •

#2
July 14, 2010 at 05:54:16
Hi,

I can't comment on the Access part of your code, but I think that you have two main problems, the subroutine structure and the 'life' of the variable counter

The subroutine needs to be changed - I don't see a need for it to be in a subroutine , and certainly not the Gosub .. Label: type.
VBA help says: "Creating separate procedures that you can call may provide a more structured alternative to using GoSub...Return".

The variable "Counter" is destroyed when the button click subroutine closes, so every time it runs it starts with Counter at zero

A.
The subroutine uses the uncommon GoSub procedure, and jumps to the Label LogCounter: when rst.NoMatch is TRUE

I do not see any Return following LogCounter: ... so I do not see a path for the code to continue.
After LogCounter: it will just continue to the end (although I don't see any End Sub)

I also note that the code appears to continue after the second End If and 'falls through' into the LogCounter code.

I don't see a need to put the LogCounter code into a separate subroutine - and if I did use a subroutine, I would make a clearly separate subroutine
Sub LogCounter(count as integer)
...
End sub

Why not put the incrementing code and the messages in the main part of the code following rst.NoMatch
I can't test your code, so this is theoretical:

If rst.NoMatch Then
    If Counter < 3 Then
        Counter = Counter + 1
        MsgBox "Invalid Login Details!", vbCritical
        
        ElseIf Counter = 3 Then
        MsgBox "Your Login Details Have Been Incorrect 3 Times!" _
            & vbNewLine & "Application Will Now Quit!", vbCritical
        Application.Quit
    End If
    Else
    If rst![Password] = Password Then
        User_key = rst![Key_Number]
        MsgBox BoxText & vbNewLine & vbNewLine & " " _
            & "Welcome!" & vbNewLine & " " & rst![Firstname] & " " _
            & rst![Surname], vbOKOnly, "Welcome"
        DoCmd.OpenForm "Main Menu", acNormal, , , acFormPropertySettings, _
            acWindowNormal, User_key
        DoCmd.Close acForm, "Logon", acSaveNo
    End If
End If

As an aside - you don't need to use Val
Counter=Counter +1 is fine

B.
I see that the routine you pasted runs when a button is clicked:
Private Sub SignOn_Click()

This means that the variable Counter only has a life when this code is run
You Dim it each time the code starts after the button click
This means that Counter always starts at zero - it is a new variable,
you increment it to 1
The button click sub closes and the value of counter disappears, only to restart at zero when the button is clicked again.

There are two possible solutions:
1. Remove Dim Counter as Integer from the subroutine and put it above the initialization code for the form (if this is a form) The idea is that the Counter variable belongs to the whole form or to the Access database that is running and can be accessed and incremented by the button click code.
Not being familiar with Access programming, I can't give you precise details - but basically you make Counter a global variable.

2. Record the value of the counter on the form that contains the logon button - perhaps as a label - the user can see the logon count.
The Button click code then starts by retrieving the value from the label (it will be text - so convert it) and after incrementing it, it updates the label text.

If the label is UserForm.Label1
Counter=Cint(UserForm,Label1.Caption)
...
UserForm.Label1.Caption = Format(Counter,"0")

Hope this gives you some clues as to what needs to be done.

Regards


Report •

#3
July 14, 2010 at 07:34:09
I got It working thanks

For those who are interested, code is now as follows:

Private Sub Form_Load()

DisableShiftKey.SetBypassProperty
EnableDisableControlBox (False)
DoCmd.OpenForm "ChkClose", , , , , acHidden
Counter = 1

End Sub

Private Sub SignOn_Click()

Dim msg
Dim User_key As Integer
Dim db As Database
Dim rst As Recordset
Dim BoxDate As String
Dim BoxTime As String
Dim BoxText As String

Me.LogCnt.Caption = Counter
BoxDate = Date
BoxTime = Time
Date = FormatDateTime(Date, vbShortDate)
BoxText = BoxDate & " " & BoxTime
Set db = CurrentDb()
Set rst = db.OpenRecordset("Users")
rst.Index = "Logon-ID"
rst.Seek "=", Logon_id
If rst.NoMatch Then
If Counter < 3 Then
Counter = Counter + 1
MsgBox "Invalid Login Details!", vbCritical
ElseIf Counter = 3 Then
MsgBox "Your Login Details Have Been Incorrect 3 Times!" & vbNewLine & "Application Will Now Quit!", vbCritical
Application.Quit
End If
Else
If rst![Password] = Password Then
User_key = rst![Key_Number]
MsgBox BoxText & vbNewLine & vbNewLine & " " & "Welcome!" & vbNewLine & " " & rst![Firstname] & " " & rst![Surname], vbOKOnly, "Welcome"
DoCmd.OpenForm "Main Menu", acNormal, , , acFormPropertySettings, acWindowNormal, User_key
DoCmd.Close acForm, "Logon", acSaveNo
End If
End If
End Sub

I made use of an invisible label on the form to get this right

Thanks


Report •

Related Solutions

#4
July 14, 2010 at 08:32:15
I made use of an invisible label on the form to get this right
You found that easier than just replacing Dim Counter with Static Counter?

Report •

#5
July 15, 2010 at 00:15:50
I tried the static counter, but it for some reason did not increment correctly still. kept looping around the invalid login still

Cant remember how it was coded though


Report •

Ask Question