DMax on existing record to increment serialized # fails

[cwberube]

Hello and happy Cinco De'Mayo!
I have a fairly simple form that uses the BeforeUpdate event to execute thecode below.

----Access 2010----
--------------------------------------------------------
Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me.NewRecord Then
If RecordsetClone.RecordCount = 0 Then
Me.IDNumber = "1"
Else
Me.IDNumber = DMax("val([IDNumber])", "Details") + 1
End If
End If
End Sub
--------------------------------------------------------

That part works perfectly for new records.
What I'm having trouble with is adding the +1 to a pre-existing record.

Working: Fill-out the form, hit the save\close button, the BeforeUpdate event executes and the IDNumber field receives a new # starting at 1 or +1. (This is separate from the Record ID index for the table)

Not Working: Form was already filled out, the record has been saved, the IDNumber is currently blank\NULL.(Different form that doesn't have the BeforeUpdate event) The record is reopened in another form (but using the same data source\table), when attempting to save\close the record the BeforeUpdateevent executes and I receive "Run-Time error '3464': Data type mismatch incriteria expression." Debug points to --- Me.IDNumber = DMax("val([IDNumber])", "Details") + 1 ----

I know from testing that the error is caused by the IDNumber field being blank. Simply by entering the next +1 number into the table and new records can be created again.

The goal is to have the BeforeUpdate event execute on pre-existing records to increment the IDNumber +1 when saved.

I can't wrap my head around how I can increment from NULL to DMAX +1.
Somehow, I need to look for and accept blank\NULL, then look at the latest IDNumber, then DMAX +1 for this record.

I do realize why it fails I just can't figure out a solution to make it work.

Can anyone help me out?
Thank you, Chris.

 

[Bob Quintal]

(e-mail address removed) wrote in

Hello and happy Cinco De'Mayo!
I have a fairly simple form that uses the BeforeUpdate event to
execute the code below.

----Access 2010----
--------------------------------------------------------
Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me.NewRecord Then
If RecordsetClone.RecordCount = 0 Then
Me.IDNumber = "1"
Else
Me.IDNumber = DMax("val([IDNumber])", "Details") + 1
End If
End If
End Sub
--------------------------------------------------------

That part works perfectly for new records.
What I'm having trouble with is adding the +1 to a pre-existing
record.

Working: Fill-out the form, hit the save\close button, the
BeforeUpdate event executes and the IDNumber field receives a new
# starting at 1 or +1. (This is separate from the Record ID index
for the table)

Not Working: Form was already filled out, the record has been
saved, the IDNumber is currently blank\NULL.(Different form that
doesn't have the BeforeUpdate event) The record is reopened in
another form (but using the same data source\table), when
attempting to save\close the record the BeforeUpdate event
executes and I receive "Run-Time error '3464': Data type mismatch
in criteria expression." Debug points to --- Me.IDNumber =
DMax("val([IDNumber])", "Details") + 1 ----

I know from testing that the error is caused by the IDNumber field
being blank. Simply by entering the next +1 number into the table
and new records can be created again.

The goal is to have the BeforeUpdate event execute on pre-existing
records to increment the IDNumber +1 when saved.

I can't wrap my head around how I can increment from NULL to DMAX
+1. Somehow, I need to look for and accept blank\NULL, then look
at the latest IDNumber, then DMAX +1 for this record.

I do realize why it fails I just can't figure out a solution to
make it work.

Can anyone help me out?
Thank you, Chris.

Your problem is a simple type mismatch.
Your field is a text field, but the equation in your code returns a
(double precision) numeric value.

Entering from the keyboard treats the input as a string, the code does
not.
There are two (2) options. The first is to fix the table design, so
that IDNumber is defined as long integer. The second is to add a
function to your code to convert your calxculation to a string:
Me.IDNumber = cStr(DMax("val([IDNumber])", "Details") + 1)