Automatically incrementing order number

[GoldenFireTiger]

I have an order form on my system, and the order numbers are set to be five digits starting with the letter O. Can I set the text box on a form or something to increment the number? I.e if record O1234 has just been entered, and the user wishes to add another record, can the system automatically change the new order number to O1235?

 

[jpstroud]

If it's just for display purposes, you can make the field an autonumber field. On forms and reports, instead of using the field itself use:

=Format([fieldname],"\O0000") 'first character after slash is the letter O

The autonumber field will increment with each new record, and the data will be displayed (not saved as) O1234, O1235, etc.

If you actually need to save the data as O1234, please let me know. I have instances of this and I can modify my code and post it for you.

 

[jpstroud]

Forgot to say, use the code I posted above (without the comment) in the data property of a text box to get the desired result. Not sure how familiar you are with access, so I just wanted to drop that piece out there

 

[GoldenFireTiger]

Thanks vewry much for your help and the code. You say it wouldn't be saved as O1235 etc, what would they save as?

 

[jpstroud]

It would save as the number itself, ie.. 1234, 1235, but would display with the leading O.

Also, if you have less than 4 digits, it will substitute zeros for the missing digits (ie, O0023, O0024). Again, this is in the display only.

This has the added advantage of not taking up as much space as a text field.

 

[GoldenFireTiger]

Would it be at all possible to save the numbers with the O?

 

[jpstroud]

It would have to be a text field in that case. To increment the number, you would need to write some code.

What is the table name and the field for the Order number? I'll post some sample code here for you.

 

[jpstroud]

Try this on for size, adjusting fields for what you need of course:

Private Sub Form_Current()
    Dim MaxOrderNo As Integer, NextOrderNo As String, rs As DAO.Recordset, db As DAO.Database
        
    Set db = CurrentDb 'initializes values
    Set rs = db.OpenRecordset("SELECT Max(CInt(Right([OrderNo],4))) AS [MaxOrderNumber] FROM Table1;")

    rs.MoveLast
    rs.MoveFirst
    
    If rs.RecordCount > 0 Then
        If Me.OrderNo.Value > 0 Then 'Checks for blank Order Number
            Exit Sub
        Else
            MaxOrderNo = Nz(rs("MaxOrderNumber"), 0)
            NextOrderNo = MaxOrderNo + 1
            OrderNo = Format(NextOrderNo, "O0000") 'Increments Order Number and applies formatting
        End If
    Else
        OrderNo = "O0000"
        Exit Sub
    End If
End Sub

Make sure that your OrderNo field is a text field and not integer. If you get any errors, make sure that you have the DAO reference set up in VBA.

Also, the Mid function wasn't working well for me so you must make sure that all order numbers are 5 characters long. This doesn't allow for expansion of the database (ie, past O9999), and the code can be tweaked for it, just not in the time I have available. I'd suggest finding a way to get Mid working. I had it working on the first record and it would error out on the second... I'll keep plugging away as to the why on that one.

Good luck!

 

[GoldenFireTiger]

JPSTROUD

The table is called tblOrder, the form is called frmOrderDetails, a sample would be O5192. I'll try your code though.. where would I place it?

 

[jpstroud]

Sorry, place it on the Form_Current event for your form. If you just drop it into the form's VBA module itself, I've already included the Private Sub.... lines. Just make sure the On Current property is set to [Event Procedure].

The code will check the current form whenever the form has the focus. If an order number is NOT found, it will assign one. This includes new forms. If a number IS found, then no new number will be assigned.

 

[GoldenFireTiger]

Thank you! I will try it! much appreciated!

 

[jpstroud]

Let me know how it turns out for you. It worked fine on my machine, but I have v2002. Not sure what you're running.

 

[GoldenFireTiger]

I have access 2000, and I already have something running on the forms on open. I tired adding the =PlaySound bit at the bottom but it doesnt work. Any suggestions?

 

[MichaelRed]

Please be aware that this 'schema' is only appropiate for SINGLE user apps. Multiple users can (e.g. eventually WILL) generate duplicates using this. There are numerous threads re this issue and a faq700-184 with aditional info.





MichaelRed

 

[GoldenFireTiger]

HI Michael red,

the system is used by multiple users, but there are no individual usernames that would seperate them.

 

[MichaelRed]

OOPs. From my perspective, you need to step back and correct that part first. If there is a network log on, and the systems are new enough, it is possible to use the network log in. but in any case, all users having the same "Id" is really poor.

It only compounds the issues of data integrity.



MichaelRed