VBA - Dmax Criteria or work around

[Brent]

Creating a sequencial membership number based on previous record using DMax.
While the code works great, I need to protect the records with membership
numbers already assigned in that the code is executed upon "Click". Tried
criteria portion of DMAX but always returns a DataType error.......The field
is Number. Code: [mem num]=dmax("[mem num]","memberlist") + 1 ----this
works, but
--[mem num]=dmax("[mem num]","memlist",[mem num]![memlist] is null
results in a "type mismatch"......mem num is set as a number in properties.

Please Help ...... Same error when attemping the "if" statement

Brent

 

[Marshall Barton]

Creating a sequencial membership number based on previous record using DMax.
While the code works great, I need to protect the records with membership
numbers already assigned in that the code is executed upon "Click". Tried
criteria portion of DMAX but always returns a DataType error.......The field
is Number. Code: [mem num]=dmax("[mem num]","memberlist") + 1 ----this
works, but
--[mem num]=dmax("[mem num]","memlist",[mem num]![memlist] is null
results in a "type mismatch"......mem num is set as a number in properties.

What you tried doedn't make sense to me. What are you
trying to accomplish by using aa criteria?

 

[Jeanette Cunningham]

To protect membership numbers already assigned, lock the control with the
membership number.

Only run the code for the next membership number if the form is on a new
record and if the membership number is null.
Use your code:
Code: [mem num]=dmax("[mem num]","memberlist") + 1


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Brent]

Jeanette,

Thank you for responding. Yes the code you wrote does work. The problem is
that it works ....."On CLICK".... in order to "Click" the "Mem num" field, it
can't be locked.
This is a data entry form where existing records can be viewed if you
navagate to them. If you click on the "Mem num" field of an existing records
it will be "updated" with a new "Mem Num".

The thought of using "criteria" was to specifiy that only records where the
"Mem Num" was blank or null or < 0 ....anything but properly populated would
be assigned.

I still don't understand why after successfully adding "1" to a field, I get
a Data type error when attempting to evaluate the content as numberic or
null? This field is a primary key if that makes a difference.

Thanks again ...... I hope there's clarity there.
--
Brent

To protect membership numbers already assigned, lock the control with the
membership number.

Only run the code for the next membership number if the form is on a new
record and if the membership number is null.
Use your code:
Code: [mem num]=dmax("[mem num]","memberlist") + 1


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Creating a sequencial membership number based on previous record using
DMax.
While the code works great, I need to protect the records with membership
numbers already assigned in that the code is executed upon "Click". Tried
criteria portion of DMAX but always returns a DataType error.......The
field
is Number. Code: [mem num]=dmax("[mem num]","memberlist") + 1 ----this
works, but
--[mem num]=dmax("[mem num]","memlist",[mem num]![memlist] is null
results in a "type mismatch"......mem num is set as a number in
properties.

Please Help ...... Same error when attemping the "if" statement

Brent

.

 

[Jeanette Cunningham]

The primary key can't be null.

I assume that [mem num] is a long integer primay key and not an auto number
primary key.

I suggest that instead of using criteria for blank or null, you use the test
for NewRecord instead.

If Me.NewRecord Then
[mem num]=dmax("[mem num]","memberlist") + 1
Else
'put here the code to view existing records
End If


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Jeanette,

Thank you for responding. Yes the code you wrote does work. The problem
is
that it works ....."On CLICK".... in order to "Click" the "Mem num" field,
it
can't be locked.
This is a data entry form where existing records can be viewed if you
navagate to them. If you click on the "Mem num" field of an existing
records
it will be "updated" with a new "Mem Num".

The thought of using "criteria" was to specifiy that only records where
the
"Mem Num" was blank or null or < 0 ....anything but properly populated
would
be assigned.

I still don't understand why after successfully adding "1" to a field, I
get
a Data type error when attempting to evaluate the content as numberic or
null? This field is a primary key if that makes a difference.

Thanks again ...... I hope there's clarity there.
--
Brent

To protect membership numbers already assigned, lock the control with the
membership number.

Only run the code for the next membership number if the form is on a new
record and if the membership number is null.
Use your code:
Code: [mem num]=dmax("[mem num]","memberlist") + 1


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Creating a sequencial membership number based on previous record using
DMax.
While the code works great, I need to protect the records with
membership
numbers already assigned in that the code is executed upon "Click".
Tried
criteria portion of DMAX but always returns a DataType error.......The
field
is Number. Code: [mem num]=dmax("[mem num]","memberlist") +
1 ----this
works, but
--[mem num]=dmax("[mem num]","memlist",[mem num]![memlist] is null
results in a "type mismatch"......mem num is set as a number in
properties.

Please Help ...... Same error when attemping the "if" statement

Brent

.