Hello Everyone,
I have a strange issue with my public variables. I have searched long and hard (probably not enough, given that I am writing this post)
I have initialized 2 public variables that I am using in a module called Public (the only module I have).
Public JNM_Coil_tag As String
Public Job_No As Integer
I am calling these variables to store 2 values entered in a form.
Once those values are stored, I use message boxes to display the values and they work fine.
In the same form, clicking a button calls the DoCmd.RunCommand acCmdRecordsGoToNew to create a new record, within a function button click() which is a public function. I need this to write these same variables to the new record.
When I call the variables in the same public function with simple message boxes:
Msgbox (JNM_Coil_tag) = this results in an error 94: Invalid Use of NULL
Msgbox (Job_No) = this message box displays the assigned value from the form.
Would anyone here be able to guide me on how I can get around this issue?
Note: Not calling the DoCmd.RunCommand acCmdRecordsGoToNew allows the public variables both integer and string to hold their values. But I need to write these 2 values to a new record.
Any help will be greatly appreciated.
Regards,
Manny
Here is the function code:
Public Sub Login_Employee_1_Click()
On Error GoTo Err_Login_Employee_1_Click
'Set Global Variables to values entered on form
Press_No = Me![PRESS #]
Job_No = Me.[JOB #]
Op_No = Me.[OPERATION #]
JNM_Coil_tag = Me.[JNM_Coil_tag]
SHIFT = Me.[SHIFT]
SHIFT_SUPERVISOR = Me.[SHIFT SUPERVISOR]
Emp_Name = Me.[Employee_Name_1]
Date_Stamp = Me.[Date Stamp]
Code_No = Me![CODE]
REASON_SCRAPPED = "Other"
Reference_Logout_1 = Me![PRODUCTION ID]
'Save Employee # 1 Record Login Time Stamp
DoCmd.RunCommand acCmdSaveRecord
'Set table values to values from Form
DoCmd.RunSQL "UPDATE [PRODUCTION TIME & COUNTS] SET [Company OR Name]='" & Emp_Name & "', Date_Stamp_Login = '" & Date_Stamp & "', [Reference_Logout]= " & Reference_Logout_1 & " WHERE [PRODUCTION ID] = " & Reference_Logout_1 & ";"
MsgBox (Emp_Name & " Employee 1 Saved")
Me.Employee_Name_1.BackColor = RGB(0, 128, 64) 'GREEN
'Check if Code requires more employees
If Me![CODE] > "1" Then
MsgBox ("Enter Employee 2")
'Create New Record
DoCmd.RunCommand acCmdRecordsGoToNew 'This is where the trouble starts!!
'Set Form variables to Global Variables
MsgBox (Press_No) ' WORKS
Me![PRESS #] = Press_No
MsgBox (Job_No) ' WORKS
Me![JOB #] = Job_No
MsgBox (Op_No) ' WORKS
Me![OPERATION #] = Op_No
MsgBox (JNM_Coil_tag) ' Gives the invalid use of NULL error
Me![JNM_Coil_tag] = JNM_Coil_tag
MsgBox (SHIFT) ' Gives the invalid use of NULL error if I remove the previous two statements
Me![SHIFT] = SHIFT
Me![CODE] = Code_No
MsgBox (SHIFT_SUPERVISOR) ' Gives the invalid use of NULL error if I remove the previous 4 statements
Me![SHIFT SUPERVISOR] = SHIFT_SUPERVISOR
Me![Employee_Name_2].SetFocus
Else
MsgBox "Production Entry Complete"
End If
Exit_Add_New1_Click:
Exit Sub
Err_Login_Employee_1_Click:
MsgBox Err.Description
Resume Exit_Add_New1_Click
End Sub