Sporadic Problems adding new records from for in VBA - driving me crazy (short drive)

[Ira]

Hi Guys,



Have an Access 365 database running Widows 10 Pro. I have had a problem for many months now and tried many different approaches to solve it with no luck.
This happens on many of my bound forms and it happens sporadically. Sometimes I can add 10 new clients with no issue and then all of a sudden...
"Error 2046 The command or action 'RecordsGotoNew' isn't available now.

Some of the forms have a subform, some do not. Some of the forms have a listbox for navigation, some do not.

Below you can see my code and a couple of the methods I have tried. Both of these methods are run by a button push on the Clients form.
Method 1 is what I currently use and when it fails, I just exit the database and then re-enter and it usually works the next time around.
Method 2 always works, but I can't seem to find a way for it to put me on the newly created record when I am in the form.


'method 1
Me.AllowAdditions = True 'make sure form allows additions
If Me.AllowAdditions = True Then 'double check
DoCmd.RunCommand acCmdRecordsGoToNew
Else
MsgBox "Problem Allow Additions still not True 'make sure allow additions is true"
End If

'method2
Me.AllowAdditions = True 'make sure form allows additions
Set db = CurrentDb
sSQL = "INSERT INTO Clients (CreatedBy, CreatedOn) VALUES ('Ira',#1/1/2022#)"
db.Execute sSQL, dbFailOnError
If db.RecordsAffected = 0 Then
MsgBox "No New records were created by the above query?"
End If

Me.Requery
DoCmd.RunCommand acCmdRecordsGoToLast 'DOES NOT WORK Never gets you to the newly created record

Thank you,

Ira

[orange]

For clarity,
is this a split database?
Is it multi-user"

Not obvious to me why this doesn't work, unless there is some constraint.

Set db = CurrentDb
    sSQL = "INSERT INTO Clients (CreatedBy, CreatedOn) VALUES ('Ira',#1/1/2022#)"
    db.Execute sSQL, dbFailOnError

[Welshgasman]

Would
DoCmd.GoToRecord , , acLast
work?

Anything like CreatedBy/Amended By etc, I would set in the form's before update event?

[Ira]

This a multi-user, split database. The error still happens even with just one user in the database.

For method 2, DoCmd.GoToRecord , , acLast does not work. It brings me to the last record (like "Zara"), instead of the new record I just created. If I can figure out how to go the newly created record (from Insert Into), I would switch to Method 2.

[orange]

You say you're using A365 and Win 10 pro. You should update your profile to reflect these.

I saw a couple of posts/articles where the issue was caused by:

-had AllowAdditions = False

The solution to my problem was a simple one: and kind of a rookie mistake on my part. I was not setting the focus to the form I was wanting to manipulate Final code:

Me.fAdminToolsDetails.Form.sfAdminToolsYearly.Form.SetFocus
DoCmd.RunCommand acCmdRecordsGoToNew

[Ira]

I updated my profile, thanks.
AllowAdditions is definitely set to True, I do a double-check before creating the new record.
Focus is on the correct form.
Still no luck.
Thanks

[Welshgasman]

acLast will take you to the last record ACCORDING to how the records are sorted when sent to the form?
if you order by autonumber field and have it set as increment (default) then you should see your last entered record?

From what you have now added it appears you are sorting on firstname or lastname?

[Ira]

Ok, that helps. So I set the sort order to the ClientID (autoincrement), then use acLast and then I set the sort order back. Not as fast or as elegant as just using acCmdRecordsGoToNew, but it works.
Just tried using acCmdRecordsGoToNew again and it failed! So at least I have a solution for now.
Thank you!

[Welshgasman]

Do not requery, then you will see the record you just added?

Edit, sorry, thought you were bound. Why is it not bound?
There is a method to find the pk of the newly created record and go to that. Have a google

[Welshgasman]

Found it
https://www.devhut.net/access-retrie...pon-insertion/