Error 2105 on DoCmd.GoToRecord , , acNewRec -ONLY- on accde code version

[amorosik]

I have a continuous form linked to a DOCUMENTITESTATE table
In the middle above there are textbox-type fields connected to some fields of the table
Below In the body section there are some fields, connected to the data of the DOCUMENTITESTATE table
I use a Command Button to add new records, and the code inside cmdAdd is this:

Public Sub cmdAggiungi_Click()
10        On Error GoTo eh_cmdAggiungi_Click
  
40        Form_BeforeInsert False
50        Me.Dirty = False
60        Form_Current
70        Me.cmbCodice.SetFocus
110       Exit Sub

eh_cmdAggiungi_Click:
120       msg_err err.Number, Erl, Error, "cmdAggiungi_Click of Documento VBA Form_frmDocumenti"
130       Resume Next

End Sub

In the Before_Insert event code there is the actual code that adds the new record

Private Sub Form_BeforeInsert(Cancel As Integer)
10        On Error GoTo eh_Form_BeforeInsert
        
20        If Len(tipo_documento_attuale) = 1 Then
30            ser_doc = InputBox("Inserire la serie documento o premere ENTER per accettare la serie proposta (verra' presa la prima lettere inserita)" & Chr(10) & "(caratteri validi per la 'serie' sono il carattere 'spazio' e tutte le lettere)", "SERIE DOCUMENTO", " ")
40            If Len(ser_doc) < 1 Then Exit Sub
50            If Len(ser_doc) > 1 Then ser_doc = Left$(ser_doc, 1)
60            ser_doc = UCase$(ser_doc)
        
70            If InStr(" ABCDEFGHILMNOPQRSTUVZKJXY", ser_doc) = 0 Then
80                dummy = MsgBox("La SERIE DOCUMENTO deve essere un valore alfabetico compreso tra i seguenti caratteri ABCDEFGHILMNOPQRSTUVZKJXY oppure un carattere SPAZIO", , "SERIE DOCUMENTO NON VALIDA")
90                Exit Sub
100               End If

110           DoCmd.GoToRecord , , acNewRec
120           dummy = dati_testata_record_da_form(Me.Form)
130           Me!ID_DOCUMENTITESTATE = NUOVO_VALORE_DA_GENERATORE_GLOBALE()
140           Me!TIPO_DOCUMENTO = tipo_documento_attuale
150           Me!SERIE_DOCUMENTO = ser_doc
160           Me!DATA_DOCUMENTO = Format$(Now(), "dd/mm/yyyy")
170           Me!ORA_DOCUMENTO = Format$(Now(), "hh:nn:ss")
180           Me!NUMERO_DOCUMENTO = NUOVO_NUMERO_DOCUMENTO(tipo_documento_attuale, ser_doc)
190           Else
200           dummy = MsgBox("Impossibile trovare il TIPO DOCUMENTO da aggiungere", , "TIPO DOCUMENTO NON DEFINITO")
210           End If
        
220       Exit Sub

eh_Form_BeforeInsert:
230       msg_err err.Number, Erl, "Form_BeforeInsert of Documento VBA Form_frmDocumenti"
240       Resume Next
End Sub

The problem is that this code gives me error 2105 on line 110 in correspondence with the DoCmd.GoToRecord , , acNewRec
And this behavior only happens a few times, say once out of 5-10 insertions
And ONLY on the version compiled in accde
If I use that piece of code from accdb version, it never reports any errors even after 100 entries in a row, never
Access 2013, 32bit, Windows10 pro, tables connected via odbc to an external db server physically on another pc on the lan
What could cause the different functioning between accdb and accde versions?
Or what is wrong with the above code?

 

[amorosik]

If I run it, the Sub Before_Insert is triggered by me, there is no doubt about this
If it's easier for you to see everything in one classic routine, the exact same problem occurs if adding the record is done by the code associated with pressing the command button, this was the original code:

Public Sub cmdAggiungi_Click()
10    On Error GoTo eh_cmdAggiungi_Click

110   DoCmd.GoToRecord , , acNewRec
120   dummy = dati_testata_record_da_form(Me.Form)
130   Me!ID_DOCUMENTITESTATE = NUOVO_VALORE_DA_GENERATORE_GLOBALE()
140   Me!TIPO_DOCUMENTO = tipo_documento_attuale
150   Me!SERIE_DOCUMENTO = ser_doc
160   Me!DATA_DOCUMENTO = Format$(Now(), "dd/mm/yyyy")
170   Me!ORA_DOCUMENTO = Format$(Now(), "hh:nn:ss")
180   Me!NUMERO_DOCUMENTO = NUOVO_NUMERO_DOCUMENTO(tipo_documento_attuale, ser_doc)

51    Me.Dirty = False
71    Me.cmbCodice.SetFocus
111   Exit Sub

eh_cmdAggiungi_Click:
121   msg_err err.Number, Erl, Error, "cmdAggiungi_Click of Documento VBA Form_frmDocumenti"
131   Resume Next
      End Sub

Basically the same code
On line 110, a few times, it gives the same error 2105
This ONLY on accde compiled file
If I try using the same file without compiling it, in accdb format, the error NEVER shows up

"..why the normal bound form operation is not suitable.."
??? This IS bound form operations

 

[MajP]

Before_Insert is triggered by me, there is no doubt about this

A little semantics here for clarity. But NO, you cannot "Trigger" or raise that event. You can call the event handler which is simply a procedure. I know what you mean, but it gets confusing since you do not know if people think it is doing something that it is not.
see discussion here.

What You Need to Know About Access Events

There is often a lot of confusion about Access Events and often answering questions posed here is difficult because of inaccurate terminology. People often say "Event" when it is unclear if they mean the actual event that takes place, the event procedure, or the event property. This thread...

www.access-programmers.co.uk

As per that discussion, normally if you want to call a procedure from more than 1 procedurey you would create a stand alone procedure and have
each event procedure call it. This provides more clarity.

Public Sub SomeProcedure
end sub

Then have mulitple event procedures call your procedure "SomeProcedure", instead of having one event procedure call another event procedure.
. 2105 means you cannot move to a new record. I think there are three main causes for this.
1. You are on a new record already and it is not dirty. So check if Not me.NewRecord and Me.Dirty
2. Form is unbound so you cannot go to a new record
3. Form is based on non editable query or me.allowadditions = false

Your design needs to handle all of these cases.

 

[amorosik]

A little semantics here for clarity. But NO, you cannot "Trigger" or raise that event. You can call the event handler which is simply a procedure. I know what you mean, but it gets confusing since you do not know if people think it is doing something that it is not.
see discussion here.

What You Need to Know About Access Events

There is often a lot of confusion about Access Events and often answering questions posed here is difficult because of inaccurate terminology. People often say "Event" when it is unclear if they mean the actual event that takes place, the event procedure, or the event property. This thread...

www.access-programmers.co.uk

As per that discussion, normally if you want to call a procedure from more than 1 procedurey you would create a stand alone procedure and have
each event procedure call it. This provides more clarity.

Public Sub SomeProcedure
end sub

Then have mulitple event procedures call your procedure "SomeProcedure", instead of having one event procedure call another event procedure.
. 2105 means you cannot move to a new record. I think there are three main causes for this.
1. You are on a new record already and it is not dirty. So check if Not me.NewRecord and Me.Dirty
2. Form is unbound so you cannot go to a new record
3. Form is based on non editable query or me.allowadditions = false

Your design needs to handle all of these cases.

Yes of course, I was referring to running a procedure that normally responds to a standard Access event
But the most important thing is the difference between the behavior of the code that runs inside the development environment (I'm referring to the accdb file) and the behavior of the exact same code but compiled (I'm referring to the accde file)
In the first case everything is fine, in the second case and only a few times, it blocks me with error 2105
AllowAddition of the form is true
The form is bound to a modifiable data table
The only thing I can do is to check when the 2105 error comes and try to perform the operations that can recover the possibility to re-execute the command
And so I've already tried to move to the first record, to the last one and retry the record creation, but without success
Currently, the only thing I can do is abort the creation of the new record and allow the operator to try again
But this doesn't seem like the solution to the problem.

 

[MajP]

Try replaicing the Docmd.Gotorecord with
Me.Recordset.AddNew

 

[MajP]

The Docmd code in that case is based on the focus. So whenever using the docmd always specifically specify what you are moving, closing, etc.

Never
Docmd.close (might not close what you think)
instead
Docmd.close, acform, Me.name

Same with
Docmd.Gotorecord, Acform, "frmName",AcNewRec

 

[amorosik]

You have been informed ....

You assume certain things that certain are not
The code inside the Before_Insert event is ALSO used when the operator types the first character on a new line, and this is intended
In addition to this, it seems clear to me that you missed post #3, where a similar example is described with code entirely inside the click event of the cmdAdd command button, which has the exact same problem
Even in the code of a command button, isn't it recommended to put operations to be performed?

 

[The_Doc_Man]

But the most important thing is the difference between the behavior of the code that runs inside the development environment (I'm referring to the accdb file) and the behavior of the exact same code but compiled (I'm referring to the accde file)

There is an implied disconnect in your understanding. The difference between the .ACCDB file and the .ACCDE file is NOT that the .ACCDE file is compiled - because in order to run, the .ACCDB file's code must also be compiled. The difference is that the human-readable text of the VBA instructions is not available in the .ACCDE file. BOTH files have everything compiled into something called pcode. Do not look for a difference in the compilation. There is no difference between the compiled pcode for an .ACCDB and an .ACCDE file - and it is the pcode that is executed. The human-readable text isn't involved during execution. Looking for a difference there will cause you problems and/or headaches.

By the way, your code in post #1 of this thread has another subtle error.

40        Form_BeforeInsert False

The "BeforeInsert" event, when fired naturally, uses call template Form_BeforeInsert( ByRef Cancel as Integer ). You could in theory decide at some point to cancel the BeforeInsert event - which is neither impossible nor even potentially unreasonable. EXCEPT that you gave it the constant "FALSE" as the actual argument of a ByRef formal argument. IF your code does try to cancel the event, one of two bad things could happen.

FIRST, you could get an error for the attempt to modify a constant because that is an illegal memory usage (writing to a read-only constant).

SECOND, you might NOT get an error because the code instead just blithely modified the constant. And then the next time you try to use the constant it ain't constant any more. (I actually believe there are safeguards in place to prevent this, but do you see the illogic of what happens there?) You have programmed the call to potentially overwrite a constant. This is a MAJOR no-no.

Back to the two file types... If there IS any difference between an .ACCDB file and an .ACCDE file, it is this: The .ACCDE file would more strictly enforce code modification restrictions, which means that you would get an error on your manual call if it tries to cancel the event. I.e. that BeforeInsert, if canceled, would likely trigger some kind of error.

 

[amorosik]

Try replaicing the Docmd.Gotorecord with
Me.Recordset.AddNew

Yes, this is one solution of my problem

The Docmd code in that case is based on the focus. So whenever using the docmd always specifically specify what you are moving, closing, etc.

Never
Docmd.close (might not close what you think)
instead
Docmd.close, acform, Me.name

Same with
Docmd.Gotorecord, Acform, "frmName",AcNewRec

And this is second solution
Probably the focus goes away from the form
Thank you very much

 

[amorosik]

There is an implied disconnect in your understanding. The difference between the .ACCDB file and the .ACCDE file is NOT that the .ACCDE file is compiled - because in order to run, the .ACCDB file's code must also be compiled. The difference is that the human-readable text of the VBA instructions is not available in the .ACCDE file. BOTH files have everything compiled into something called pcode. Do not look for a difference in the compilation. There is no difference between the compiled pcode for an .ACCDB and an .ACCDE file - and it is the pcode that is executed. The human-readable text isn't involved during execution. Looking for a difference there will cause you problems and/or headaches.

By the way, your code in post #1 of this thread has another subtle error.

40        Form_BeforeInsert False

The "BeforeInsert" event, when fired naturally, uses call template Form_BeforeInsert( ByRef Cancel as Integer ). You could in theory decide at some point to cancel the BeforeInsert event - which is neither impossible nor even potentially unreasonable. EXCEPT that you gave it the constant "FALSE" as the actual argument of a ByRef formal argument. IF your code does try to cancel the event, one of two bad things could happen.

FIRST, you could get an error for the attempt to modify a constant because that is an illegal memory usage (writing to a read-only constant).

SECOND, you might NOT get an error because the code instead just blithely modified the constant. And then the next time you try to use the constant it ain't constant any more. (I actually believe there are safeguards in place to prevent this, but do you see the illogic of what happens there?) You have programmed the call to potentially overwrite a constant. This is a MAJOR no-no.

Back to the two file types... If there IS any difference between an .ACCDB file and an .ACCDE file, it is this: The .ACCDE file would more strictly enforce code modification restrictions, which means that you would get an error on your manual call if it tries to cancel the event. I.e. that BeforeInsert, if canceled, would likely trigger some kind of error.

Thank you very much
In-depth knowledge of a system obviously allows you to use it better and better
And your considerations are of great help to me