Subform Will Not Requery (1 Viewer)

[DazJWood]

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

 

[MarkK]

Guessing...

Forms("frmClient").sbfClient.Form.sbfClientBookingConfirmed.Form.Requery

or prettier still...

Cancel = True
With Forms("frmClient").sbfClient.Form
  .sbfClientBookingConfirmed.Form.Requery
  .sbfClientBooking.Form.Requery

  .sbfClientBooking.Visible = True
  .lblBooking.Visible = False
End With

 

[DazJWood]

Thanks,

But this still does not work. Except as well as it not querying I get an:

"Error 3246. Operation not supported in transaction"

I don't think it's a referencing problem as I use the exact lines to requery this subform else where in my database and it works just fine. It does not make any sense as to why it's not working.

Anyone else any ideas?

Daz

 

[DazJWood]

Have now determined that if I stick a command button on the top parent form (frmClient) with :

[Forms]![frmClient]![sbfClient]![sbfClientBookingConfirmed].Requery
[Forms]![frmClient]![sbfClient]![sbfClientBooking].Requery

then it works fine. But obviously I don't want to be clicking a button.
I don't understand why it won't query at all