Expression builder: for newest date and for newest time, then dmax number +1

[ssgagosto]

I've got a form that is used to enter specific data. The first box is a number associated with the total record. I already have an autonumber formatted ID column that is unique per record, but the record number is formatted as a number and allows duplicates. The validation range for it is ">=1 and <=9999" and we will be cycling through the 1-9999 number many times in the same table.

Right now, I have the default value for the text box to display =DLast("[tgtnum]","targets")+1 which looks up the last number and adds one so the user shouldn't have to enter in the number (most times). However, once and a great while, a number will get skipped or put in a reverse order when doing data entry (i.e. the next input number should be 100, but they put in 112, then enter 111, making the default value formula above 112, not 113 like it should be). Also, within the same record is a date field and a time field.

I need to somehow make an expression that goes something like this:

for the greatest time (of field time of table1) of the greatest date (of field date of table1), then dmax field number and add 1

Is this a little extreme?

Would a query be easier to build based off of the table, then reference the query in the expression builder for default value?

Thanks in advance to anyone that can help and let me know what else may help.

 

[Galaxiom]

Tables are unordered lists. First and Last are not meaningful ways to order records.
Also you cannot rely on DMax() + 1 to provide a complete sequence in a multiuser environment.

Max(Timefield) of the Max(Datefield) can be returned by simply adding the fields together first. Make a query with a derived field with the expression:
DateField + TimeField
Then return the Max from that field.

To make a reliable sequence, particularly in a multiuser environment, the next number is stored and updated in a separate table. When the next number is required the record is locked, the number read and updated before other users are allowed to access it.

 

[ssgagosto]

I realize what you're saying and understand.

I was playing around with a query that reference the table that my form source derives from, and takes the max of date, max of time and takes the tgtnum+1 and give me 3 fields showing one date, time and number(+1), but since the form source comes from the targets table, and the text box field control source is tgtnum from the target table and validation rule looks up another field in the targets table, but is there any way to make the default value lookup from a query?

I just started playing with access about a week ago, so I'm fresh meet.

 

[ssgagosto]

After playing around a bit, I ended up getting it.

I used a query looking at max date, max time and number+1, then used dlookup to display the number+1 value.

I seems to be exactly what I need.

Thanks for steering me in the right direction.

I just started to learn access a week ago.

Thanks!