Unload and OnError Events

D

Daniel Dickover

I am encountering a problem with my unload event if I have
trapped an error with my OnError event. I think it has to
do with the acDataErrContinue constant. After I trap the
error the form goes blank and then the Unload event gives
me an 'Invalid use of null' message. How do I keep the
form from going blank?

Thanks

Daniel
 
K

Ken Snell

Can you tell us more about what code is running in the OnError event? And
how the form's properties are set up (e.g., is this a data entry form? a
data editing form? what is the form's recordsource? etc.).
 
D

Daniel Dickover

The form is a data editing form though it is used to enter
appointments also (I have no specific form designated
as 'Data Entry.') The form's recordsource is a query.
The Unload event is coded to add a record to the confirm
table if necessary and to add a record to the job table if
necessary. The code for both of these tasks uses
rs.addnew.
Hope that clarifies some.

Thanks.

Dan
 
K

Ken Snell

Not completely...... can you share the code that you're running? I still
don't have enough info to fully "see" the setup.
 
D

Daniel Dickover

Thanks for your patience. I have narrowed it down a bit
more. For some reason, if I close my form with the 'X'
button on the top of the form, the code executes as it
should. I also have a 'Close' button using DoCmd.Close
acForm, and if I use that, I get the 'Invalid use of Null'
error on the line with a star in the code. Mmmm?

Dan

Private Sub Form_Unload(Cancel As Integer)
'Check to see if a confirmation or a job ticket needs to
be created
'If they do, check if they exist already and if not
'create new ones in the table to start the process

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim cnt As Integer
Dim strCreated As String

On Error GoTo Error_Handler
'Set blnUpdate to false so that if form is opened again
and fApptSite is changed
'then prompts for entry of fApptDeptCust
blnUpdate = False
'Currently we do not confirm Spanish internal appointments
'Remove this if you want to confirm all appointments
If Me!fLangName = "Spanish" And Not Me!fApptSite = "CUST"
Then
Exit Sub
Else
'Open rs and check if there is a Conf already
***** CurrentApptID = Me!fApptID ******
Set cn = CurrentProject.Connection
Set rs = New Recordset
rs.Open "tConf", cn, adOpenDynamic, adLockOptimistic
rs.Find "fConfApptID ='" & CurrentApptID & "'"
If Not rs.EOF Then
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
Exit Sub
Else
strCreated = Format$(Now(), "MMDDYY-HHMM ") & "-"
& CurrentUser
With rs
'If first record then cnt starts at 1
If .BOF Then
cnt = 1
Else
.MoveLast
'Get the last value in fConfID
cnt = !fConfID
'Generate the ID string for output
cnt = cnt + 1
End If
.AddNew
!fConfID = cnt
!fConfApptID = CurrentApptID
!fConfCreated = strCreated
.Update
End With
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
End If
'Open rs and see if there is a tJob already. If not,
create one.
If Me!fApptSite = "Cust" Then
Set cn = CurrentProject.Connection
Set rs = New Recordset
rs.Open "tJob", cn, adOpenDynamic, adLockOptimistic
rs.Find "fJobApptID ='" & CurrentApptID & "'"
If Not rs.EOF Then
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
Exit Sub
Else
With rs
'If first record then cnt starts at 1
If .BOF Then
cnt = 1
Else
.MoveLast
'Get the last value in fConfID
cnt = !fJobID
'Generate the ID string for output
cnt = cnt + 1
End If
.AddNew
!fJobID = cnt
!fJobApptID = CurrentApptID
.Update
End With
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
End If
End If
End If
Exit Sub
Error_Handler:
MsgBox Err.Description
MsgBox "There was an error unloading the form. Please
see database manager."
Open ErrorLogLocation For Append As #1
Print #1, Date, Time, "frmAppt_Unload", Err.Number,
Err.Description
Close #1
Exit Sub
End Sub
 
K

Ken Snell

My guess is that the form no longer has the values for the controls in the
OnUnload event....likely the form has already destroyed the controls so
there is no value there to get. Is it necessary to use the Unload event for
what you want to do? Why not use the code of the command button that is
closing the form?
 
G

Guest

Ken,

Can I check for errors without using the OnError event? For example, as you suggested
when I click the 'Close' button? If so how do I do that?

Thank

Dan
 
K

Ken Snell

Depending upon what you mean by "errors"..... How 'bout you give me a
specific example of an error that you'd want to trap when the user wants to
close the form?
 
D

Daniel Dickover

Say when I click the 'Close' button can it check that all
of my required fields are not null (err 3314)? Or is that
something that needs to be done by field? What about
checking that I am not creating a duplicate record (err
3022)?

I think I am at the point that I need some formal
education. I can see what I want but cannot always get
myself there.

Thanks

Dan
 
K

Ken Snell

Something like this might get you started:

Private Sub cmdCloseButton_Click()
If IsNull(Me.Control1.Value) = True Or _
IsNull(Me.Control2.Value) = True Or _
IsNull(Me.Control3.Value) = True Then
MsgBox "You cannot leave a textbox empty!", _
vbExclamation, "Empty Textbox(es)!"
ElseIf DCount("*", Me.RecordSource, "[PrimaryKeyField]=" & _
Me.PrimaryKeyControl.Value) > 0 Then
MsgBox "Your entry will create a duplicate record." & _
" You cannot save it.", vbExclamation, _
"Duplicate Record Would Be Created"
Else
DoCmd.Close acForm, Me.Name
End If
End Sub


Note that there are many ways you can write code to do these types of
validations in this manner. However, keep in mind that doing a validation in
the command button's Click event means that the form can't have tried
already to save the record; in that situation, use the form's BeforeUpdate
event to do the validations and cancel the update if something isn't right.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top