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
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)
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
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, _
DoCmd.Close acForm, "Logon", acSaveNo
As an aside - you don't need to use Val
Counter=Counter +1 is fine
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
UserForm.Label1.Caption = Format(Counter,"0")
Hope this gives you some clues as to what needs to be done.