Auto Incrementing a field without using Autonumber

[MrSmith]

So I have a table tblPO2006 with a field PO that contains a number.
Currently I have 100 records and the curent number in this field is 10804
What i want to do is when a new record is created the field updates with a +1 increment to the previous records number.
Thus the new record would be 10805 and the next 10806 etc

I dont want to use autonumber... can someone point me in the write direction of code to do this.

thanks

 

[ByteMyzer]

So I love how people begin postings and conversations with the word "So", as though they were continuing a previous discussion

All sarcasm aside, there is a simple VBA function which you can use to calculate the next PO Number:

Public Function NewPO() As Long

    Dim rs As DAO.Recordset

    Set rs = DBEngine(0)(0).OpenRecordset("SELECT MAX(tblPO2006.PO) AS PO " _
        & "FROM tblPO2006", dbOpenSnapshot)
    NewPO = IIf(IsNumeric(rs!PO), rs!PO + 1, 1)

    rs.Close
    Set rs = Nothing

End Function

You didn't specify how the new record is to be created, with a form or with an append query, or what. If you use a form to add a new record, and you have a field on the form with the PO field as the control source, you can insert the following code into your form's Code Module:

Private Sub Form_BeforeInsert(Cancel As Integer)

    Me.PO = NewPO

End Sub

 

[RuralGuy]

Use DMax() + 1 on the field in the BeforeUpdate event of your form. You will want to test for IsNull or Me.NewRecord so you don't overwrite a valid value.

 

[MrSmith]

Hi,

Thanks for the responses however i must be doing something wrong as neither seems to work.

The data is enetered onto a Form - the form gets its data from POQuery which is a query from 2 seperate tables.

All the data on the form works fine - however i cannot seem to get the POnumber to increment.

tblPO2006
tblVendors

qryPO

fmPO2006

Thanks for your time

 

[RuralGuy]

If you used my suggestion, what code did you put in the BeforeUpdate event?

 

[Uncle Gizmo]

You have to be careful in a multiuser environment that two records are not created at the same time, with the same number.

Cheers Tony.

 

[The_Doc_Man]

If the number isn't stored in numeric format, (i.e. it is a number but stored in a text field), that won't work right. Gotta be working with "real" numbers (INT, LONG) to be able to add numbers to them. Any chance that your "number" field just happens to be a text field that was used with digit characters?

 

[MrSmith]

Rural -

Im not entirely sure how to write the code to test for is null -
so i am getting just a blank return as opposed to a number.
The field is definatly set to number/long interger

any surgestions?

 

[RuralGuy]

Please post the code you used in the BeforeUpdate event so we can see what you have tried.

 

[MrSmith]

[PO] = DMax("[PO]","PO2006") + 1

Is this right? i add it to the Before update of the form in code build.

 

[Rich]

DMax('PO','PO2006') + 1

 

[RuralGuy]

That should work. Put MsgBox "New PO is [" & [PO] & "]" just after that DMax() line and see what is returning from the function. You already have some records in there correct? You should never get a Null once the code starts working.

 

[MrSmith]

ok followed your instructions

whats weird is that when i add a new record the number doesnt appear
however when i use the back record button i get the pop up message box that tells me the new po number and if i now go forward to the record i created i can now see the number....

 

[RuralGuy]

If you want to be a little more user friendly then put the same code in the BeforeInsert event as well. That will show the value on your form after the first keystroke. Since the record has not been written yet, the code in the BeforeUpdate event will get the same number. I would put the code in the BeforeUpdate event inside a If Me.NewRecord Then ... End If structure so it only works on a new record.

 

[RuralGuy]

whats weird is that when i add a new record the number doesnt appear...

For the record, the BeforeUpdate event only fires when you are saving a record and unless you provide otherwise, that only occurs when you try and move off of the record or close the form.

 

[MrSmith]

So which event occurs once a new record is created ?
as i assume i need to add the code to that event in order for it to enter the record number straight away.

thanks for your time

 

[MrSmith]

Great I got it working now with the before insert

thank you so much for your help

 

[RuralGuy]

If you look up events in Access help (not VBA help) it has a pretty complete description of all of the events and the sequence. AFAIK no event occurs just because a new record is created. The BeforeInsert event occurs as soon as the record is Dirty (needs saving) and that occurs when the user types the 1st character into a control, even before the user is done with the control. The BeforeUpdate event occurs *every* time a record needs to be saved and just before it is saved, which is why you need to eliminate all but the NewRecord events. You can see that BeforeInsert can occur minutes before the BeforeUpdate event.

 

[RuralGuy]

If you are multi-user then it *needs* to be in the BeforeUpdate event. There is a very small window between the event and the actual write where two users could get the same number. As I said earlier, there can be a *huge* window between the BeforeInsert event and the actual update of the record. Do yourself a favor and put it in both events and fix the BeforeUpdate event to only work on NewRecords.

 

[MsLady]

I wonder why this isn't working for me.
I am trying to get a customer autonumber like this 08060001 i.e. mmyy0001 and i have tried the suggestions in this thread.

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me.NewRecord Then
    Me!jobId.DefaultValue = Format(Date, "mmyy") & Nz(DMax("[jobId]", "tblJobDetails"), 0) + 1
End If

When i open a new form the first time, it works. Any records after that will shoot an error msg telling me to enter a value into Me!JobId cos i have set the required value to true.


What am i missing?