Dmax question

[MrMitch]

Hi, I've been searching around a bit on the site trying to find an answer, but I believe I will need someone to hold my hand on this one.

I have a table called "WorkOrders". In this table there is a field called "WorkOrder#" which is a 4 digit number that I would like to start at 0001. I want this field on each new record to just add 1, for example, 0001, then the next record 0002, etc. I need this to be contiguous.

These records are added to the table by using a form called "WorkOrders". There is a textbox that generates, and shows the user what the "Work Order ID" is, which is the year (pulled from "Year" field in the table), then the Order #. So for example, 2007-0001, 2007-0002, etc.

I can't seem to figure out how to write the Dmax command or where exactly to put it to get this to work right, and I would also like it to start back at 0001 each year, which I am pretty clueless on getting to work as well.

Hope I explained my issues ok, let me know if you need any more info. Thanks!

 

[orna]

Me!WorkOrderNumb = DMax("[WorkOrderNumb]" , "WorkOrders", "left([WorkOrderNumb],4)='" & YourYear & "'")

You can write it On the exit button/save button

 

[MrMitch]

Ok, I tried that, but anytime I try and launch the form with this, it crashes access.

 

[WayneRyan]

MrMitch,

Use your form's BeforeInsert event:

Me.WorkOrderNumb = Nz(DMax("[WorkOrderNumb]" , "WorkOrders", "[Year] = " & Clng(DatePart("yyyy", Date))), 0) + 1

Lock the field Me.WorkOrderNumb so that the user doesn't mess with it.

hth,
Wayne

 

[MrMitch]

Ok, so I got it work now.

On the form, I have Before Insert as:

Me.[WorkOrder#] = Nz(DMax("[WorkOrder#]", "WorkOrders", "[Year] = " & CLng(DatePart("yyyy", Date))), 0) + 1

So now it will start at 2007-0001, and so on. I just want to make sure that this will reset to 2008-0001 once it gets to that point. Is this correct?

 

[WayneRyan]

Mitch,

Yes, that's right.

When it gets to 2008, the DMax will return a NULL.
The Nz function will convert that to 0 and add one to it.

Wayne