Adding 1 to a field for a new record

[eliotchs]

Hi

I have a form which queries records by clino
it is sorting descending by seqno

something like



clino seqno data..
001 5 dataswedxex
001 4 dataswedxex
001 3 dataswedxex


I have a new record button and I would like the textbox for the seqno to populate(and actually not be able to change) with the next number in sequence.. in the example it would be 6


Thanks
Joe

[ranman256]

in a form, on Form_BeforeInsert event, add 1 to the max count...

[seqno] = dMax("[seqno]","tabl","[clino]='" & txtBoxClino & "'") + 1

[John_G]

You can use the DMax domain function to determine the current maximum SeqNo for that client, then add 1:

me![NewSeqNo] = nz(dmax("seqno", "Tablename","clino = " & me![ClientNo] ), 0) + 1

Change the italics to the actual names of your tables and form controls.

If there are no records yet for that Client, the DMax will return Null, so the Nz function is used to give you 0 instead of null.

You would put this in the code for the button.

To prevent the textbox from being changed, set its Locked property to Yes. (That does not prevent VBA code from setting it).

HTH

John

[eliotchs]

Thanks

That worked great
!!!