having problems using public variables

atrium

Registered User.
Local time
Tomorrow, 08:38
Joined
May 13, 2014
Messages
348
I have created a standard module called "PublicLogin"
Option Compare Database
' setting up the public variables for the User and the Branch
Option Explicit
Public pintUserId As Integer
Public ptxtUserCode As String
Public pintBranchId As Integer
Public ptxtBranchCode As String

I have also created a small login form called LoginFrm

I ask for the Branch, for the User Code and their password.

The following code is on the 'on click' event on the LoginButt

Private Sub LoginButt_Click()
'Check that Branch and UserCode have been selected
If IsNull(Me.BranchCodeFld) Then
MsgBox "You need to select a Branch!", vbCritical
Me.BranchCodeFld.SetFocus
Else
If IsNull(Me.UserCodeFld) Then
MsgBox "You need to select a user!", vbCritical
Me.UserCodeFld.SetFocus
Else
'Check for correct password
If Me.PasswordFld = Me.UserCodeFld.Column(3) Then
'Check if password needs to be reset
If Me.UserCodeFld.Column(4) = True Then
pintUserId = Me.UserCodeFld.Column(0)
pintBranchId = Me.BranchCodeFld.Column(0)
ptxtUserCode = Me.UserCodeFld.Column(1)
ptxtBranchCode = Me.BranchCodeFld.Column(1)
DoCmd.OpenForm "frmPasswordChange", , , "[UserID] = " & Me.UserIdFld
End If
pintUserId = Me.UserCodeFld.Column(0)
pintBranchId = Me.BranchCodeFld.Column(0)
ptxtUserCode = Me.UserCodeFld.Column(1)
ptxtBranchCode = Me.BranchCodeFld.Column(1)
MsgBox (pintUserId & pintBranchCode & ptxtUserCode & ptxtBranchCode)
DoCmd.OpenForm "TogasMenu"
Me.Visible = False
Else
MsgBox "Password does not match, please re-enter!", vboOkOnly
Me.PasswordFld = Null
Me.PasswordFld.SetFocus
End If
End If
End If
End Sub


The first 'pintUserId' gives me Compiler Error - Ambiguous name detected

Question 1. how do I activate the standard module PublicLogin - how do I get the variables declared and in memory.

Question 2. Do I need to somehow execute the standard module in the LoginFrm ?

This is probably a simple concept but I have read a lot of posts but as yet I haven't been able to come to grips with it.

Thank you in anticipation
 
Use code brackets to make your code readable. Edit->Advanced->select code->click #
 
..
The first 'pintUserId' gives me Compiler Error - Ambiguous name detected
..
The errormessage tells you that you've declare 'pintUserId' twice somewhere in your code.
 
Code:
 Private Sub LoginButt_Click()
'Check that Branch and UserCode have been selected
If IsNull(Me.BranchCodeFld) Then
    MsgBox "You need to select a Branch!", vbCritical
    Me.BranchCodeFld.SetFocus
Else
    If IsNull(Me.UserCodeFld) Then
       MsgBox "You need to select a user!", vbCritical
       Me.UserCodeFld.SetFocus
    Else
      'Check for correct password
      If Me.PasswordFld = Me.UserCodeFld.Column(3) Then
         pintUserId = Me.UserCodeFld.Column(0)
         pintBranchId = Me.BranchCodeFld.Column(0)
         ptxtUserCode = Me.UserCodeFld.Column(1)
         ptxtBranchCode = Me.BranchCodeFld.Column(1)
      
        'Check if password needs to be reset
        If Me.UserCodeFld.Column(4) = True Then
            DoCmd.OpenForm "frmPasswordChange", , , "[UserID] = " & Me.UserIdFld
        End If
         MsgBox (pintUserId & pintBranchCode & ptxtUserCode & ptxtBranchCode)
        DoCmd.OpenForm "TogasMenu"
        Me.Visible = False
     Else
        MsgBox "Password does not match, please re-enter!", vboOkOnly
        Me.PasswordFld = Null
        Me.PasswordFld.SetFocus
     End If
   End If
End If
 End Sub

The only place I have used the variable name is when I declared it
 
highlight the variable and press shift-F2. The compiler should take you to the declaration of the variable. It won't because it is duplicated.

It might be the name of a field/control on the form as well as a variable, or the variable might be there twice. Either way, Access can't decide which on to use.
 
Code:
 Private Sub LoginButt_Click()
'Check that Branch and UserCode have been selected
If IsNull(Me.BranchCodeFld) Then
    MsgBox "You need to select a Branch!", vbCritical
    Me.BranchCodeFld.SetFocus
Else
    If IsNull(Me.UserCodeFld) Then
       MsgBox "You need to select a user!", vbCritical
       Me.UserCodeFld.SetFocus
    Else
      'Check for correct password
      If Me.PasswordFld = Me.UserCodeFld.Column(3) Then
         pintUserId = Me.UserCodeFld.Column(0)
         pintBranchId = Me.BranchCodeFld.Column(0)
         ptxtUserCode = Me.UserCodeFld.Column(1)
         ptxtBranchCode = Me.BranchCodeFld.Column(1)
      
        'Check if password needs to be reset
        If Me.UserCodeFld.Column(4) = True Then
            DoCmd.OpenForm "frmPasswordChange", , , "[UserID] = " & Me.UserIdFld
        End If
         MsgBox (pintUserId & pintBranchCode & ptxtUserCode & ptxtBranchCode)
        DoCmd.OpenForm "TogasMenu"
        Me.Visible = False
     Else
        MsgBox "Password does not match, please re-enter!", vboOkOnly
        Me.PasswordFld = Null
        Me.PasswordFld.SetFocus
     End If
   End If
End If
 End Sub
The only place I have used the variable name is when I declared it

Make sure the VBA code module for the form contains
Option Explicit at the start.
This forces it to check all variables actually EXIST.
It should be mandatory but I think it's optional
Without Option Explicit, the code generates the variables by itself as it finds them. It means that if you misspell variables it will generate different variables, so it's dangerous and very sloppy!!
You've defined the variable as Public in your module, but the forms code without Option Explicit will re-define it and then generate a duplicate error.
 
I have over come the duplicate problem.
You will notice I have used a MsgBox statement to display the values of the public variables after they have been set. They show all the right info.

My problem now is that when I use the public variables in the form that is called after the login I get a #Name? error.

The Standard Module is called PublicLogin

Option Compare Database
' setting up the public variables for the User and the Branch
Option Explicit
Public pintUserId As Integer
Public ptxtUserCode As String
Public pintBranchId As Integer
Public ptxtBranchCode As String



the LoginFrm Code is
Private Sub LoginButt_Click()
'Check that Branch and UserCode have been selected
If IsNull(Me.BranchCodeFld) Then
MsgBox "You need to select a Branch!", vbCritical
Me.BranchCodeFld.SetFocus
Else
If IsNull(Me.UserCodeFld) Then
MsgBox "You need to select a user!", vbCritical
Me.UserCodeFld.SetFocus
Else
'Check for correct password
If Me.PasswordFld = Me.UserCodeFld.Column(3) Then
UserIdFld = Me.UserCodeFld.Column(0)
pintUserId = Me.UserCodeFld.Column(0)
BranchIdFld = Me.BranchCodeFld.Column(0)
pintBranchId = Me.BranchCodeFld.Column(0)
ptxtUserCode = Me.UserCodeFld.Column(1)
ptxtBranchCode = Me.BranchCodeFld.Column(1)

'Check if password needs to be reset
If Me.UserCodeFld.Column(4) = True Then
DoCmd.OpenForm "frmPasswordChange", , , "[UserID] = " & Me.UserIdFld
End If
MsgBox ("User Id = " & pintUserId & " Branch Id = " & " " & pintBranchId & " User Code = " & " " & ptxtUserCode & " Branch Code = " & " " & ptxtBranchCode)

DoCmd.OpenForm "TogasMenu"
Me.Visible = False
Else
MsgBox "Password does not match, please re-enter!", vboOkOnly
Me.PasswordFld = Null
Me.PasswordFld.SetFocus
End If
End If
End If
End Sub

This is driving me crazy
 
Sorry I didn't wrap the code
Code:
Private Sub LoginButt_Click()
'Check that Branch and UserCode have been selected
If IsNull(Me.BranchCodeFld) Then
    MsgBox "You need to select a Branch!", vbCritical
    Me.BranchCodeFld.SetFocus
Else
    If IsNull(Me.UserCodeFld) Then
       MsgBox "You need to select a user!", vbCritical
       Me.UserCodeFld.SetFocus
    Else
      'Check for correct password
      If Me.PasswordFld = Me.UserCodeFld.Column(3) Then
         UserIdFld = Me.UserCodeFld.Column(0)
         pintUserId = Me.UserCodeFld.Column(0)
         BranchIdFld = Me.BranchCodeFld.Column(0)
         pintBranchId = Me.BranchCodeFld.Column(0)
         ptxtUserCode = Me.UserCodeFld.Column(1)
         ptxtBranchCode = Me.BranchCodeFld.Column(1)
      
        'Check if password needs to be reset
        If Me.UserCodeFld.Column(4) = True Then
            DoCmd.OpenForm "frmPasswordChange", , , "[UserID] = " & Me.UserIdFld
        End If
         MsgBox ("User Id = " & pintUserId & " Branch Id = " & " " & pintBranchId & " User Code = " & " " & ptxtUserCode & " Branch Code = " & " " & ptxtBranchCode)
        
        DoCmd.OpenForm "TogasMenu"
        Me.Visible = False
     Else
        MsgBox "Password does not match, please re-enter!", vboOkOnly
        Me.PasswordFld = Null
        Me.PasswordFld.SetFocus
     End If
   End If
End If
 End Sub
 
I think that somewhere in your project you have one more "pintUserId" declared as public variable.
Do a search and look to all places where the "pintUserId" appear.
 
I have over come the duplicate problem.
You will notice I have used a MsgBox statement to display the values of the public variables after they have been set. They show all the right info.

My problem now is that when I use the public variables in the form that is called after the login I get a #Name? error.

The Standard Module is called PublicLogin

Option Compare Database
' setting up the public variables for the User and the Branch
Option Explicit
Public pintUserId As Integer
Public ptxtUserCode As String
Public pintBranchId As Integer
Public ptxtBranchCode As String



the LoginFrm Code is


This is driving me crazy

You don't use the public variables directly as the ControlSOurce

Set the form fields to UNBOUND and then move the public variables into them or if they are linked to a table move the public variables into fields. It always works fine no problem.

eg set Controlsource for Field1 = Unbound
field1=pintBranchID
or set Controlsource for Fields1=BranchID
BranchID = pintBranchID

This obviously works the other way round to. ie move the UNBOUND field from the form into a public variable.
eg pintBranchID = field1 etc
 

Users who are viewing this thread

Back
Top Bottom