By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,835 Members | 1,391 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,835 IT Pros & Developers. It's quick & easy.

Incrementing a maximum value of a field when adding a record

P: n/a
I need to increment the maximum value of a field of a table by 1 when a
form opens a blank record. (e.g.
[trip_number]=max([tripmain]![trip_number}) + 1)
I can show the maximum record in a text box on the form using
=max([tripmain]![trip_number})
so the logic and references are working at the form level.

I've tried a number of variants tied to the before update event, but
always get the message "The field 'tripmain.trip_number' can't contain a
Null value because the Required property for this field is set to True.
Enter a value in this field."

The table in question is TripMain.
The field trip_number is the primary key (no duplicates)
The parent form is frmTripMain
It has a one-to-many relationship with a subform "trip detail" using the
trip_number field

I've reviewed other threads with similar requests and can't seem to make
them work in this situation. I'd appreciate any suggestions and code
samples for making this work.

Thanks in advance for your suggestions and knowledge! --Ken

Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Try the DMax() function instead of Max.

--
Wayne Morgan
MS Access MVP
"K. Davis" <kd**********@sbcglobal.net> wrote in message
news:72****************@newssvr11.news.prodigy.com ...
I need to increment the maximum value of a field of a table by 1 when a
form opens a blank record. (e.g.
[trip_number]=max([tripmain]![trip_number}) + 1)
I can show the maximum record in a text box on the form using
=max([tripmain]![trip_number})
so the logic and references are working at the form level.

I've tried a number of variants tied to the before update event, but
always get the message "The field 'tripmain.trip_number' can't contain a
Null value because the Required property for this field is set to True.
Enter a value in this field."

The table in question is TripMain.
The field trip_number is the primary key (no duplicates)
The parent form is frmTripMain
It has a one-to-many relationship with a subform "trip detail" using the
trip_number field

I've reviewed other threads with similar requests and can't seem to make
them work in this situation. I'd appreciate any suggestions and code
samples for making this work.

Thanks in advance for your suggestions and knowledge! --Ken

Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.