Run-Time error '2105' You can't go to the specified record.

[MHamid]

Hello,

I am using the below code for required fields in the form's Before Update event.

For Each ctl In Me.Controls
    If ctl.ControlType = acTextBox Then
        If ctl.Tag = "*" And Trim(ctl & "") = "" Then
            msg = "Data Required for '" & ctl.Name & "' field!" & nl & _
            "You can't save this record until this data is provided!" & nl & _
            "Enter the data and try again . . . "
            Style = vbCritical + vbOKOnly
            Title = "Required Data..."
            MsgBox msg, Style, Title
            ctl.SetFocus
            Cancel = True
            Exit For
        End If
    End If
Next

When I click on my Add Record Button I get the error message "Run-Time error '2105' You can't go to the specified record." and in my AddRecord button code the go to new record line is highlighted (in red text elow).

Private Sub AddRecord_Click()
Dim ID As Long
 
ID = DMax("RefID", "QAMaster")
[COLOR=#FF0000]DoCmd.GoToRecord , , acNewRec[/COLOR]
Me.Approved = DLookup("ApprovedBy", "QAMaster", "RefID=" & ID)
Me.CycleMonth = DLookup("CycleMonth", "QAMaster", "RefID=" & ID)
Me.DateReviewed = DLookup("DateReviewed", "QAMaster", "RefID=" & ID)
Me.ReportType = DLookup("ReportType", "QAMaster", "RefID=" & ID)
Me.MainSection = DLookup("MainSection", "QAMaster", "RefID=" & ID)
Me.TopicSection = DLookup("TopicSection", "QAMaster", "RefID=" & ID)
Me.ReviewerType = DLookup("ReviewerType", "QAMaster", "RefID=" & ID)
Me.Reviewer = DLookup("Reviewer", "QAMaster", "RefID=" & ID)
Me.ReviewerReportArea = DLookup("ReviewerReportArea", "QAMaster", "RefID=" & ID)
Me.Individual = DLookup("OwnershipIndividual", "QAMaster", "RefID=" & ID)
Me.Team = DLookup("OwnershipTeam", "QAMaster", "RefID=" & ID)

Me.EnteredBy = Environ("USERNAME")
Me.DateEntered = Format(DateValue(Now()), "Short Date")
Me.txtCount.SetFocus
 
Me.txtOther.Visible = False
Me.txtOther2.Visible = False
End Sub

I need the required field code to ensure all required fields are filled. Then I want it to go to the blank field and not add the record until all fields are filled.


Thank you

 

[xenou]

Not sure what you are asking here -- required field code in a before update event should have nothing to do with going to a new record (new records by definition won't be anything that is being updated because they are new records, not records being saved).

 

[MHamid]

Hello,

I see my mistake. The record was already in a new record, which is why it was erring out in the new record portion of the code. I corrected this to:

Private Sub AddRecord_Click()
Dim ID As Long
 
    ID = DMax("RefID", "QAMaster")
[COLOR=#FF0000]If Not Me.NewRecord Then
    DoCmd.GoToRecord , , acNewRec
End If[/COLOR]
    Me.Approved = DLookup("ApprovedBy", "QAMaster", "RefID=" & ID)
    Me.CycleMonth = DLookup("CycleMonth", "QAMaster", "RefID=" & ID)
    Me.DateReviewed = DLookup("DateReviewed", "QAMaster", "RefID=" & ID)
    Me.ReportType = DLookup("ReportType", "QAMaster", "RefID=" & ID)
    Me.MainSection = DLookup("MainSection", "QAMaster", "RefID=" & ID)
    Me.TopicSection = DLookup("TopicSection", "QAMaster", "RefID=" & ID)
    Me.ReviewerType = DLookup("ReviewerType", "QAMaster", "RefID=" & ID)
    Me.Reviewer = DLookup("Reviewer", "QAMaster", "RefID=" & ID)
    Me.ReviewerReportArea = DLookup("ReviewerReportArea", "QAMaster", "RefID=" & ID)
    Me.Individual = DLookup("OwnershipIndividual", "QAMaster", "RefID=" & ID)
    Me.Team = DLookup("OwnershipTeam", "QAMaster", "RefID=" & ID)

    Me.EnteredBy = Environ("USERNAME")
    Me.DateEntered = Format(DateValue(Now()), "Short Date")
    Me.txtCount.SetFocus
 
    Me.txtOther.Visible = False
    Me.txtOther2.Visible = False
End Sub

Now I'm not getting the error message anymore. However, the required fields code is not ensuring all required fields are filled. I will do a new post for this issue.


Thank you

 

[xenou]

okay. make sure you have the asterisk in the tag property of the required fields you want to validate.

 

[MHamid]

The asterisk are in the tag property of the required field.

I posted a new thread with the code and some additional detail: https://www.mrexcel.com/forum/micro...ll-adding-record-missing-required-fields.html