me.requery error - Help!

[Guest]

The following sub runs when a button on my form
is clicked. It saves information to a table,
deletes it from a temp table, then refreshes the
form to reflect the new recordset in the temp table.

Problem is after the update/insert and delete sequence
I get an error "Run-time error '3167' Record is deleted".
When I click "debug", the me.requery line is highlighted.

How do I update my form's recordset after deleting a record?


Code:

Private Sub cmdAddToHistory_Click()
'On Error GoTo Err_cmdAddToHistory_Click
'DoCmd.SetWarnings False

Dim XLEmployeeID As Integer, EmployeeName As String, _
EmployeeAddress1 As String, EmployeeAddress2 As String, _
Employeeid As Integer, EmployeeCity As String, _
EmployeeState As String, EmployeeZip As String, _
EmployeePhone As String, EmployeePhoneExt As String, _
EmployeeFax As String, EmployeeEmail As String, _
EmployeeNotes As String, Clientid As Integer

If Me.cboEmployeeList.Value = "" Then Me.cboEmployeeList.Value = Null

If Not IsNull(Me.Employeeid.Value) Then XLEmployeeID = Me.Employeeid.Value
If Not IsNull(Me.EmployeeName.Value) Then EmployeeName = Me.EmployeeName.Value
If Not IsNull(Me.cboEmployeeList.Value) Then Employeeid =
Me.cboEmployeeList.Value
If Not IsNull(Me.EmployeeAddress1.Value) Then EmployeeAddress1 =
Me.EmployeeAddress1.Value
If Not IsNull(Me.EmployeeAddress2.Value) Then EmployeeAddress2 =
Me.EmployeeAddress2.Value
If Not IsNull(Me.EmployeeCity.Value) Then EmployeeCity = Me.EmployeeCity.Value
If Not IsNull(Me.EmployeeState.Value) Then EmployeeState =
Me.EmployeeState.Value
If Not IsNull(Me.EmployeeZip.Value) Then EmployeeZip = Me.EmployeeZip.Value
If Not IsNull(Me.EmployeePhone.Value) Then EmployeePhone =
Me.EmployeePhone.Value
If Not IsNull(Me.EmployeePhoneExt.Value) Then EmployeePhoneExt =
Me.EmployeePhoneExt.Value
If Not IsNull(Me.EmployeeFax.Value) Then EmployeeFax = Me.EmployeeFax.Value
If Not IsNull(Me.EmployeeEmail.Value) Then EmployeeEmail =
Me.EmployeeEmail.Value
If Not IsNull(Me.EmployeeNotes.Value) Then EmployeeNotes =
Me.EmployeeNotes.Value
If Not IsNull(Me.Clientid.Value) Then Clientid = Me.Clientid.Value

If XLEmployeeID = 0 Then
MsgBox "Can't add - none selected.", vbCritical, "Can't add."
Else
If Employeeid = 0 Then
'new employee to database - add
DoCmd.RunSQL "insert into employee (employeename, employeeaddress1,
employeeaddress2, employeecity, employeestate, employeezip, employeephone,
employeephoneext, employeefax, employeeemail, employeenotes, clientid) " & _
"values (""" & EmployeeName & """, """ &
EmployeeAddress1 & """, """ & EmployeeAddress2 & """, """ & EmployeeCity &
""", """ & EmployeeState & """, """ & EmployeeZip & """, """ & EmployeePhone
& """, """ & EmployeePhoneExt & """, """ & EmployeeFax & """, """ &
EmployeeEmail & """, """ & EmployeeNotes & """, " & Clientid & ")"
Else
'update existing employee record
DoCmd.RunSQL "update employee set employeename = """ & EmployeeName &
""", employeeaddress1 = """ & EmployeeAddress1 & _
""", employeeaddress2 = """ & EmployeeAddress2 & """,
employeecity = """ & EmployeeCity & _
""", employeestate = """ & EmployeeState & """,
employeezip = """ & EmployeeZip & _
""", employeephone = """ & EmployeePhone & """,
employeephoneext = """ & EmployeePhoneExt & _
""", employeefax = """ & EmployeeFax & """,
employeeemail = """ & EmployeeEmail & _
""", employeenotes = """ & EmployeeNotes & """, clientid
= " & Clientid & " " & _
"where employeeid = " & Employeeid

End If
DoCmd.RunSQL "delete from tempxlemployee where employeeid = " &
XLEmployeeID
Me.cboEmployeeList.Value = Null
Call clearOldValues
End If

Exit_cmdAddToHistory_Click:
Me.Requery

'DoCmd.SetWarnings True
Exit Sub

Err_cmdAddToHistory_Click:
MsgBox Err.Description

Resume Exit_cmdAddToHistory_Click


End Sub

 

[Guest]

Are you sure your update and insert SQL works? Because when I look at it I
see you are using """ for non-numeric values. It should be "'" because you
already stated " before your SQL began, so in this case Insert and or Update.
Try putting requery after End IF before the Exit_cmdAddToHistory_Click:

 

[Guest]

The SQL insert/update statements work.

I've tried putting requery after End If and get the same response. Any
other suggestions?

Thanks!