The error 2046: GoToRecord' isn't available now

[Babycat]

Hi Everyone,

I have tried to search before making this post, but none of result solve my issue (I am a newbie and quite new to Access).
My access file has 2 forms: FrmMain (bound) and FrmID (unbound).
- The Frmmain contents the informtions with primary-key: SoBC (ID), it is default form and display most recent record.
- FrmID is kind of data-entry form which is allow user keys in new ID number.

When user opens access, Frmmain is loaded. On the Form_Load event, I check if database contents any data yet, if no data, a msg reminds user to key in first data. The form FrmID is then openned with an auto suggested "ID name".
When OK_btn on the form FrmID is clicked, it searches in database (tblMain) if this new "ID name" is exist or not, and performs following code:

Code Tags Added by UG

            CalculatedStr = Trim(Forms!FrmID!Txt_NewSBC)
                          
            ConditionStr = "[SoBC]= '" & CalculatedStr & "'"
            SBCvalue = DLookup("SoBC", "tblMain", ConditionStr)
          
            If Not IsNull(SBCvalue) Then
                MsgBox "This ID number is already exist!"
                Txt_NewSBC.SetFocus
              
            Else
                DoCmd.Close acForm, "FrmID"
                             
                rs.AddNew
                rs!SoBC = CalculatedStr
                rs.Update
                                                       
                Application.Echo False  'turn-off screen for a while in order preventing the form flashes to first record
                Forms!FrmMain.Requery
                DoCmd.GoToRecord acDataForm, "FrmMain", acLast      'the error 2046 orcurs here
                Application.Echo True  'turn-on screen

            End If

Please help to get out this point since I am already stuck for a day.

I have attached the project file, to see to problem, delete all record and open the form FrmMain.

Best regards, Xuan

 

[MajP]

Works fine for me. It does what you describe without failing.

 

[Babycat]

Works fine for me. It does what you describe without failing.

Hi MajP. Have you deleted all record, close FrmMain and reopen it?

 

[June7]

I did and yes get error and Access freezes. Cannot get right click menu nor click ribbon.

Set focus back to FrmMain first:

Forms!FrmMain.SetFocus

Not a fan of setting Caption property in table. I prefer to see real field name when viewing table. I seldom set formatting in table.

 

[MajP]

I did delete all the records, but could not recreate the error. The reason is this error, "Is not available now" is very focused related, and will only have if you use docmd code. If you use a recordset move last it would not happen. One way to tell when it is going to happen is if you look at the ribbon and that action is greyed out. It will be greyed out depending on what other action you are doing and what has focus. So basically you are trying to run a menu action that cannot be run because it is greyed out. Therefore setting the focus to the correct place fixes it (if the action is no longer greyed out). In my case it worked because I had the focus in a working location.

 

[Babycat]

I did and yes get error and Access freezes. Cannot get right click menu nor click ribbon.

Set focus back to FrmMain first:

Forms!FrmMain.SetFocus

Not a fan of setting Caption property in table. I prefer to see real field name when viewing table. I seldom set formatting in table.

Dear June7.

It works, I am so happy and much appreciated your helps.
As a beginner, I am playing around table properties...

Yes, set focus to form FrmMain after Docmd close FrmID. Then It works
DoCmd.Close acForm, "FrmID"
Forms!FrmMain.SetFocus

But set focus to form FrmMain BEFORE Docmd close FrmID. Then It doesn't works. Just curiously.

 

[MajP]

As I said this is Docmd specific
So try replacing
DoCmd.GoToRecord acDataForm, "FrmMain", acLast 'the error 2046 orcurs here
with
Me.Recordset.MoveLast

 

[June7]

I suppose because focus is returned to FrmID when Close command executes.

 

[Babycat]

As I said this is Docmd specific
So try replacing
DoCmd.GoToRecord acDataForm, "FrmMain", acLast 'the error 2046 orcurs here
with
Me.Recordset.MoveLast

MajP, you need to deleted all record, close FrmMain and reopen it. The error will occur...
I will study about Recordset.MoveLast method, however I tried your suggestion, unfortunately It still got error. Can try at your side?

Code Tags Added by UG

DoCmd.Close acForm, "FrmID"
                Forms!FrmMain.SetFocus
             
                rs.AddNew
                rs!SoBC = CalculatedStr
                rs.Update
                                                      
                Application.Echo False  'turn-off screen for a while in order preventing the form flashes to first record
                Forms!FrmMain.Requery
                ' DoCmd.GoToRecord acDataForm, "FrmMain", acLast     ' Comment out
                Me.Recordset.MoveLast                                                    ' Try this
                Application.Echo True  'turn-on screen

Regards, Xuan

 

[Babycat]

I suppose because focus is returned to FrmID when Close command executes.

Yeah, I understand and think so. Have a good day, brother!

 

[MajP]

There was a lot of other issues.

 

[Babycat]

There was a lot of other issues.

Dear MajP,

Appreciated your time and helps !

I have realized following things:

1. You changed db, rs to local variable. Is that for computer RAM saving?
2. Use Recordset.MoveLast instead of DoCmd.GoToRecord. Yes, I am good with this recommendation.
3. When user click OK, i want FrmMain showing latest record (just created). Your code with rs.Movelast seems not making it (probably FrmMain.Requery re-points to 1st record), what is your purpose of using "rs.Movelast"?
4. I have changed the code a bit as attached photo. Should be OK, right?

 

[MajP]

1. You changed db, rs to local variable. Is that for computer RAM saving?

Not for RAM but for scope and encapsulation. For what I could see all your events opened a recordset and then finished using it, the other events were not "reusing" the same recordset. Therefore there is no reason to leave it open. If it is local it goes out of scope when done and the recordset closes. The way you had it would be considered sloppy programming and not very error proof.

3. When user click OK, i want FrmMain showing latest record (just created). Your code with rs.Movelast seems not making it (probably FrmMain.Requery re-points to 1st record), what is your purpose of using "rs.Movelast"?

Sorry that should be
Forms!frmmain.Recordset.MoveLast
I thought RS was pointing to the actual mainform recordset, but it is basically a clone.

This also answers the original problem. Since your focus was going to frmID which is unbound the docmd.Gotorecord "is not available now". Like I said you can prove this. Set focus on frmID and look at the ribbon. Goto is greyed out.

 

[Babycat]

Sorry that should be
Forms!frmmain.Recordset.MoveLast
I thought RS was pointing to the actual mainform recordset, but it is basically a clone.

Oh, in that case, RS shoud be something like: "Set rs = Froms!FrmMain.Recordset", Am I correct?

This also answers the original problem. Since your focus was going to frmID which is unbound the docmd.Gotorecord "is not available now". Like I said you can prove this. Set focus on frmID and look at the ribbon. Goto is greyed out.

Sadly, I could not make it, or I might misunderstood. Attached photo, the form frmID got focus and active, but "goto, create new..." on mainform are not grey out.

 

[arnelgp]

hello, I made changes to your db on post #1.
see if this is the behavior you want.
chk the code of FrmMain and FrmID.

 

[Babycat]

Hi Arnelgp.

I have learnt good technique from your code
However, its behavior is not correct when I click Add-new button which is also trigger the form FrmID.

Thank for your help and much appreciated

 

[arnelgp]

ok, for now I removed the macro for the Add New Record.
I created a function in frmMain, udfNewRecord().
move the code that create new record to this function so that
opening the form without record and adding new record will
call on Same function.

 

[Babycat]

ok, for now I removed the macro for the Add New Record.
I created a function in frmMain, udfNewRecord().
move the code that create new record to this function so that
opening the form without record and adding new record will
call on Same function.

Thank you. It works perfect now

 

[arnelgp]

keep safe!

 

[Babycat]

Dear Arnelgp

Thank for your your help.
I have a question thats not so related to topic, but as a new with access I am so much confused btw regular recordset rs ( Set rs = db.OpenRecordset("SELECT * FROM mytablename) and Form's recordset: Me.Recordset (let assumed this form link to mytablename).
Are they same?
Seems method rs.movenext won't make the form shows next record while Recordset .movenext does.
Sorry for those question, but they really confuse me.