Selecting the (consecutive??) max value + 1 for a new field

[Guest]

I've got a very simple form that lists SerialNo., ModemNo and UniqueNo (and
an autonumber index field).
This lists historical data where the unique number is unique and assending,
but at the moment for any new records I've got to manually type the next
consecutive number and it's driving me nuts since I'm adding a couple of 100
per day!!!
is these a piece of code that says look at the maximum value for the
field(uniqueNo), add 1 to it and then have that as your default value for the
next record - i.e. the next consecutive number???!?!

PLEASE HELP!!!

 

[Andi Mayer]

I've got a very simple form that lists SerialNo., ModemNo and UniqueNo (and
an autonumber index field).
This lists historical data where the unique number is unique and assending,
but at the moment for any new records I've got to manually type the next
consecutive number and it's driving me nuts since I'm adding a couple of 100
per day!!!
is these a piece of code that says look at the maximum value for the
field(uniqueNo), add 1 to it and then have that as your default value for the
next record - i.e. the next consecutive number???!?!

PLEASE HELP!!!

look at DMax() in the helpfile

 

[Guest]

cheers Andi, that sorted it, although interestingly only in a single record
form. if you set your form to continuous records it returned the same
value...

 

[Andi Mayer]

cheers Andi, that sorted it, although interestingly only in a single record
form. if you set your form to continuous records it returned the same
value...

Thats the purpose of DMax("MyField","Mytable")

It give you the biggest number in MyTable in the the Field MyField

myNewNumber=DMax("MyField","Mytable")+1

Have you set all your new numbers only to dMax?

then it will find the same as Dmax and you have a lot of Dmax in your
MyField

 

[Rick Brandt]

cheers Andi, that sorted it, although interestingly only in a single
record form. if you set your form to continuous records it returned
the same value...

You canot use the DMax()+1 strategy as the default value in a continuous
form or in an app with multiple users. You have to calculate and assign the
value in the form's BeforeUpdate event.

The reason to use that event is because the record is saved immediately
after the value is calculated reducing the odds that duplicate values will
be generated. BeforeUpdate is the only event where that is true. Since it
can fire multiple times per record you need to add an If-Then block so it
only assigns a value to records that do not already have one.

 

[Guest]

i have a datasheet subform i call 'DaysView' which is sitting on a mainform
called 'Screening Log (Edit Only)'.

the idea is that the mainform has patient information on people who were
screened for interest in joining a certain clinical trial and that the
subform has information about what future visits may have been scheduled.
that's the short explanation for the interdependence of the two and will
probably suffice to segue into the cunundrum's description....

i am looking for a way to sequentially number each visit in the subform, so
i whipped up the following which i placed into the 'Visit #' field's 'Default
Value' property:

=Nz(DMax("[RecordNumber]","[DaysView]","[Last Name] = " & [Me]![Last Name] &
"[First Name] = " & [Me]![First Name] & "[MI] = " & [Me]![MI] & "[MR_Number]
= " & [Me]![MR_Number] & "[IRB Number] = " & [Me]![IRB Number]),0)+1


well wouldn't you know it but it doesn't do too much more than just display
"#Name?" (w/o the quotations) in the 'Visit #' control on the datasheet.

is your theory relevant here.

before adding the criteria portion to the above, i used it w/o any criterion
and it correctly added the maximum value -- BUT at the same time, it added
the same maximum value to every subform's records :-(

any thoughts would be welcome,

-ted