How increment field by 1 on a form when the field is not an autonumber field

How can I increment a field on a form that is not  a primary key auto-number field.  The field has to look like 020000 as the first record I create and has to appear that way so what field type should it be?  The next record would be 020001, the next one 020002, etc.
SteveL13Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
Just search for "custom autonumber" or similar. Many, many examples out there.
0
Dale FyeOwner, Developing Solutions LLCCommented:
if it must have leading zeros, then it must be a string, although you would not have to store it that way, you could store the number as a long integer and simply use Format(lngNumber, "000000").

Then, to get the next number you would query the field that the number is stored in for the maximum in that column:

dim lngNextNumber as long
lngNextNumber = NZ(DMAX("lngNumber", "yourTable"), 20000) + 1

However, I would not fill this value in until just before you save the record, in the Form_BeforeUpdate event.  The reason for this is that if you populate a field with this value, and then don't save the record right away, then someone else (in a multi-user environment) could call the code to create that number and would generate the same number.  So your BeforeUpdate event might look like:
Private Sub Form_BeforeUpdate(Cancel as Integer)

    if me.txtSomefield & "" = "" then 
        msgbox "enter a value in SomeField!"
        me.txtSomeField.SetFocus
        Cancel = true
        Exit Sub
    endif

    me.txtNewValue = NZ(DMAX("lngNumber", "yourTable"), 20000) + 1
    'These last two assume you have fields LastModified and ModifiedBy in your forms RecordSource
    me.!LastModified = Now()
    me!ModifiedBy = fOSUsername 
End Sub

Open in new window

0
PatHartmanCommented:
Numbers do NOT contain leading zeros.  We learned that in grammar school.  You are forcing a string format but you need a numeric data type.  If you want a six digit number, start with 100001.  If you have existing numbers in the 20000 range that you need to convert, start with 120002.

If you insist on going with the string format, you are going to be converting to and from a number to generate the next ID.

Personally, I would let go of the leading zeros and use a long integer.  Most people don't want their user visible IDs to start with 1.  They would rather have a longer number so you can start with any value you want by simply manually inserting a record that is 1 less than what you want as your starting value.  I would then delete the dummy record once I have added a valid record.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

mbizupCommented:
I agree with Pat regarding storing a numeric/long integer.  Dale's basic approach applies for generating the next number in the sequence:

DMax(YourFieldName) + 1

Open in new window


To display a number on your form with leading zeros, you would format the number by making the control source for your textbox something like this, which would force four characters, padded with leading zeros as needed:

= Format([YourFieldName], "0000")

Open in new window

 

Is the "02" prefix representative of anything?  Department?  Project?  Etc?  
Is that prefix subject to change?

If it is subject to change, you might consider storing it in a separate field, titled according to whatever it represents.   If you do store it as a separate field, the textbox control source for your custom autonumber would concatenate the prefix to your autonumber like this:

' This control source would result in numbers like 020001
= [YourPrefixFiedlName] & Format([YourFieldName], "0000") 

Open in new window

 

' This control source would result in numbers like 02-0001
= [YourPrefixFiedlName] & "-" &  Format([YourFieldName], "0000") 

Open in new window

 

If the 02 prefix is NOT subject to change (fixed), a control source as follows would format your autonumber to include it:
'Formatted like 020001
= Format([YourFieldName], "020000") 

Open in new window

 

' Formatted like 02-0001
= Format([YourFieldName], "02-0000") 

Open in new window

0
SteveL13Author Commented:
MBizup:  The 02 prefix is NOT subject to change.

So in an attempt to make this work using suggestions I have this code in the beforeupdate event of the form:

Private Sub Form_BeforeUpdate(Cancel As Integer)

    Dim lngNextNumber As Long
    lngNextNumber = Nz(DMax("PO_N", "tblPurchaseOrders"), 89000) + 1
    
    Me.txtQASR_N = Nz(DMax("PO_N", "tblPurchaseOrders"), 89000) + 1
    
    Me.txtQASR_N = Format([PO_N], "000000")
 
End Sub

Open in new window


But nothing is happening.  Nothing is getting entered in the PO_N field in the table when I close the form.  Also, for what its worth I can't seem to have PO_N as the control source for the field in the form.  

What in the world am I doing wrong?
0
Gustav BrockCIOCommented:
You can do:

ThisNumber = "020002"

NextNumber = Format(Val(ThisNumber) + 1, "000000")

Open in new window

0
SteveL13Author Commented:
I seem to have it working with:

Private Sub Form_BeforeUpdate(Cancel As Integer)

    Dim lngNextNumber As Long
    lngNextNumber = Nz(DMax("PO_N", "tblPurchaseOrders"), 89999) + 1

    Me.txtPO_N = Nz(DMax("PO_N", "tblPurchaseOrders"), 89999) + 1
    Me.txtPO_N = Format(Val(PO_N), "000000")
 
End Sub

Open in new window


But I have two issues.  The number doesn't show up in the form unless I close the form and re-open it.  Plus it doesn't display as 090000.  It only shows as 90000.

I do now have PO_N as the control source for the field on the form.
0
Gustav BrockCIOCommented:
You should be able to requery the textbox:

Me!txtPO_N.Requery

Open in new window


You may also have to set the Format of the textbox as well: 000000
0
Dale FyeOwner, Developing Solutions LLCCommented:
1.  The number does not show up on the form until after you close that record and come back to it because you don't actually fill that field until the record is saved.  Although you should see the value as soon as you have saved the record.  You should not have to move off the record to see the Formatted_PO_N value (see #2 below)

2.  Personally, I would use a query as the RecordSource for the form.  It would look something like:

SELECT yourTable.*, iif(IsNull(yourTable.PO_N), NULL, Format(yourTable.PO_N, "0000000") as Formatted_PO_N
FROM yourTable

Then, I would bind txtPO_N control to the Formatted_PO_N column from the query.  This will not be updateable, but should display the number with the value that is in the PO_N field of the table.

Dale
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PatHartmanCommented:
Store the value as a string.  It is a string so treat it as one.  If you don't, you'll have nothing but trouble with searches and joins.

To generate the next number, convert it to numeric and then reformat it as a string.

    Me.txtPO_N = Nz(clng(DMax("PO_N", "tblPurchaseOrders"), 20000)) + 1
    Me.txtPO_N = Format(Me.txtPO, "000000")

I put the default back to 20000 since I couldn't figure out where the 89999 came from.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.