Global DIM's Not working?

[Nwtech75]

Greetings all,
My Name is Vince, I'm new to the community. I was wondering if I could get some input. I am working at a beginning to intermediate knowledge regarding VBA and Access, so please be as specific as you can. Oh and I am working with Access 2007-2010.

I am working with unbound Forms as I find them easiest to control how and when they add records to the table. Over the last few weeks I've started having some moderate success with the VBA codes. What I would like to do now is clean up the code and simplify it. The code below is from a Form that I am using that updates a Personnel table, specifically adds new members. As you can see I have Dim's outlined and defined before my first sub routine. They are currently commented out because they weren't being recognized by the sub's, and I still don't understand why. Both sub routines are for the same form so I thought that placing my Dim's at the beginning would be a good idea. In the end to make the buttons work I had to go what seems the long way. Can anyone explain why my desired method didn't yield the results that I was hoping for? For those of you who are reading carefully, I think you should be able to easily see the difference between what I was trying to do, and what I ultimately had to do to make the thing work.


Option Compare Database
'Call out the Dim's
'Dim First As String
'Dim Last As String
'Dim ID As String
'Dim Pass As String
'Dim Security As String
'Define the Dim's
'Set First = Me.txt_F_NAME.Value
'Set Last = Me.txt_L_NAME.Value
'Set ID = Me.txt_EMPID
'Set Pass = Me.txt_PSWD
'Set Security = Me.cmb_SEC.Value

Private Sub cmd_ADD_Click() 'Dims need clean up and possible set to Global's

'Set the DBase and Table Connections
Dim dbsFreeStock As DAO.Database
Set dbsFreeStock = CurrentDb
Dim rstUsers As DAO.Recordset
Set rstUsers = dbsFreeStock.OpenRecordset("Personnel")

'run the code
'rstUsers!F_NAME = First
'rstUsers!L_NAME = Last
'rstUsers!EMPID = ID
'rstUsers!PSWD = Pass
'rstUsers!SEC = Security

rstUsers.AddNew
rstUsers!F_NAME = Me.txt_F_NAME.Value
rstUsers!L_NAME = Me.txt_L_NAME.Value
rstUsers!EMPID = Me.txt_EMPID.Value
rstUsers!PSWD = Me.txt_PSWD.Value
rstUsers!SEC = Me.cmb_SEC.Value
rstUsers.Update

'First = Null
'Last = Null
'ID = Null
'Pass = Null
'Security = Null

Me.txt_F_NAME = Null
Me.txt_L_NAME = Null
Me.txt_EMPID = Null
Me.txt_PSWD = Null
Me.cmb_SEC = Null
Me.txt_F_NAME.SetFocus
End Sub

Private Sub cmd_CLOSE_Click()

'Set the DBase and Table Connections
Dim dbsFreeStock As DAO.Database
Set dbsFreeStock = CurrentDb
Dim rstUsers As DAO.Recordset
Set rstUsers = dbsFreeStock.OpenRecordset("Personnel")

If IsNull(Me.txt_F_NAME) Then
DoCmd.Close 'This one is working

Else

If MsgBox("Do you want to save the Current Record?", vbYesNo, "Save Entry?") = vbYes Then

rstUsers.AddNew
rstUsers!F_NAME = Me.txt_F_NAME.Value
rstUsers!L_NAME = Me.txt_L_NAME.Value
rstUsers!EMPID = Me.txt_EMPID.Value
rstUsers!PSWD = Me.txt_PSWD.Value
rstUsers!SEC = Me.cmb_SEC.Value
rstUsers.Update
DoCmd.Close

Else

Cancel = True 'This is working
DoCmd.Close

End If
End If
End Sub


One thought I would pose is that I did question the actual Dim's. The values are being obtained from Text and Combo boxes, so I wondered if I was declaring them incorrectly.

Many thanks in advance to anyone that can provide some input.

[jonh]

I'm not totally sure what you mean by not working.

But you can't set a variable outside of a procedure and you only use SET for object variables.


Dim First As String
Set First = Me.txt_F_NAME.Value



Dim First As String

private sub mysub()
First = Me!txt_F_NAME
end sub

[SamT]

You can't use "Set" outside a procedure

'Call out the Dims
Dim First As String
Dim Last As String
Dim ID As String
Dim Pass As String
Dim Security As String
'Define the Dims
First = Me.txt_F_NAME.Value
Last = Me.txt_L_NAME.Value
ID = Me.txt_EMPID
Pass = Me.txt_PSWD
Security = Me.cmb_SEC.Value

Do all those Controls actually have a value when the UserForm is Inititalized? If not, the variable Values will be = ""

Use code like this

'Call out the Dims
Dim First As String
Dim Last As String
Dim ID As String
Dim Pass As String
Dim Security As String

Private Sub txt_F_NAME_Exit(ByVal Cancel As MSForms.ReturnBoolean)
First = Me.txt_F_NAME.Value
End Sub

Private Sub txt_L_NAME_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Last = Me.txt_L_NAME.Value
End Sub

Private Sub Etc_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Etc = Me.Etc.Value
End Sub

Etc, etc

Private Sub cmd_ADD_Click()
Blah Blah
Etc

[HiTechCoach]

Also some of your variable names are reserved keywords and will cause you much headache.

[Nwtech75]

Many thanks to all who replied to the post. I eventually came to the same conclusion. What I was attempting to do was declare all my variables for the form in a single statement which, as I had hoped, would allow me to utilize all my variables repeatedly without declaring them for every new sub routine on the form. The form is currently working, albeit it I had to use some clever work around's, it is working. Can anyone suggest a method to accomplish what I am trying to do with the explanation in this post?

[SamT]

declare all my variables for the form in a single statement which, as I had hoped, would allow me to utilize all my variables repeatedly without declaring them for every new sub routine on the form. That sounds like one of Boyd's thousand ways to do it wrong.

Watch what happens when you run this code
Dim Counter as long
Dim i as Long

Sub Test1()
For Counter = 1 to 3
i = i + 1
MsgBox "Test1 Counter = " & Counter
Call Test2
MsgBox "Test1 Counter = " & Counter
Next

MsgBox "Test1 looped " & i & " Times"
End Sub

Sub Test2()
For Counter = 4 to 5
MsgBox "Test2 Counter = " & Counter
Next
End Sub



Can anyone suggest a method to accomplish what I am trying to do with the explanation in this post?Not without seeing the form.