locked
error on MS Access form "You can’t go to the specified record" RRS feed

  • Question

  • This form is used to add two missing fields from a table.  The form is bound to a query that identifies the records that are missing data in the two subject fields. Another query, TemporaryTransactions Query7, is an Update Query and is used to add the two missing fields to the table.  The Event Procedure updates the two fields for all records but I get the error “You can’t go to the specified record” after the update and the next form to be processed isn’t opened and the current form is not closed.  The Event Procedure code is as follows:

    While IsNumeric([ID]) = True

         DoCmd.SetWarnings False

         DoCmd.OpenQuery "TemporaryTransactions Query7", acViewNormal, acEdit

         DoCmd.SetWarnings True

         DoCmd.GoToRecord Record:=acNext

       Wend

         DoCmd.OpenForm "frmPayeePayorCategoryChecksDep", acNormal, "", "", , acNormal

         DoCmd.Close acForm, "frmPayeePayorCategoryByDescription"

    I would appreciate any help on this issue.  Bob
    Monday, November 23, 2020 2:37 PM

Answers

  • Sorry. After the DoCmd.CancelEvent is when you open and close the forms and then exit:

    RecErr:
    DoCmd.CancelEvent

    DoCmd.OpenForm "frmPayeePayorCategoryChecksDep", acNormal, "", "", , acNormal
    DoCmd.Close acForm, "frmPayeePayorCategoryByDescription"
    Exit Sub

    • Marked as answer by BobAubry Monday, November 23, 2020 10:31 PM
    Monday, November 23, 2020 10:05 PM

All replies

  • Hi,

    I suggest asking over here:
    https://docs.microsoft.com/en-us/answers/topics/office-itpro.html

    https://answers.microsoft.com/en-us/msoffice/forum/msoffice_access

    (Please don't forget to mark helpful replies as answer, thank you)

    Best regards,
    Leon


    Blog: https://thesystemcenterblog.com LinkedIn:

    • Proposed as answer by KHURRAM RAHIM Wednesday, November 25, 2020 6:59 AM
    Monday, November 23, 2020 3:01 PM
  • You could try scrolling through each record on the open form as follows:

    On Error GoTo RecErr
    Dim RecNum As Double
    DoCmd.GoToRecord acActiveDataObject, , acFirst
    RecNum = Me.Count 'Forms Record Count
    For Index = 0 To RecNum
        If IsNumeric([ID]) Then
            DoCmd.OpenQuery "TemporaryTransactions Query7", acViewNormal, acReadOnly 'Runs the query
        End If
        DoCmd.GoToRecord acActiveDataObject, , acNext
    Next
    DoCmd.OpenForm "frmPayeePayorCategoryChecksDep", acNormal, "", "", , acNormal
    DoCmd.Close acForm, "frmPayeePayorCategoryByDescription"
    Exit Sub
    RecErr:
    DoCmd.CancelEvent
    Exit Sub

    Monday, November 23, 2020 7:19 PM
  • Lawrence, thanks for your reply:

    Your code updates all the records and avoids the error message but close the form and open the next form.  The following is a copy of your code.  Could you look it over to see if I entered something incorrectly.  Bob

    Private Sub Form_Load()
    On Error GoTo RecErr

    '    Loop until
    Dim RecNum As Double
    DoCmd.GoToRecord acActiveDataObject, , acFirst
    RecNum = Me.Count 'Forms Record Count
    For Index = 0 To RecNum
        If IsNumeric([ID]) Then
            DoCmd.SetWarnings False
            DoCmd.OpenQuery "TemporaryTransactions Query7", acViewNormal, acReadOnly 'Runs the query
            DoCmd.SetWarnings True
        End If
        DoCmd.GoToRecord acActiveDataObject, , acNext
    Next
    DoCmd.OpenForm "frmPayeePayorCategoryChecksDep", acNormal, "", "", , acNormal
    DoCmd.Close acForm, "frmPayeePayorCategoryByDescription"
    Exit Sub
    RecErr:
    DoCmd.CancelEvent
    Exit Sub

    End Sub

    Monday, November 23, 2020 8:59 PM
  • Lawrence, thanks for your reply:

    Your code updates all the records and avoids the error message but close the form and open the next form.  The following is a copy of your code.  Could you look it over to see if I entered something incorrectly.  Bob

    Private Sub Form_Load()
    On Error GoTo RecErr

    '    Loop until
    Dim RecNum As Double
    DoCmd.GoToRecord acActiveDataObject, , acFirst
    RecNum = Me.Count 'Forms Record Count
    For Index = 0 To RecNum
        If IsNumeric([ID]) Then
            DoCmd.SetWarnings False
            DoCmd.OpenQuery "TemporaryTransactions Query7", acViewNormal, acReadOnly 'Runs the query
            DoCmd.SetWarnings True
        End If
        DoCmd.GoToRecord acActiveDataObject, , acNext
    Next
    DoCmd.OpenForm "frmPayeePayorCategoryChecksDep", acNormal, "", "", , acNormal
    DoCmd.Close acForm, "frmPayeePayorCategoryByDescription"
    Exit Sub
    RecErr:
    DoCmd.CancelEvent
    Exit Sub

    End Sub
    Monday, November 23, 2020 9:00 PM
  • Sorry. After the DoCmd.CancelEvent is when you open and close the forms and then exit:

    RecErr:
    DoCmd.CancelEvent

    DoCmd.OpenForm "frmPayeePayorCategoryChecksDep", acNormal, "", "", , acNormal
    DoCmd.Close acForm, "frmPayeePayorCategoryByDescription"
    Exit Sub

    • Marked as answer by BobAubry Monday, November 23, 2020 10:31 PM
    Monday, November 23, 2020 10:05 PM
  • Perfect, you are a genius. Thank you, Bob
    Monday, November 23, 2020 10:31 PM