BeforeInsert Event

[mlozano]

Hi
I have a form with the following code

Private Sub Form_BeforeInsert(Cancel As Integer)
NUMERO = Nz(DMax("NUMERO", "CAB SOLICITUD")) + 1
End Sub

I need the Before Insert event to set another value to another field, the expression of the value is as follows:

"SP" & "-" & [LINEA NEGOCIO] & "-" & [NUMERO] & "-" & Año(Fecha())

Can anyone help by telling me how to put this in the code? I would appreciate as I am a little lost

Many thanks

 

[mlozano]

Sorry, Año(Fecha()) is actually Year(Date())

 

[gemma-the-husky]

you want form beforeupdate, not before insert

in beforeupdate just put

whateverfield = "SP" & "-" & [LINEA NEGOCIO] & "-" & [NUMERO] & "-" & Año(Fecha())

 

[missinglinq]

You also want your original code

NUMERO = Nz(DMax("NUMERO", "CAB SOLICITUD")) + 1

in the Form_BeforeUpdate event, if this is being used by multiple users.

One of the really big names in Access programming used to recommend using number incrementing code like this in the BeforeInsert event, at the same time saying that it needs to be done just prior to saving the record, which is true. The problem is, the BeforeInsert fires as soon as a single character is entered into any textbox! This means that if one user starts a record, and before he saves it, a second user starts a record, both records will have the same generated number, in this case, the same Numero!

As to placing multiple code in one event, you simply do that, placing the new line of code after the original line of code (because you need the value of Numero before assigning the second value)

Private Sub Form_BeforeUpdate(Cancel As Integer)
 NUMERO = Nz(DMax("NUMERO", "CAB SOLICITUD")) + 1
 whateverfield = "SP" & "-" & [LINEA NEGOCIO] & "-" & [NUMERO] & "-" & Año(Fecha())
End Sub

I always assumed this expert had simply mistyped one time, saved the reply and pasted it in to other threads as needed, something a lot of us who work these forums regularly do. He has since changed his reply and now recommends placing the code in the Form_BeforeUpdate event.

 

[boblarson]

And I'm going to say that NO field should be based on any other field's data. It can be based on an entry in a control (unbound) but one field should not be dependent upon any other field for its value (rules of normalization).

 

[mlozano]

Thanks guys, question solved and more importantly 2 great pieces of advice towards a more solid app!

When does the BeforeUpdate event insert the data in the target field?
a) when the user tabs to the next field? or
b) when the user changes to a different record?

As a newbie in such depths of form building, I very much appreciate your help!

 

[wazz]

"

Private Sub object_BeforeUpdate(Cancel As Integer)
Object The name of a form or a control.
Cancel The setting determines if the BeforeUpdate event occurs. Setting the Cancel argument to True (–1) cancels the BeforeUpdate event.

Remarks

Changing data in a control by using Visual Basic or a macro containing the SetValue action doesn't trigger these events for the control. However, if you then move to another record or save the record, the form's BeforeUpdate event does occur.

...

The BeforeUpdate event is triggered when a control or record is updated. Within a record, changed data in each control is updated when the control loses the focus or when the user presses ENTER or TAB. When the focus leaves the record or if the user clicks Save Record on the Records menu, the entire record is updated, and the data is saved in the database.

"