Default next higher number field

In my database form I have to enter data for certain number of parts in a lot. When that lot is finished I have to start again from number 1.

I have to assign a serial number to each part. I want in a field the next higher number to appear as default when I move to new record. I already have an Auto number field in my database.

Please somebody help.

Thank you.

mansoor,

Look at the DMax function to retrieve the highest number
for the desired lot. Add 1 to your result.

I would guess that you should put this in the BeforeInsert
event.

Wayne

Wayne

Thank you very much for your reply. But can you please explain DMax function in a bit more detail. I will be grateful.

Thank you

Mansoor,

Assuming that your form has fields Serial and Lot:

Me.Serial = DMax("[Serial]", "YourTable", "[Lot] = '" & Me.Lot & "'") + 1

hth,
Wayne

Wayne

Thank you for your reply again. I really appreciate your help.

I may sound very thick for you here but actually I am not very good in the advanced features of MS access like writing expression and codes etc.

The solution of my problem you suggested must be the right one but I am a bit confused about where to use this expression. Should I be using as an event procedure or in control source property box.

I explain a bit more of my database. On my form I have a field ‘Line No’ and another field ‘Pallet No’. For example there are 30 parts on a pallet. I enter the pallet no and then I enter Line No =1 for first part’s data. Then I take second part and enter Line No 2 for that. I want in Line No field the next number to appear as default when I move to new record until

-that pallet is finished or
-if I enter any number, next higher number appear as default

I want the same Line Nos to be stored in actual table as well.

I would be very grateful to you if you can suggest the solution.

Thank you for you time.

Mansoor,

You would use the BeforeUpdate event on the Pallet control.
It would be an [Event Procedure] that would have the
DMax function above.

Wayne