Event Procedure Natural Key

[Zanstemic]

I'm currently setting the following value in a form field by placing it in
the default value.

=Nz(DMax(" [AutoNumber]","Attendees","[Date Received] = Date()"),0)+1

I need to have this in the Before Update event and not sure about the syntax.

I'm new to VB so any help is appreciated.

 

[Zanstemic]

Here is the sample code that is in an early stage. I'm not fully
understanding all the syntax (ie. db.Execute, Insert Into etc. Values,
returning value to the calling procedure)

The intention is to set this number and populate a table in the BeforeUpdate
Event prior to open a form.

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim jetSQL As Integer
Dim AutoNumber as Integer

' pick a number to start at
AutoNumber = Nz(DMax(" [AutoNumber]", "Attendees", "[Date Received] =
Date()"), 0) + 1

Do While True
jetSQL = "INSERT INTO etc VALUES (" & [AutoNumber]);"
db.Execute jetSQL, dbFailOnError


If Err.Number <> 0 Then
' insert failed, someone else must have grabbed the
' same number just before us
[AutoNumber = [AutoNumber] + 1

Else
' okay it worked, this number now belongs to us
Exit Do
End If
Loop

' return txtAutoNumber to the calling procedure
AutoNumber = AutoNumber
End Sub

 

[John W. Vinson]

I'm currently setting the following value in a form field by placing it in
the default value.

=Nz(DMax(" [AutoNumber]","Attendees","[Date Received] = Date()"),0)+1

I need to have this in the Before Update event and not sure about the syntax.

I'm new to VB so any help is appreciated.

Well, just don't use an actual Autonumber datatype for the field - use a Long
Integer.

And don't use the Default Value property. Instead, use the form's BeforeInsert
event:

Private Sub Form_BeforeInsert(Cancesl as Integer)
Me![AutoNumber] = NZ(DMax("[AutoNumber]", "[Attendees]", _
"[Date Received] = Date())) + 1
Me.Dirty = False ' write the record to disk immediately
End Sub

John W. Vinson [MVP]

 

[Zanstemic]

John,
I've started a new thread called "Event Procedure" with some code that
should increment the number. I really appreciate the help, I have to
understand how to meet the validation rules before the Me.Dirty will work.

I ran some tests without the validation rules and it works well. Thanks for
the help.

I'm currently setting the following value in a form field by placing it in
the default value.

=Nz(DMax(" [AutoNumber]","Attendees","[Date Received] = Date()"),0)+1

I need to have this in the Before Update event and not sure about the syntax.

I'm new to VB so any help is appreciated.

Well, just don't use an actual Autonumber datatype for the field - use a Long
Integer.

And don't use the Default Value property. Instead, use the form's BeforeInsert
event:

Private Sub Form_BeforeInsert(Cancesl as Integer)
Me![AutoNumber] = NZ(DMax("[AutoNumber]", "[Attendees]", _
"[Date Received] = Date())) + 1
Me.Dirty = False ' write the record to disk immediately
End Sub

John W. Vinson [MVP]