Alphanumeric auto number not incrementing

[StephD8459]

Hi All,

I'm looking for some help correcting my code below.

What I'm trying to accomplish is when a user selects a value from a dropdown field on a form that value of the field code in the table Support Detail is checked by the code and incremented by 1

The field code is a concatenation of Region (NA, EU, AP) the letters MN and a numeric value.

What I'm getting from my code below is the Region selected, 'MN' and 1. Example EUMN1

Private Sub Region_AfterUpdate() 'autonumber
If Me.NewRecord Then
    Me.Code = Me.Region & "" & "MN" & Nz(DMax("val(Right('[fieldnamedcode]',Len('[fieldnamedcode]')-4))", "[Support Detail]") + 1, "0000")
End If
End Sub
 

I've also tried

Private Sub Region_AfterUpdate() 'autonumber
If Me.NewRecord Then
    Me.Code = Me.Region & "" & "MN" & Nz(DMax("val(Right('[fieldnamedcode]',4))", "[Support Detail]") + 1, "0000")
End If
End Sub
 

fyi, the above "fieldnamedcode" is just code, but I can't post it that way as it causes and issue for the forum
Any assistance you can give me is appreciated
Thanks

[gnawoncents]

It would be much simpler to add a field to your table that contains a numeric value, rather than trying to break down a text then combine it again. For example, add a field called CodeValue which you increment by 1 whenever your conditions are met. Then, whenever you need to display the “Code” you use the following:

 Me.Code = Me.Region & "MN" & me.CodeValue
 

I eliminated the extra quote marks as they were not doing anything.

[NeoPa]

I had to LoL at your problem with the field named [code]. You're absolutely right that it is used in the BB Code that the site uses.

Domain Aggregate functions can be a bit of trouble to use. The first question that comes to my mind, when attempting to help you towards a solution, is :
Is the number required the next number sequentially from whatever was last used (as the question implies) or is it the more usual requirement of the next number in sequence that matches the Region value selected?

The format of the command would need to be more like :

Private Sub Region_AfterUpdate() 'autonumber
  If Me.NewRecord Then
    Me.Code = Me.Region & "MN" & _
              Format(Nz(DMax("Right([code],4)", _
                             "[Support Detail]"), 0) + 1, "0000")
  End If
End Sub

Your original code had parameters for the functions in the wrong order so they didn't match what was expected. Otherwise you weren't too far off base.

PS. Don't forget to answer the question posed earlier in the post.

[StephD8459]

NeoPa,
So to answer your question, Ideally I'd like it to number by region, but if this is to convoluted to create, the next number should be sufficient.

On the code sample you posted I am getting a Runtime Error
Runtime error '13':
Type Mismatch

Any suggestions?

Thanks

[NeoPa]

Steph, It's always a good idea to indicate which line the error occurs on when posting error information. I may be able to guess, but always worth doing anyway.

I think I may have left out a Val() call. Try instead :

Private Sub Region_AfterUpdate() 'autonumber
  If Me.NewRecord Then
    Me.Code = Me.Region & "MN" & Format(Val(Nz( _
              DMax("Right([code],4)", _
                   "[Support Detail]"), "0")) + 1, "0000")
  End If
End Sub

[NeoPa]

For numbering sequentially by Region try :

Private Sub Region_AfterUpdate() 'autonumber
  If Me.NewRecord Then
    Me.Code = Me.Region & "MN" & Format(Val(Nz( _
              DMax("Right([code],4)", _
                   "[Support Detail]", _
                   "[code] Like '" & Me.Region & "MN????'"), "0")) + 1, "0000")
  End If
End Sub

[StephD8459]

NeoPa,
Sorry about not specifically calling out the error location, but your guess was good :)
I am no longer getting an error, but it is still not incrementing.
I am getting '0001' for all entries.

I really just don't know where to start on trouble shooting this.

Thanks again for your help
Steph

[NeoPa]

Debugging in VBA may be of some help.

While debugging, it is often helpful to break down the code from a single line including multiple functions into multiple lines that work out and save each intermediate value.