Default next higher number field

[Mansoor Ahmad]

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.

 

[WayneRyan]

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

 

[Mansoor Ahmad]

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

 

[WayneRyan]

Mansoor,

Assuming that your form has fields Serial and Lot:

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

hth,
Wayne

 

[Mansoor Ahmad]

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.

 

[WayneRyan]

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