Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2002
    Posts
    16

    Unanswered: Subform Will Not Requery

    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

  2. #2
    Join Date
    Oct 2002
    Posts
    16
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •