Runtime Error

[Don]

I am trying to run this code in the On Close or the On Unload event of my form:

Private Sub Form_Unload(Cancel As Integer)
If IsNull(Me.PrintDate) And IsNull(Me.Date) Then

Else
If IsNull(Me.PrintDate) Then
Me.[PrintDate] = Now
Me.DidNotPrint = "Yes"



End If
End If

End Sub

The purpose is to document that the form's data was not printed via the
submit button on the form. If the user closes the form by the close X then I
want the PrintDate to record todays date and I want the DidNotPrint to be
"Yes". What I am getting is a Runtime Error "-2147352567 (800200009)": You
can't assign a value to this object. The funny thing is it will run without
an error at first then after the first order submitted, the next submit or
attempt to close the form results in the above message. I have similar code
that runs in the the Quit button using the following code:

Private Sub Command27_Click()
On Error GoTo Err_Command27_Click

Dim stDocName As String

If IsNull(Me.PrintDate) And IsNull(Me.Date) Then
stDocName = "Quit"
DoCmd.RunMacro stDocName

Else
If IsNull(Me.PrintDate) Then
Me.PrintDate = Now
Me.DidNotPrint = "Yes"

stDocName = "Quit"
DoCmd.RunMacro stDocName

End If
End If
Exit_Command27_Click:
Exit Sub

Err_Command27_Click:
MsgBox Err.Description
Resume Exit_Command27_Click

End Sub

What might be the problem?

 

[John W. Vinson]

I am trying to run this code in the On Close or the On Unload event of my form:

Private Sub Form_Unload(Cancel As Integer)
If IsNull(Me.PrintDate) And IsNull(Me.Date) Then

Else
If IsNull(Me.PrintDate) Then
Me.[PrintDate] = Now
Me.DidNotPrint = "Yes"



End If
End If

End Sub

Use the form's BeforeUpdate event instead. Both close and unload come too late
- the record has already been saved to disk at that point.

 

[Don]

One more thing I need to post to complete this subject. John your solution
worked.

However, once I made the change it seemed to make no difference. This has
happened to me before with other versions of Access but always using the 2000
format. I have spent many more hours trying variations of suggested code to
no avail. However, in my struggles and in the past by mistake, I have closed
the db which compacts it (based on my set-up). When I reopen the db the
original suggested code worked? So my recommendation to all new users is if
the suggested code does not work, try to compact the db, close it and then
reopen it and oddly, it works!
--
Thanks,

Dennis

I am trying to run this code in the On Close or the On Unload event of my form:

Private Sub Form_Unload(Cancel As Integer)
If IsNull(Me.PrintDate) And IsNull(Me.Date) Then

Else
If IsNull(Me.PrintDate) Then
Me.[PrintDate] = Now
Me.DidNotPrint = "Yes"



End If
End If

End Sub

Use the form's BeforeUpdate event instead. Both close and unload come too late
- the record has already been saved to disk at that point.

 

[John W. Vinson]

One more thing I need to post to complete this subject. John your solution
worked.

However, once I made the change it seemed to make no difference. This has
happened to me before with other versions of Access but always using the 2000
format. I have spent many more hours trying variations of suggested code to
no avail. However, in my struggles and in the past by mistake, I have closed
the db which compacts it (based on my set-up). When I reopen the db the
original suggested code worked? So my recommendation to all new users is if
the suggested code does not work, try to compact the db, close it and then
reopen it and oddly, it works!

I would turn Compact on Close *OFF* - it can sometimes help but it often does
more harm than good. You can compact at any time, manually, and doing so often
does resolve this kind of problem.

If it doesn't you can escalate - you can Decompile a database using the
(poorly or un-documented) command line switch /Decompile. This will clean out
the compiled VBA code; you can then compact the database to clear it out
completely, compile the code, and compact once more. Good backups are
essential of course!

 

[David W. Fenton]

I would turn Compact on Close *OFF* - it can sometimes help but it
often does more harm than good.

It *never* helps, ever. Compact on Close is an abomination that
never should have been implemented. Where it is not dangerous, it is
merely useless. And dangerous and useless cover the entire gamut of
how to describe it.