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?

 

[Pat Hartman]

You are mis-using the form's BeforeInsert event. The BeforeInsert event is NOT triggered by YOU. It is triggered when the user types the FIRST character into any control in the form. The first character typed into the form triggers the form's on dirty event AND it triggers the form's BeforeInsert event. Therefore, it would be 100% wrong to force Access to save a record in this event because no more than a single character would ever have been typed. With the code you have posted, you seem to be saving the first character typed and then moving to a new record and entering additional values. Have you looked at your table? Do you have records that contain only a single character?

Please step away from the keyboard and tell us exactly what you are trying to do as a use-case, not with code. Also include an explanation of why the normal bound form operation is not suitable.

 

[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

 

[Pat Hartman]

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)

You have been informed (and you have confirmation by a second expert) that you have code in this event which is NOT VALID should that event be triggered by Access. So, instead of arguing, perhaps you should remove the code as MajP suggested. Then you can make the decision as to what you want to happen should the event be triggered naturally by a user typing something in an empty record and what you want to happen should you call the event procedure (which is NOT the same as triggering the event) from a different procedure. Once you separate the two different logic paths, you can still call a common procedure to execute common code. That is the best practice solution. You can't just arbitrarily overload an event procedure by inserting code into it that will ONLY work when the event procedure is "called" by a different procedure and will NEVER work when the event procedure is triggered by Access.

 

[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

 

[Pat Hartman]

If I run it, the Sub Before_Insert is triggered by me, there is no doubt about this

As MajP already pointed out. There is a difference between triggering the BeforeInsert event and running the code in the procedure. I'm not sure why you assume that you know so much more about form events than the experts do. The entire Access form is a class event. There are thousands of lines of code needed to load the form and render it on the screen as well as populate it with data, scroll from record to record and save a dirty record. The form level events for which you provide code are subroutines of the form's Class Module. Think of them as stubs since they may or may not contain any of YOUR code. YOUR code is separate from the code that is part of Access.exe which is the code that makes the whole form work. A bound form will work perfectly without ANY code written by you. Is that magic? NO, it is the code in the form's Class Module making the form work. This is code you can never see or alter. It does what it does without your intervention and whether you want it to run or not. This is the code that makes Access the excellent RAD tool that it is. You seem to want to interfere with that.

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

This makes no sense at all when you know what the BeforeInsert event is intended to be used for.

It makes NO SENSE whatsoever to run the form's BeforeInsert event when a user types a new line in a control. The intent of that form level event is to allow YOU to determine whether or not it is appropriate for the user to actually add a new record at this time. So, I use this event in only three situations.
1. I have security that restricts user actions on forms. So, code in the BeforeUpdate event checks the user's permissions to see if he is even allowed to add records using this form, If he is not, then I undo the typing and cancel the event thereby preventing the user from creating a new record.
2. In a subform, it is necessary that the mainform record has already been added. In older versions of Access, If you bypassed the main form and started typing in the subform, Access would allow it but you would get an error when you tried to save the subform record because there would be no PK assigned for the parent record and therefore, there would be no value available as the FK for the child record. So, in subforms, my code would check the autonumber field on the main form. If it was null, I would undo the typing and cancel the BeforeInsert event and place focus into the first control on the main form. Current versions of Access seem to have fixed this problem and you get the error message immediately rather than at the time of saving.
3. In the case where you use a popup form rather than a subform to enter child records, YOU become responsible for filling in the FK value as each record is inserted. What you don't want to do is write code that causes YOU to dirty the popup form. You want to wait until the user types at least one character thereby committing himself to actually entering a record before you populate the FK. Two of the several ways to do this are to pass in the FK in the OpenArgs parameter when the base form opens the popup -- Me.SomeFK = Me.OpenArgs An alternative is to pull the value from the base form --- Me.SomeFK = Forms!yourbaseform!TheMainFormPK

The code you place into form events is intended to handle some specific need at a pre-determined point in time. You are forgetting that even though you can call the code in an event procedure (which is poor practice as we are trying to explain along with why it is poor practice because if you do not understand why, you can never properly use form events), ACCESS will also call the event code but at a time when ACCESS thinks the code should be called because the event the code is intended to handle has been triggered. That time is after a SINGLE character is typed into ANY form control. The form's class module, when it recognizes the first typed character, calls YOUR code in the BeforeInsert event. Then it calls YOUR code in the form's on Dirty even. Then it calls your code in the control's on Dirty event, etc.

EVERY event (form or control) has a specific use. When you mis-use form and control level events, you will occasionally get away with it but it is ALWAYS more trouble than it is worth. So, maybe you should take the offered advice and separate the logic. The common code can go into a sub in the form and the specific BeforeInsert code can go in the correct event. That avoids ALL confusion AND your errors will disappear as if by magic.

Public Sub cmdAggiungi_Click()
10 On Error GoTo eh_cmdAggiungi_Click

40 Form_BeforeInsert False
50 Me.Dirty = False

Running the BeforeInsert code and immediately following that with a directive to save the current record indicates that you do not understand how or where data should be validated. you cannot validate data in the BeforeInsert event since at most one character has ever been typed AND, besides that, Access has not actually populated the control with that single typed character yet. In the form's BeforeInsert event (when it is run naturally, the control you are checking will ALWAYS be Null. It will Never be anything else. However, when you force the procedure to run at some other point in time, who knows what the state of any control on the form is?

This is a very wrong-headed approach. You will find Access ever so much easier to work with if you accept the fact that it is a RAD tool and you are only providing subroutines at critical logic points so it makes no sense at all to do delete logic in the BeforeInsert event or validation in the Current event just because you could, in your warped understanding of event logic call the procedures from other events. Learn what triggers each of the events so you will have a much better understanding of what code belongs where and you will learn how to actually control Access rather than fight with it.