Problem with VBA Code in Access

[andreygk]

Hi,
I'm having trouble with automatically incrementing one of my ID fields. The ID field has a layout of LL00 (input mask). I have set a defualt value of AA00 to the field. When I click the 'New' button labelled cmdNew i want the ID to automatically increment to the next ID. If the default value is AA00 then the next ID would be AA01 after incrementation, then AA02 and so on until AA99. Once it has reached AA99 and the 'New' button is clicked the next ID should be AB01 and so on until AB99. And the next should be BB01 and so on until BB99. This should keep going until you get to ZZ99.

This is the code i currently have. I have only created it for values AA00 to AA99 becasue i am not sure how to increment alphabetically. I can't see why the code isn't working.



Private Sub cmdNew_Click()
Dim a As String
DoCmd.GoToRecord , , acLast
a = [Employee ID]
If a = AA00 Then
[Employee ID] = AA01
Else
Do While [Employee ID] < AA99
DoCmd.GoToRecord , , acNext
[Employee ID] = [Employee ID] + 1
Loop
End If
End Sub


Thanks.

[Tommy]

Try this, it may work



Private Sub cmdNew_Click()
Dim a As String
DoCmd.GoToRecord , , acLast
a = [Employee ID]
If Val(Right(a, 2)) < 99 Then
' add 1 to the number
a = Left(a, 2) & CStr(Val(Right(a, 2) + 1))
Else
If Mid(a, 2, 1) < "Z" Then
' add a letter to the second letter
a = Left(a, 1) & Chr(Asc(Mid(a, 2, 1)) + 1) & "00"
Else
' add a letter to the first one
a = Chr(Asc(Left(a, 1)) + 1) & "A00"
End If
End If
DoCmd.GoToRecord , , acNext
[Employee ID] = a
' If a = "AA00" Then
' [Employee ID] = AA01
' Else
' Do While [Employee ID] < AA99
' DoCmd.GoToRecord , , acNext
' [Employee ID] = [Employee ID] + 1
' Loop
' End If
End Sub

[andreygk]

Thanks for your help. I tried the code you gave me. When i ran it a message box appeard saying "Run-time error '2105': You can't go to the specified record".
When i opened the VBA the line "DoCmd.GoToRecord , , acNext" was highlighted in yellow.
Any suggestions to why this occured and how to get around it?

[Tommy]

DoCmd.GoToRecord , , acNewRec maybe?

[orange]

Thanks for your help. I tried the code you gave me. When i ran it a message box appeard saying "Run-time error '2105': You can't go to the specified record".
When i opened the VBA the line "DoCmd.GoToRecord , , acNext" was highlighted in yellow.
Any suggestions to why this occured and how to get around it?

Just curious but what is the significance of AA01.. AA99, AB01.. AB99?

[andreygk]

That's the format i chose to use for the ID. I'm currently doing my coursework for my A2 computing course. Using that format for my ID and a loop would show complexity of coding which is bound to get me the marks i need to get my A grade.

Tommy, i used DoCmd.GoToRecord , , acNewRec as you suggested and that worked perfectly. However another error message appeared saying "Run-time error '13': Type mismatch" and the following line of code was highlighted in yellow - a = Left(a, 2) & CStr(Val(Right(a, 2) + 1)).

[Tommy]

a = Left(a, 2) & CStr(Val(Right(a, 2) ) +1)

My Bad, had the add 1 in the wrong set of ()

[Tommy]

BTW I have never taken a VB computer class, could we share the 'A' :devil2:
What is an A2 computer class?

[andreygk]

I'm 6th Form in London at the moment. Sometimes referred to as College. One of the subjects I'm doing is Computing. In the first year you da AS Computing and in the second you do A2 Computing. Combined they are A Level computing. We do A Levels before going to University.
Thanks for your help, really! I would have had to use the boring incrementation i used previously where digits were incremented from 0 to 99,999.

[Tommy]

No Problem, next time please let us know you are doing this for class work and will be a lot more informative as to the whys and wheres. :) But You will have to do it, then we will explain where and why if you have problems. After if we do it you don't really learn anything.

On the next one :)

[andreygk]

Oh, that would actually be a lot better. My teacher suggested i do it this way, by simply posting my problem and asking for someone to help.

[andreygk]

Hey, it's me again.
When I click the 'New' button the incrementation works, at first, and the ID becomes AA1, which is fine. However when I click the 'New' button again to go to the next record the form goes to the next record but the ID does not increment to AA2, it remains AA1 and because the field is a primary key I can't do anything becasue the ID is duplicated.

[Tommy]

The issue is here, as long as the number is < 10 the issue will remain so what needs to be done is check for a numer < 10 and check for a length of 4 then do something with it. As far as I know there is about 3 ways to handle it. My bad again. This is what I get for just pointing in the direction LOL


a = Left(a, 2) & CStr(Val(Right(a, 2) + 1))

BTW I am watching the learnvisualstudio.net videos on how to code in C# so forgive me if I start drifting.

[Tommy]

I believe this is going to fix you up. I need you to test cause I don't have a way to test this at this time.


Private Sub cmdNew_Click()
Dim a As String
DoCmd.GoToRecord , , acLast
a = [Employee ID]
' check for the first record
If Len(a) < 4 Then
a = "AA00"
End If
' add a number unless you want to start the numbers from 0
If Val(Right(a, 2)) < 99 Then
' add 1 to the number
If Val(Right(a, 2)) < 10 Then
' this will ensure that the format is correct and
' remains correct for the first 9 numbers in each sequence - "AA01"
If Val(Right(a, 2)) = 9 Then
a = Left(a, 2) & CStr(Val(Right(a, 2)) + 1)
Else
a = Left(a, 2) & "0" & CStr(Val(Right(a, 2)) + 1)
End If
Else
a = Left(a, 2) & CStr(Val(Right(a, 2)) + 1)
End If
Else
If Mid(a, 2, 1) < "Z" Then
' add a letter to the second letter
a = Left(a, 1) & Chr(Asc(Mid(a, 2, 1)) + 1) & "00"
Else
' add a letter to the first one
a = Chr(Asc(Left(a, 1)) + 1) & "A00"
End If
End If
DoCmd.GoToRecord , , acNewRec
[Employee ID] = a
End Sub

[andreygk]

OMD!!! Thank you so much. That works brilliantly. By the way do you have any tips from where i can get information about VB becasue the books i have from school are extremely basic and they do not help much. If i have any real poblems again i will post on here but do not give me the code. Just tell me about the VB statements, which could help me.
Thanks again, so much.

[Tommy]

I can't recommend any books due to not having read any. I do recommend learning the .NET framework.

Your best friend will alternate between the F1 key (help) and the object browser (F2). Other things will be the Watch window, locals window, F8, Shift + F8, and F9. Just to name a few. Use Google (or any other search engine) to look and find explanations about specifics.

The basics are a very good place to start!!! Learn these to the point of being automatic. This will keep you out of more "traps" than you can shake a stick at!!!!

When you ask/post in forums make sure to explian in detail what you are trying to do, the code you have written, and where the problem first shows up. Sometimes someone will tell you to code it differently. Ask why don't be offended, they can't see what you do, or they may be showing you "best practices". Mainly to get you out of the pickel you're in, and keep you out. :) Could be just trying to get you to think more about what you are doing and make sure this is actually what you want.

LOL There is more but I am border line ranting at this point. :rotlaugh:

[andreygk]

I have vaguely understood what the code you wrote does and how it works. I tried to adapt it to another Primary Key in a different form. This time the format is slightly easier - It's A001 to Z999.
This is how i thought adapting the code should work. But it is not working.


Private Sub cmdNew_Click()
Dim a As String
DoCmd.GoToRecord , , acLast
a = [Payment ID]
If Len(a) < 4 Then
a = "A000"
End If
If Val(Right(a, 3)) < 999 Then
If Val(Right(a, 3)) < 100 Then
If Val(Right(a, 3)) = 99 Then
a = Left(a, 3) & CStr(Val(Right(a, 3)) + 1)
Else
a = Left(a, 3) & "0" & CStr(Val(Right(a, 3)) + 1)
End If
Else
a = Left(a, 3) & CStr(Val(Right(a, 3)) + 1)
End If
Else
If Mid(a, 3, 1) < "Z" Then
a = Chr(Asc(Left(a, 1)) + 1) & "000"
End If
End If
DoCmd.GoToRecord , , acNewRec
[Payment ID] = a
End Sub

Let me know why it didn't work and what exactly is wrong and how i can fix it or where i can get help on how to fix it.
Sorry about it looking so messy, i copied and pasted from Microsoft VisualBasic.

[andreygk]

I also get an error-time message "2105" which is regarding the DoCmd.GoToRecord , , acNewRec. I tried changing it to acNext but that didn't help.

[Tommy]

I am looking at this but are you using VB or are you using VBA in Access. There is a major difference. Mainly how did you get
DoCmd.GoToRecord , , acLast
to work?

Still looking.

[andreygk]

Sorry about the uncertainty, i'm definately using VBA. I can get the code to increment as follows but that's not what i want it to do - A001, A002, A003, A004, A005, A006, A007, A008, A009, B000 and so on.
DoCmd.GoToRecord, , acLast works fine because it looks at the record which is currently open. I don't know why DoCmd.GoToRecord, ,acNewRec isn't working.

[andreygk]

If i use this code:



Private Sub cmdNew_Click()
Dim a As String
DoCmd.GoToRecord , , acLast
a = [Payment ID]
If Len(a) < 4 Then
a = "A000"
End If
If Val(Right(a, 2)) < 999 Then
If Val(Right(a, 2)) < 100 Then
If Val(Right(a, 2)) = 99 Then
a = Left(a, 2) & CStr(Val(Right(a, 2)) + 1)
Else
a = Left(a, 2) & "0" & CStr(Val(Right(a, 2)) + 1)
End If
Else
a = Left(a, 2) & CStr(Val(Right(a, 2)) + 1)
End If
Else
If Mid(a, 2, 1) < "Z" Then
a = Chr(Asc(Left(a, 1)) + 1) & "000"
End If
End If
DoCmd.GoToRecord , , acNewRec
[Payment ID] = a
End Sub

The ID is incremented until A009 and then i get an error message saying:
Run-time error "3163": The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.

[Tommy]

First we will fix the Indexing. Next will be the new record.

Private Sub cmdNew_Click()
Dim a As String, chkVal As Integer
DoCmd.GoToRecord , , acLast
a = [Payment ID]
If Len(a) < 4 Then
a = "A000"
End If
If Val(Right(a, 3)) < 999 Then
If Val(Right(a, 3)) < 100 Then
' there is a need for a variable here due to it
' gets confusing for some one else or you in a
' week or 2 (me too btw)
chkVal = Val(Right(a, 3)) + 1
If chkVal < 10 Then
a = Left(a, 1) & "00" & CStr(chkVal)
ElseIf chkVal < 100 Then
a = Left(a, 1) & "0" & CStr(chkVal)
Else
a = Left(a, 1) & CStr(chkVal)
End If
' If Val(Right(a, 3)) = 99 Then
' a = Left(a, 3) & CStr(Val(Right(a, 3)) + 1)
' Else
' a = Left(a, 3) & "0" & CStr(Val(Right(a, 3)) + 1)
' End If
Else
' problem here do a search on Left
' you only have the 1 character you need the
' other 3 are supposed to be numbers
a = Left(a, 1) & CStr(Val(Right(a, 3)) + 1)
' a = Left(a, 3) & CStr(Val(Right(a, 3)) + 1)
End If
Else
If Mid(a, 3, 1) < "Z" Then
a = Chr(Asc(Left(a, 1)) + 1) & "000"
Else 'You will have to do something if the "Z" is there
' complain, throw a fit, stop
End If
End If
DoCmd.GoToRecord , , acNewRec
[Payment ID] = a
End Sub

[Tommy]

Run-time error "3163": The field is too small to accept the amount of data you attemped to add. Try inserting or pasting less data.

3 lefts + 3 rights = 6 not 4 :)

[andreygk]

In order to keep the incrementation working until Z999 would it be better to do...


If Mid(a, 3, 1) <= "Z" Then
a = Chr(Asc(Left(a, 1)) + 1) & "000"
Else


Or would it work for Z regardless of the "="?

[Tommy]

My Bad:mkay

I blew right past that. The Mid function is for picking a string out of the middle of a string. Right now we are only concerned with the first or leftmost character here.


If Left(a, 1) < "Z" Then
a = Chr(Asc(Left(a, 1)) + 1) & "000"
Else 'You will have to do something if the "Z" is there
'complain, throw a fit, stop
End If

[andreygk]

Would this work?



If Left(a, 1) < "Z" Then
a = Chr(Asc(Left(a, 1)) + 1) & "000"
Else
stop
End If

Would that stop the incrementation when it reaches Z999 so that the end user cannot add a new record???

[Tommy]

That is for you to determine. :whip Do you really want to limit the database?:banghead:

I would not suggest a Stop though. The user will be extremely upset because they couldn't add the record, but to get no message as to why, or anything? and then the application just stops for no apparent reason.:bug:

OHHHH no that is a phone call I don't want to get.:(

I think for now I would have a message box saying that the database has reached it's limits, no new records can be added.:yes and don't forget to skip the

DoCmd.GoToRecord , , acNewRec
[Payment ID] = a

So now what would you like to do?

[andreygk]

That's what i was going to do, use a message box, because that's how my incrementation of just digits works. I have no other requests at the moment. Any other coding i plan on doing i know how to do, e.g. make fields invisible onClick. Thanks for your help. If i'm stuck on anything else i will post on this thread.
Cheers

[Tommy]

Have a good one :)

[andreygk]

Hey, it's been a while, luckily.

Everything with the system is working fine, i'm using your tips and the access help can be quite helpful.
Anywho i was wonderin if you knew how to make your own input mask, for example: the system stores credit card numbers and i want the input mask to control how the data is shown. the credit card number consists of 16 numbers but i want the data to be shown as ************1234 instead of 1234123412341234.

Thanks.