Add Record to Subform from Main Form - Error "you can't go to the specified record"

[user9]

I have three forms. All are connected to tblComp.


1. frmMain
2. frmSelect (subform embedded in main form) - read-only as Datasheet
3. frmRecords (popup form) - editable

I'm looking to add a new record from clicking a button on frmMain, which creates a new record in frmRecords with only the primary key (RV_ID) populated. I need the new record to be a new incremental value for RV_ID by finding the highest value of RV_ID in tblComp and adding 1 (presumably using DMax).

I've tried several different methods based on other posts with no luck (getting the error 2105 - "You can't go to the specified record". Below is my current code, any guidance/suggestions would be greatly appreciated.

    DoCmd.OpenForm "frmRecords"
    DoCmd.GoToRecord , , acNewRec
    [RV_ID] = Nz(DMax("RV_ID", "tblComp") + 1)

[ItsMe]

Edit: the acFormAdd makes the most sense

[June7]

The code is behind main form so I think it is trying to move to new record on main form and set value of [RV_ID] field on main form.

Probably easier to open the form in Add mode.

DoCmd.OpenForm "frmRecords", , , , acFormAdd

Then code in the popup form Current event:

If IsNull(Me!RV_ID) Then 'or use If Me.NewRecord
Me!RV_ID = Nz(DMax("RV_ID", "tblComp") + 1)
DoCmd.RunCommand acCmdSaveRecord
End If

Is this a multi-user database?

EDIT: I see ItsMe got a post in just before me. As you can see, more than one way ...

[user9]

The code is behind main form so I think it is trying to move to new record on main form and set value of [RV_ID] field on main form.

Probably easier to open the form in Add mode.

DoCmd.OpenForm "frmRecords", , , , acFormAdd

Then code in the popup form Current event:

If IsNull(Me!RV_ID) Then 'or use If Me.NewRecord
Me!RV_ID = Nz(DMax("RV_ID", "tblComp") + 1)
DoCmd.RunCommand acCmdSaveRecord
End If

Is this a multi-user database?

EDIT: I see ItsMe got a post in just before me. As you can see, more than one way ...

This will initially be a 2-user database, with potential for more down the line. Your code worked, save for the DoCmd on which I got the run-time error 2501 "the RunCommand action was cancelled". I commented this line out and see the record saved on my Save Record click so I should be all set there.

Thanks to both of you for the very quick and knowledgable replies!

[June7]

I added that line because if this is a multi-user database, immediately committing the new ID to table will prevent another user from calculating the same ID.

That is what I do but my code is a bit different. I was hoping this one would work.

[user9]

I added that line because if this is a multi-user database, immediately committing the new ID to table will prevent another user from calculating the same ID.

That is what I do but my code is a bit different. I was hoping this one would work.

In that event, what causes the error I'm getting? I appreciate the extra code to make it more flexible, just not sure why I get that error sometimes on the DoCmd for save.

[ItsMe]

Which Docmd is causing the error? If it is the OpenForm....

Add a line to save the record before opening the new form. So, before your DoCmd.OpenForm "frmRecords", , , , acFormAdd

add
If Me.dirty = true then me.dirty = false

If it is the other Docmd, maybe you are placing a duplicate in a field that does not allow duplicate keys.

[June7]

ItsMe, not sure how that saves record with new RV_ID.

I just re-read the OP which states that all 3 forms are connected to tblComp. Why would frmMain and frmSelect have same dataset?

In my procedure, I use sql action to save record with new ID then open form filtered to that new record.

[ItsMe]

ItsMe, not sure how that saves record with new RV_ID.

It does not. I was simply suggesting to add a line of code that would help to discern where the error occurs. Perhaps the current form is not able to lose focus. If this is the case, it will debug on the line I provided.