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.
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.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
Many thanks in advance to anyone that can provide some input.