Auto Increment By One Letter

[CINQUEFOIL22]

Ok. I work in a welding shop and we are in the process of creating a database that allows us to keep track of our inventory coming in and going out.
Each piece of new material we bring in is assigned a Trace Code which is simply three letter. First piece brought in is AAA. Next piece AAB and so on...When you get to AAZ the next one becomes ABA. Ultimately when you get to AZZ your next series is now BAA. Is there a way for me to set up the form to automatically do that?
I have already set up the form and table and they are named Inventory Receiving List. The field for this data is named Trace Code. Any help anyone could offer would be greatly appreciated.
Please be mindful in answering that I really am not very strong with formulas and modules.
Many Many Thanks in advance!!

 

[Brianwarnock]

I think that this has to be done using VBA so create a module, you can leave it as module 1, then paste and copy the code below into that module.
to use the function just use aPLUS1(yourfield), you can rename the function but remember to change it in the code in each place and keep it different to the module name.

I haven't allowed for what happens after ZZZ

Brian

Function aPLUS1(mystring As String) As String
Dim char1 As String
Dim char2 As String
Dim char3 As String
char1 = Left(mystring, 1)
char2 = Mid(mystring, 2, 1)
char3 = Right(mystring, 1)

If char3 < "Z" Then
char3 = Chr(Asc(char3) + 1)
GoTo exitcode
Else: char3 = "A"       'char2 must have been Z
    If char2 < "Z" Then
    char2 = Chr(Asc(char2) + 1)
    GoTo exitcode
    Else: char2 = "A"
    char1 = Chr(Asc(char1) + 1)
    End If
End If
exitcode:
aPLUS1 = char1 & char2 & char3
End Function

 

[Brianwarnock]

I don't think I really answered your question.

In the Form Before insert event place the following code replacing f1 and table2 by your field and table names, which I would change to have no spaces replace a space by the underscore. spaces cause syntax problems andforce the use of [ ].

Private Sub Form_BeforeInsert(Cancel As Integer)

If Me.NewRecord = False Then
Exit Sub
Else
mystring = DMax("f1", "table2")
End If
Dim char1 As String
Dim char2 As String
Dim char3 As String
char1 = Left(mystring, 1)
char2 = Mid(mystring, 2, 1)
char3 = Right(mystring, 1)

If char3 < "Z" Then
char3 = Chr(Asc(char3) + 1)
GoTo exitcode
Else: char3 = "A"       'char2 must have been Z
    If char2 < "Z" Then
    char2 = Chr(Asc(char2) + 1)
    GoTo exitcode
    Else: char2 = "A"
    char1 = Chr(Asc(char1) + 1)
    End If
End If
exitcode:
Me.f1 = char1 & char2 & char3

End Sub

Private Sub Form_Open(Cancel As Integer)
If Me.NewRecord = False Then
Exit Sub
Else
mystring = DMax("f1", "table2")
End If
Dim char1 As String
Dim char2 As String
Dim char3 As String
char1 = Left(mystring, 1)
char2 = Mid(mystring, 2, 1)
char3 = Right(mystring, 1)

If char3 < "Z" Then
char3 = Chr(Asc(char3) + 1)
GoTo exitcode
Else: char3 = "A"       'char2 must have been Z
    If char2 < "Z" Then
    char2 = Chr(Asc(char2) + 1)
    GoTo exitcode
    Else: char2 = "A"
    char1 = Chr(Asc(char1) + 1)
    End If
End If
exitcode:
Me.f1 = char1 & char2 & char3
End Sub

 

[CINQUEFOIL22]

First, I would like to thank you for helping me. Really do appreciate it...
I copied the formula you left in your second post and put it into my form as you said to do.
I changed f1 to TraceCode, and table2 was changed to InventoryReceivingList. This is what it looks like:

Private Sub Form_BeforeInsert(Cancel As Integer)
If Me.NewRecord = False Then
Exit Sub
Else
mystring = DMax("TraceCode", "InventoryReceivingCode")
End If
Dim char1 As String
Dim char2 As String
Dim char3 As String
char1 = Left(mystring, 1)
char2 = Mid(mystring, 2, 1)
char3 = Right(mystring, 1)
If char3 < "Z" Then
char3 = Chr(Asc(char3) + 1)
GoTo exitcode
Else: char3 = "A" 'char2 must have been Z
If char2 < "Z" Then
char2 = Chr(Asc(char2) + 1)
GoTo exitcode
Else: char2 = "A"
char1 = Chr(Asc(char1) + 1)
End If
exitcode:
Me.TraceCode = char1 & char2 & char3
End Sub
Private Sub Form_Open(Cancel As Integer)
If Me.NewRecord = False Then
Exit Sub
Else
mystring = DMax("TraceCode", "InventoryReceivingList")
End If
Dim char1 As String
Dim char2 As String
Dim char3 As String
char1 = Left(mystring, 1)
char2 = Mid(mystring, 2, 1)
char3 = Right(mystring, 1)
If char3 < "Z" Then
char3 = Chr(Asc(char3) + 1)
GoTo exitcode
Else: char3 = "A" 'char2 must have been Z
If char2 < "Z" Then
char2 = Chr(Asc(char2) + 1)
GoTo exitcode
Else: char2 = "A"
char1 = Chr(Asc(char1) + 1)
End If
exitcode:
Me.TraceCode = char1 & char2 & char3
End Sub


However when I go to my form back into form view it keeps giving me this error:

Compile Error
method or data error not found

and this is what it is pointing to:

Me.TraceCode = char1 & char2 & char3
End Sub


sooo...sighs, i am still confused...

 

[Brianwarnock]

OK I'm confused also. I don't have 2007 but if you can upload your DB in an earlier version I will have a look at it.
Compact and repair from the tools menu first and/or zip it.

I take it that Tracecode is the name of the control on your form?

Brian

 

[CINQUEFOIL22]

You have no idea how much I would love your help....I don't usually find myself being dumb...but with this I totally am....
And yes, TraceCode is the name of the control on my form....
Where do I send it to? I can save it in 2003 format...

 

[Brianwarnock]

You attach it to a post, when you reply scroll down and under Additional Options you will find manage attachments.

Brian

 

[CINQUEFOIL22]

Thank you so VERY MUCH!!!

 

[Brianwarnock]

Ok problem solved. I warned against spaces in names and sure enough that was the problem, Yes the field in the table was Tracecode but the name of the control on the form was Trace Code, for which VBA uses Trace_Code this is easily found by using the VBA intellisense, ie when you type the Me. a dropdown lists all of the viable statements, enter the T and Trace_code appears select it and your code works.
I list it below.

A couple of points
1 I was puzzled by the lack of Labels on your Form
2 I would remove the Tab stop from the Trace_code control to prevent accidental changes. Changes can still be made by selecting with the mouse.
This is done in the properties of the control in design view.
3 I have not only not considered what to do after ZZZ but did not allow for the first entry, that will be done manually - sorry . I suppose one could check the return from the DMAX.

I think that's it
Best Wishes in your efforts and remember that there are experts here, as well as me, willing to help

Brian

BTW there was no code with the posted DB so as an afterthought I have attached the DB I played with with the changes in.

If Me.NewRecord = False Then
Exit Sub
Else
mystring = DMax("tracecode", "inventoryreceivinglist")
End If
Dim char1 As String
Dim char2 As String
Dim char3 As String
char1 = Left(mystring, 1)
char2 = Mid(mystring, 2, 1)
char3 = Right(mystring, 1)

If char3 < "Z" Then
char3 = Chr(Asc(char3) + 1)
GoTo exitcode
Else: char3 = "A"       'char2 must have been Z
    If char2 < "Z" Then
    char2 = Chr(Asc(char2) + 1)
    GoTo exitcode
    Else: char2 = "A"
    char1 = Chr(Asc(char1) + 1)
    End If
End If
exitcode:
Me.Trace_Code = char1 & char2 & char3

 

[CINQUEFOIL22]

From the very bottom of my heart, I am so very much thankful for your help!! If I could kiss ya I would!!
You had a couple of things you commented on, so I figured I would answer....
Labels - I never really payed attention to 'em....I am not sure, are they really all that important?
Tab Control - Being that I was only in the designing stages, it was just that way. Once I had what I needed, intentions were to lock it so no one could mess with it (which it is now)
ZZZ - I don't even know what the boss man plans to do once we hit that point, but I feel that is a long ways off...But Thanks for your concern about that!!!
Anyways, I love what you did. It was exactly what I needed. I did go into it and can see what you are talking about as far as the spaces go...So the good news is that I learned from your experience!! Many Thanks!!

 

[Brianwarnock]

Thanks for coming back, its appreciated its amazing how many people just take the answer and disappear.

Labels - Its each to their own but I like to know what each field on the form represents, not essential but I prefer it.

Hope your Boss is happy

best of luck

Brian

 

[vbaInet]

From the very bottom of my heart, I am so very much thankful for your help!! If I could kiss ya I would!!

Go on then! Location - Merseyside, UK

 

[CINQUEFOIL22]

Go on then! Location - Merseyside, UK

Well, as soon as my boss man gives me that payraise and I can afford that leer jet, I just might!! Many Thanks goes to Brian!!! Couldn't have done it without his help and patience!!

 

[CINQUEFOIL22]

Thanks for coming back, its appreciated its amazing how many people just take the answer and disappear.

Labels - Its each to their own but I like to know what each field on the form represents, not essential but I prefer it.

Hope your Boss is happy

best of luck

Brian

Brian,

Bossman = Happy man. However, he has brought me more confusion. I did add a form that allows for the guys on the floor to log what they take out by simply typing in the trace code and the qty. He wants me to be able to run a report that states how many are left of the said product. I thought I could just subtract one field from the other on the two tables but all it does is not what I want it to do.
At first I was frustrated, but now I am just figuring, Hey, if I can learn from it why not, right??
I was and still am very good at Crystal Reports and even could figure my way around access 2003. But this 2007 thing has my mind boggled...

 

[Brianwarnock]

Crystal Reports arrived on site as I retired so I never got to grips with it, likewise with the problems people have with 2007 and me only using ACCESS on the forum i wont purchase it but will just fade away on 2002.

Inventory databases have been discussed many times on the forum, a search may help.

Brian