Number increase

[tony curtis]

I am trying to create a drawing register, each new drawing has a number that increases by one for each new drawing entered.
I can not seem to get either an update or number +1 to work correctly.
My number format is AAA000000 this is to be assign only when certain text fields have been filled in, if any easier a button could be created to issue said number.
The prefix letters remain the same on all drawings and the number is to increase by 1.
please help !

[damonreid]

What I did when I had a problem like this was to create a combo box with all the codes I needed. Eg:

AAA
AAB
AAC
AAD
AAE

When the picked a value from the combo box I used that on a query to select all of that type. (only AAA's say).
On AfterUpdate for the combo box I used the following code:

    Dim strProdCode As String
 
        If Me.NewRecord Then
        strProdCode = Me.SectionCode.Value
 
            ' Increment the CodeNumber field by One, if field is Null, set as 0 then add 1
            Me!CodeNumber = Nz(DMax("Right(Code,3)", "QueryName"), 0) + 1
            'Customize the ID then attach the incremented CodeNumber
            Me!Code = Me.SectionCode.Value & "-" & Format(Me!CodeNumber, "000")
 
        Else
 
            Me!Code = Me!Code
        End If

This would give AAA-001 as the first value, AAB-001 as the frist value in that section and then AAA-002 ect.

You need to add a new field to your list (in my case CodeNumber) to work with the SectionCode for this to work.

[tony curtis]

What I did when I had a problem like this was to create a combo box with all the codes I needed. Eg:

AAA
AAB
AAC
AAD
AAE

When the picked a value from the combo box I used that on a query to select all of that type. (only AAA's say).
On AfterUpdate for the combo box I used the following code:

    Dim strProdCode As String
 
        If Me.NewRecord Then
        strProdCode = Me.SectionCode.Value
 
            ' Increment the CodeNumber field by One, if field is Null, set as 0 then add 1
            Me!CodeNumber = Nz(DMax("Right(Code,3)", "QueryName"), 0) + 1
            'Customize the ID then attach the incremented CodeNumber
            Me!Code = Me.SectionCode.Value & "-" & Format(Me!CodeNumber, "000")
 
        Else
 
            Me!Code = Me!Code
        End If

This would give AAA-001 as the first value, AAB-001 as the frist value in that section and then AAA-002 ect.

You need to add a new field to your list (in my case CodeNumber) to work with the SectionCode for this to work.

Thanks!

but i just need to increase the number by 1, did'nt quite get how to do that.
Sorry but new to SQL but slowly getting there.