Create an auto-increment field

[jobrfc]

I have a table contain PO details which includes a Yes/No box entitled "CC". I have created the form with all relevant fields but need an additional field which when the "CC" field is ticked creates a number starting at 1 and incrementing by 1 every time a new record is added and "CC" is ticked.

 

[John Big Booty]

Welcome to the forum.

Have a look at the Dmax() function plus 1.

The following Code in the Form's On Current Event Should do the trick;

    If Me.YourCounterField = 0 Or IsNull(Me.YourCounterField) Then
        Me.YourCounterField = Nz(DMax("YourCounterField", "YourTableName"), [B][COLOR="Red"]X[/COLOR][/B]) + 1
    End If

Note; Replace X with your seed number (the number you wish your series to start at).

 

[jobrfc]

Hi,

Thanks for the help. I'm pretty new to Access so I'm still struggling a little. I've attached a screen shot of the query design where I'm hoping to put the auto-increment code. Could you take a look and advise accordingly.

Thank you

 

[John Big Booty]

Check the On Current event of Form1 in the attached Sample.

 

[jobrfc]

Hi,

Thanks for your help. The code does create a number for each new record but I only want a number when a certain condition is met, in this case the "credit card" tickbox is checked.

 

[RainLover]

Just add an If Statement

e.g.

If Len(Me.CreditCareField) Then

Do the DMax thing

End If

EDIT

You can't do this with a Query.

Do it via a Form.

 

[Sketchin]

Welcome to the forum.

Have a look at the Dmax() function plus 1.

The following Code in the Form's On Current Event Should do the trick;

    If Me.YourCounterField = 0 Or IsNull(Me.YourCounterField) Then
        Me.YourCounterField = Nz(DMax("YourCounterField", "YourTableName"), [B][COLOR=red]X[/COLOR][/B]) + 1
    End If

Note; Replace X with your seed number (the number you wish your series to start at).

I am having trouble getting this to work. Is there any added code needed above this code?

Sorry I am not a VBA expert at all and am just fumbling my way through it.

Thanks

 

[John Big Booty]

I am having trouble getting this to work. Is there any added code needed above this code?

Sorry I am not a VBA expert at all and am just fumbling my way through it.

Thanks

No. Have a look at the sample I posted in post #4.

 

[RainLover]

I can't see a problem with John's code so it would appear that you have done something wrong.

Please post your code so we can have a look.

 

[IMGR8EST]

Thanks John. I had a primary key field with 'Date' data. I wanted to increment it with each new record. The code worked wonderfully after substituting #My last Record Date# in place of x. Now with each press of new record button, the next date is added.