Enter new number in sequence in PO

[tchirpich]

I have a po form that I have in access 2007 and I am having trouble getting the right
verbage to get to the next number,
my po number are as follows, 10191,10192,10193,etc.
When I put in the formula that you have on the website I get a error in that field.
My table field for that stored number is PO Number. Is it possible that you can edit the
formula so that it will work for me. Also the table name is Purchase Orders.
I can send you the more information if needed.
Nz(DMax("NumberField", "TableName", "DateField = " & Year(Date())), 0) + 1



Thank You,
Tim Chirpich

[alansidman]

Try this:

NZ(Dmax("NumberField","TableName")+1,0)

[Robeen]

What is the error you are getting?

I created a TextBox [txtMax_Number] on a Form and in the Form Load event of the Form, I put this code:

 
Private Sub Form_Load()
 
Me.txtMax_Number.SetFocus
Me.txtMax_Number.Text = Nz(DMax("NumberField", "[TableName]", "Year([DateField]) = " & Year(Date)), 0) + 1
 
End Sub

You'll notice that I put the Year function for the DateField ["Year([DateField]) = . . .]

The above code gave me the next highest number.

Let me know if this helps!

[tchirpich]

Try this:

NZ(Dmax("NumberField","TableName")+1,0)

Do I have to change the number field and table name to what I have in my Database??????

[alansidman]

Absolutely. I just used what you provided.

BTW: I put it as a default value in the form property for the text box control.

[tchirpich]

I still get a error when I use this formula, something I am doing is not right.
NZ(DMax("PO Number","Purchase Orders")+1,0)?????
It still gives me error in that field when I open up the Purchase order form.

[RayMilhon]

Since you have spaces in your tablename and Numberfield don't forget the [] around them. Look at Robeens sample

[tchirpich]

Absolutely. I just used what you provided.

BTW: I put it as a default value in the form property for the text box control.

Nothing is working for me If I send you my database can you get it to work for me??

[Robeen]

Put your db out here. One of us will look at it.

[tchirpich]

Put your db out here. One of us will look at it.

It will not let me load it because it is to large 2.38MB
any other ideas???

[Robeen]

You'll have to zip it.

[tchirpich]

You'll have to zip it.

Here it is.
Purchase order zip

Please help

[Robeen]

Where are you trying to put that formula in?

And what are you trying to do with that formula?

[tchirpich]

Where are you trying to put that formula in?

And what are you trying to do with that formula?

Under the Purchase order form
in the box that request a PO Number.
If you click on New Purchase order it opens a blank
form saying new. When you hit enter it assigns a po id and then
the next line I want it to assign the next po number in sequence,
ie 10191, then next number would be 10192 without manually having
to enter that number in that field. I hope you can help.

Tim

[Robeen]

Is this Form ONLY for Data Entry?

If so - then it should not be a problem.