Results 1 to 2 of 2
  1. #1
    kamathmanoj is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    May 2019
    Posts
    41

    Unhappy Public Variables (As String) Vs. Public Variables (As Integer) & the acCmdRecordsGoToNew

    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

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Please post code between CODE tags to retain indentation and readability. You could edit your post.

    Advise not to use spaces nor punctuation/special characters (underscore only exception but I don't use it) in naming convention. Better would be JobNum or Job_Num.

    Is Date_Stamp_Login a date/time type field? If yes, its parameter should be delimited with # instead of apostrophe.

    Have you step debugged? Set breakpoint and step through code. Disable error handler - comment the On Error line. Where does variable lose value?

    Global variables lose value during run-time error, however, since one of the variables holds value something else must be causing the variable to lose value.

    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 01-25-2019, 09:03 PM
  2. Properly Quote Variables In SQL String
    By chalupabatman in forum Macros
    Replies: 12
    Last Post: 08-24-2017, 10:02 PM
  3. Using Public variables between forms/queries/reports
    By dcrake in forum Sample Databases
    Replies: 2
    Last Post: 12-25-2015, 05:44 PM
  4. VBA Public variables
    By jmitchelldueck in forum Programming
    Replies: 5
    Last Post: 08-20-2015, 12:03 PM
  5. public variables
    By zul in forum Programming
    Replies: 3
    Last Post: 08-23-2011, 11:11 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums