I have a form with a number of subforms.
I am interrupting the delete event so I can use my own code to delete records or amend records. My code runs fine but I cannot get the form to requery. It displays no error and other than not requerying it runs fine. My code also requeries another form shoudl it be open but this works fine.
I'll show you all the code I have in the event but just highlight where it is falling over:
Private Sub Form_Delete(Cancel As Integer)
On Error GoTo ErrorHandler
CheckCandidate = 0
CheckBooking = 0
Response = 0
DoCmd.OpenForm "frmMsgBoxDelete", , , , , acDialog
' Check Response from Message Form
If Response = 1 Then
DoCmd.SetWarnings False
strSQL = ""
If CheckCandidate = 1 Then
strSQL = "UPDATE tblCandidateAvailability SET tblCandidateAvailability.Status = 'Available'" _
& " WHERE (((tblCandidateAvailability.CandidateID)=" & Me![CandidateID] & ") AND " _
& "((tblCandidateAvailability.AvailableDate)=#" & Format(Me![ScheduleDate], "mm/dd/yy") & "#));"
DoCmd.RunSQL strSQL
If IsLoaded("frmCandidate") Then
Call RefDates
End If
ElseIf CheckCandidate = 0 Then
strSQL = "DELETE tblCandidateAvailability.CandidateID, tblCandidateAvailability.AvailableDate " _
& "FROM tblCandidateAvailability " _
& "WHERE (((tblCandidateAvailability.CandidateID)=" & Me![CandidateID] & ") AND ((tblCandidateAvailability.AvailableDate)=#" & Format(Me![ScheduleDate], "mm/dd/yy") & "#));"
DoCmd.RunSQL strSQL
If IsLoaded("frmCandidate") Then
Call RefDates
End If
End If
If CheckBooking = 1 Then
StrSQL3 = "UPDATE tblBookingDetail SET tblBookingDetail.CandidateID = Null" _
& " WHERE (((tblBookingDetail.ScheduleDetailsID)=" & [ScheduleDetailsID] & ") AND " _
& "((tblBookingDetail.CandidateID)=" & Me![CandidateID] & "));"
DoCmd.RunSQL StrSQL3
' Cancel = True
If IsLoaded("frmCandidate") Then
[Forms]![frmCandidate]![sbfCandidateBookingConfirmed].Requery
End If
Cancel = True
[Forms]![frmClient]![sbfClient]![sbfClientBookingConfirmed].Requery
[Forms]![frmClient]![sbfClient]![sbfClientBooking].Requery
[Forms]![frmClient]![sbfClient]![sbfClientBooking].Visible = True
[Forms]![frmClient]![sbfClient]![lblBooking].Visible = False
Else
ScheduleCheck = Me![ScheduleID]
End If
DoCmd.SetWarnings True
ElseIf Response = 0 Then
Cancel = True
End If
Exit Sub
ErrorHandler:
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Sub
The code in BOLD is the code that refuses to requery. sbfClientBookingConfirmed is the subform, (which is set to datasheet view if it matters), where this delete event occurs. Basically the code runs when you delete a record on this form. Instead of running the normal delete event, it pops up another form asking whether the record should be deleted or the Candidate (in this case) just being removed from the booking, therefore leaving the record intact. (The code built in SQL removes the candidateID from the record). After this it should requery the form so that the record disappears off the sbfClientBookingConfirmed subform and appears then on the sbfClientBooking subform.
Both these requeries do not work and the records stay as they are. However the requery (which occurs on another form if open), above in RED works fine.
Also if I move off the main record though on the parent form (via a combo box) and then return back to my record the subforms now display the correct data.
I am baffled as to why this is so and I am sure I have overlooked something blindingly obvious.
Any suggestions would be appreciated.
Thanks,
Daz
I am interrupting the delete event so I can use my own code to delete records or amend records. My code runs fine but I cannot get the form to requery. It displays no error and other than not requerying it runs fine. My code also requeries another form shoudl it be open but this works fine.
I'll show you all the code I have in the event but just highlight where it is falling over:
Private Sub Form_Delete(Cancel As Integer)
On Error GoTo ErrorHandler
CheckCandidate = 0
CheckBooking = 0
Response = 0
DoCmd.OpenForm "frmMsgBoxDelete", , , , , acDialog
' Check Response from Message Form
If Response = 1 Then
DoCmd.SetWarnings False
strSQL = ""
If CheckCandidate = 1 Then
strSQL = "UPDATE tblCandidateAvailability SET tblCandidateAvailability.Status = 'Available'" _
& " WHERE (((tblCandidateAvailability.CandidateID)=" & Me![CandidateID] & ") AND " _
& "((tblCandidateAvailability.AvailableDate)=#" & Format(Me![ScheduleDate], "mm/dd/yy") & "#));"
DoCmd.RunSQL strSQL
If IsLoaded("frmCandidate") Then
Call RefDates
End If
ElseIf CheckCandidate = 0 Then
strSQL = "DELETE tblCandidateAvailability.CandidateID, tblCandidateAvailability.AvailableDate " _
& "FROM tblCandidateAvailability " _
& "WHERE (((tblCandidateAvailability.CandidateID)=" & Me![CandidateID] & ") AND ((tblCandidateAvailability.AvailableDate)=#" & Format(Me![ScheduleDate], "mm/dd/yy") & "#));"
DoCmd.RunSQL strSQL
If IsLoaded("frmCandidate") Then
Call RefDates
End If
End If
If CheckBooking = 1 Then
StrSQL3 = "UPDATE tblBookingDetail SET tblBookingDetail.CandidateID = Null" _
& " WHERE (((tblBookingDetail.ScheduleDetailsID)=" & [ScheduleDetailsID] & ") AND " _
& "((tblBookingDetail.CandidateID)=" & Me![CandidateID] & "));"
DoCmd.RunSQL StrSQL3
' Cancel = True
If IsLoaded("frmCandidate") Then
[Forms]![frmCandidate]![sbfCandidateBookingConfirmed].Requery
End If
Cancel = True
[Forms]![frmClient]![sbfClient]![sbfClientBookingConfirmed].Requery
[Forms]![frmClient]![sbfClient]![sbfClientBooking].Requery
[Forms]![frmClient]![sbfClient]![sbfClientBooking].Visible = True
[Forms]![frmClient]![sbfClient]![lblBooking].Visible = False
Else
ScheduleCheck = Me![ScheduleID]
End If
DoCmd.SetWarnings True
ElseIf Response = 0 Then
Cancel = True
End If
Exit Sub
ErrorHandler:
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Sub
The code in BOLD is the code that refuses to requery. sbfClientBookingConfirmed is the subform, (which is set to datasheet view if it matters), where this delete event occurs. Basically the code runs when you delete a record on this form. Instead of running the normal delete event, it pops up another form asking whether the record should be deleted or the Candidate (in this case) just being removed from the booking, therefore leaving the record intact. (The code built in SQL removes the candidateID from the record). After this it should requery the form so that the record disappears off the sbfClientBookingConfirmed subform and appears then on the sbfClientBooking subform.
Both these requeries do not work and the records stay as they are. However the requery (which occurs on another form if open), above in RED works fine.
Also if I move off the main record though on the parent form (via a combo box) and then return back to my record the subforms now display the correct data.
I am baffled as to why this is so and I am sure I have overlooked something blindingly obvious.
Any suggestions would be appreciated.
Thanks,
Daz