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

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

Answer 1

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:

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

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

Answer 6

Perfect, you are a genius. Thank you, Bob