Null Records in Form_UnLoad event

[grgimpy]

The form I am working with writes to a Table named "Silver Production Log.” I’ve written some code that also writes the field values to another table, “Silver PM Log”, when the user clicks an “Add Record” command button I created. This all works fine. I allow my users to go between the most recent record and the current record using “Previous Record” and “Next Record” command buttons. When a user edits a previous record, I’ve written code that edits the table field values in “Silver PM Log.” This all works fine with the code I will display below.

Now, I wanted to also put this same code into the Form_Unload event of my form, so that if for any reason the Database is closed abnormally, the record will still be saved. Here is the code:

Private Sub Form_Unload(Cancel As Integer)

MsgBox "you are closing this form"
''''THIS INSURES THAT VALUES ENTERED WILL
''''BE WRITTEN TO THE "Silver PM Log"
''''IN CASE THE FORM IS CLOSED INCORRECTLY

''''IF DATE FIELD IS NULL THEN DO NOT
''''ADD A RECORD TO "Silver PM Log"
    If IsNull(Me.Silver_Date_Time) Then
MsgBox "exit sub"
MsgBox Me.Silver_Date_Time.Value
        Exit Sub
    End If
    
''''ADD SHIFTLY PM ELEMENTS TO
''''PM LOG TABLE
    If Me.AddNewRecord.Enabled = True Then
MsgBox "records are being added"
        Set db = CurrentDb()
        Set rs = db.OpenRecordset("Silver PM Log", dbOpenTable)
        rs.AddNew
            rs![Silver-PMType] = "Shiftly"
            rs![Silver-Date/Time] = Me.Silver_Date_Time.Value
            rs![Silver-Operator] = Me.Silver_Operator.Value
            rs![Silver-Comments] = Me.Silver_Comments.Value
‘(more fields that I deleted for space reasons)
        rs.Update
    Else
MsgBox "records are being edited"
''''EDIT SHIFTLY PM ELEMENTS TO
''''PM LOG TABLE
        Set db = CurrentDb()
        Set rs = db.OpenRecordset("select *" & _
            " from [Silver PM Log]" & _
            " where [Silver-Date/Time]=#" & Me.Silver_Date_Time.Value & "#")
        rs.Edit
            rs![Silver-PMType] = "Shiftly"
            rs![Silver-Date/Time] = Me.Silver_Date_Time.Value
            rs![Silver-Operator] = Me.Silver_Operator.Value
            rs![Silver-Comments] = Me.Silver_Comments.Value
‘(more fields that I deleted for space reasons)
        rs.Update
    End If

    Set db = Nothing
    Set rs = Nothing

End Sub

I use the Date/Time stamp field, rs![Silver-Date/Time] , to locate the record I want to edit. I wrote the first part of code to exit the sub if there is no value in the [Silver-Date/Time] field, but for some reason Access always thinks this field is null, even when it is not. So, none of the records get added to the table “Silver PM Log”. Even when I take out that piece of code and just let it add or edit records each time, it only adds records that contain null values or tries to edit a record containing a null value in [Silver-Date/Time].

What do I have to do to save these records in the table “Silver PM Log.” All the records are always saved in “Silver Production Log,” so I’m not losing any information. But, I want to append the records in “Silver PM Log” for various reasons.

Thanks for any help.

 

[mp9]

If the code for your button works fine.... why not just have your form's unload event call your button's click event?

 

[grgimpy]

The above code works in the UnLoad event. Also, I can't call my button's click event b/c other things are associated with that event that I don't want to include in this one.

The problem is that Access thinks all my fields are null when it runs the Form_UnLoad event, even when they are not. I can't even get this piece of code to work:

Private Sub Form_Unload(Cancel As Integer)

msgbox me.Silver_Date_Time.value

End Sub

[Silver-Date/Time] is a field in my form. If the field is NOT null when the UnLoad event is fired, then why am I getting the error "invalid use of null"?

That is my question really.

 

[mp9]

Question: Do you get this error only on new records or on existing records too?

 

[grgimpy]

This error occurs on both new records and existing records.

 

[grgimpy]

Well, I was never able to figure this one out. I ended up just using the Form_Timer event to add/edit the record periodically, then call the event when the user clicks on the command buttons. It works well enough. This isn't a critical function of my database, so I'm not going to spend too much time on it.

I also tried using the Form_AfterUpdate event to run this code, but it seems like the AfterUpdate event fires randomly sometimes within the form. I guess I don't know as much about the Form events as I thought I did.

If anyone does know anything about why field values may become null on the Form_UnLoad event, I would still like to know. I know that the Form is still visible to the user during the UnLoad event, and it appears the fields still contain data from the underlying Table, but maybe that's not the case. I'd also like to know when exactly the Form_AfterUpdate event fires. I thought it would fire each time a field is updated within the form, but apparently not.

Thanks for any information regarding this in advance. I've searched around the Internet and it doesn't seem like the Form_UnLoad event is used for these purposes often, so maybe I was just going about it in the wrong way. Alright, done rambling.