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