auto number (1 Viewer)

[gizmogeek]

Hello all I am looking for some help with next number showing up on form. I set tried to setup auto number in my table but my key id is setup as autonumber and it tells me I can't have two. Is there a way to get a "next number" to show up on my form when I open the form? I'm also hoping that if the form is not completely filled out that it discards that number and uses it for the next form opened.

Thanks!

 

[jdraw]

You should read this to see what auto numbers are and when to use them.
http://www.utteraccess.com/wiki/index.php/Autonumbers

good luck.

 

[gizmogeek]

Okay I did read it and it doesn't look like I want to use that. Especially with the dropped numbers on compact. So if I'm looking to set like and invoice number on each form what would be the best way to accomplish this?
Thanks!

 

[John Big Booty]

Also have a look at the DMax() function plus one and search this forum on the subject you should find plenty of samples and discussion on the topic.

 

[jdraw]

GizmoGeek,
I hope you noticed that autonumbers are Not necessarily
. sequential nor
. positive

If you start to create a record with an autonumber field, and decide to discard the record- that autonumber is gone.

As John says, look for DMax and DMax + 1 usages.

 

[gizmogeek]

Okay. I got that but it isn't working for me.

Private Sub Form_BeforeInsert(Cancel As Integer)
PO# = Nz(DMax("PO#", "Purchase Order Table")) + 1
End Sub

Could it have something to do with the spaces in the table name?

My goal is when I open the form a new PO# comes up.

 

[gemma-the-husky]

Private Sub Form_BeforeInsert(Cancel As Integer)
PO# = Nz(DMax("PO#", "Purchase Order Table")) + 1
End Sub

it's more likely to be po#

what IS PO# - for this to work it needs to be the name of your bound control on the form which stores the po#

 

[gemma-the-husky]

ah. I see. it needs to be in the current event, rather than the beforeinsert

you want this in the currentevent

if me.newrecord then
....your code
end if

(if you don't restrict it to newrecords, you will overwrite records you already entered. )

note that if more than one of you are entering records, you will both get the same po#. the best place to put this is actually in the beforeupdate event, although that means you won;t get the order number until right at the end

the other thing is that if you start entering a record, and then abandon it, you may leave partially edited records in your table. another reason to leave the po# fetch until the end.

 

[gizmogeek]

The code still doesn't work. This is what I have now.

Private Sub Form_Current()
If Me.NewRecord Then
PO# = Nz(DMax("PO#", "Purchase Order Table")) + 1
End If
End Sub

So if more than one user will be using the form is there a way that I can make it so the PO# will be unique for each? Also wondering if more than one person will be using it of course I want the database on the server itself but what about a front end? It's been a long time since I've done this so I can't recall how I've done it in the past. I also have another project just like this one to do.
Thanks!

 

[jdraw]

See the article and samples at
http://www.utteraccess.com/forum/Custom-Autonumbers-t1953743.html

The discussion is well worth the read just to see some of the concepts.

I'd recommend no spaces or special characters in field and object names. Limit names to alphas and numerics (and the underscore"_")

 

[gemma-the-husky]

The code still doesn't work. This is what I have now.

Private Sub Form_Current()
If Me.NewRecord Then
PO# = Nz(DMax("PO#", "Purchase Order Table")) + 1
End If
End Sub

So if more than one user will be using the form is there a way that I can make it so the PO# will be unique for each? Also wondering if more than one person will be using it of course I want the database on the server itself but what about a front end? It's been a long time since I've done this so I can't recall how I've done it in the past. I also have another project just like this one to do.
Thanks!

as I say - do not allocate the PO# until the end of the order.
either that or take the next order from a table, and update it immediately. The problem with the latter approach is that if you cancel the entry you "lose" the order number you just took. You need to decide how you wqant to manage your PO# sequence.

 

[gizmogeek]

jdraw I did read the articles and looked at the samples and they were a bit over my head. I also had others check it out and they didn't understand either.

gemma-the-husky the code is still not working for some reason? I did allocate at the end. Am I missing something? Could it be because of the table names having spaces?

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
PO# = Nz(DMax("PO#", "Purchase Order Table")) + 1
End If
End Sub
​

 

[CJ_London]

Just a thought - is PO# defined in your table as a number or text? And to clarify, when you say the code doesn't work - do you mean you get an error on execution or no error but after update the PO# field in the table is blank or 0 or some other value?

 

[gizmogeek]

I don't get anything in the table or on the form. No errors either. The field is defined as a number.
Thanks!

 

[jdraw]

Perhaps you should run a compact and repair, then zip a copy of your database with any confidential info removed.

My guess is that none of the readers fully understand what exactly you are trying to do.

 

[CJ_London]

there is an error in your code if you use # or other non alphanumeric characters in your field name, they need to be surrounded with square brackets

[PO#] = DMax("[PO#]", "Purchase Order Table") + 1

will work

So Dmax wouldn't work for me because when I compress the database there would be deleted records.

Not sure what you mean by this, you shouldn't reuse numbers

 

[RainLover]

There is very little guarantee that the DMax will work as you wish. It will work 99% of the time but in a multiuser version the chances of the number duplicating increases.

This is why in the real world you get that number last. For example if you want a receipt of a conversation with a company on the phone the number is not produced until the record is committed. I don't know any company that give the receipt number first.

There are tricks etc that the others are trying to show you but none are 100% correct. They are however the best you can come up with.

In my signature their is a sample of how to use Dmax + 1 for single users and another for multiusers. The later is for advanced users only.

Good luck.

Edit

My link is broken. Will fix Soon.

 

[gizmogeek]

Okay RainLover I changed my field name to "PO". I took the single user code and tried it and it doesn't work for me. I'd also like to know how your single user form is updated. I see that you have it written for AfterUpdate but don't see where it actually gets updated.

I am unable to see form properties of the multiuser form that you have there. It doesn't give me an option to change to design view.

This is what I have for code now.

Private Sub PO_AfterUpdate()

If (conHandleErrors) Then On Error GoTo ErrorHandler

' If we have some data
If Len(Me!PO) Then

' Find the highest number and increase it by ONE.
Me!PO = Nz(DMax("[PO]", "Purchase Order Table", "[PO] = '" & Me!PO & "'"), 0) + 1
Else
MsgBox "A String is Required", vbInformation, "Missing Information"
Screen.PreviousControl.SetFocus
Me.Undo
End If

' Added 03/02/2011 by Rainlover.
Me.txtConcatenated = Me.PO
Me.Refresh

ExitProcedure:
Exit Sub

ErrorHandler:
DisplayError "PO_AfterUpdate", Me.Name
Resume ExitProcedure

End Sub

 

[RainLover]

Off line for 15 minutes.

Back soon

 

[CJ_London]

this doesn't make sense

' Find the highest number and increase it by ONE.
Me!PO = Nz(DMax("[PO]", "Purchase Order Table", "[PO] = '" & Me!PO & "'"), 0) + 1

The bit highlighted in red is limiting your returned value to one where PO=me.PO which is a nonesense. In addition, this implies that PO is text, whereas in your db PO is a number

I refer you back to my post

[PO#] = DMax("[PO#]", "Purchase Order Table") + 1

works in the db you uploaded, although I did add an inital value to the existing record so really you should be using for the first record

[PO#] = nz(DMax("[PO#]", "Purchase Order Table")) + 1