Help with Duplicate Account Numbers

[mosh]

Hey

I currently have this code which tells the user there is a an accout number already on the db:

Private Sub AccountNo_AfterUpdate()
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("Select * From Spreadsheet where AccountNo = '" & Me.AccountNo & "';")
If rs.RecordCount > 0 Then
MsgBox "This Account Number Is Currently Within The Database And Will Not Be Allowed.", vbInformation, "DD Cancellations"
Me.AccountNo = Null
End If
End Sub


I want a peice of code which instead allocates a letter to the duplicate account, so user enters a duplicate account, the account will then turn into 123456789A, upto 5 duplicates are allowed, so upto 123456789E. Once "E" has been reached accountnum box will turn null with msgbox. I tried out Case statements but that doesn't seem to work.

all help appreciated. Thanks.
________
Medical Marijuana Seeds

 

[mosh]

I'v tried this, but not working;

Private Sub AccountNo_AfterUpdate()
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("Select * From Spreadsheet where AccountNo = '" & Me.AccountNo & "';")
If rs.RecordCount > 0 Then
Public Var As String
Var = Right(Me.AccountNo, 1)
Case Var
Case "A"
Mid(Me.AccountNo, 1, Len(Me.AccountNo) - 1) = Me.AccountNo & "B"
Case "B"
Me.AccountNo = Me.AccountNo & "C"
Case "C"
Me.AccountNo = Me.AccountNo & "D"
Case "D"
Me.AccountNo = Me.AccountNo & "E"
Case Else
MsgBox "This Account Can't be Allowed"
Me.AccountNo = Null
End Select
End If
End Sub
________
Prilosec Lawyers

 

[RuralGuy]

Forgive me but I have to ask. Are you running low on numbers?

 

[mosh]

not quite sure what you mean? That account numbers consist of 10numbers. If a duplicate is entered a letter is placed after the REF number.
________
Web Shows

 

[RuralGuy]

Instead of asking them to use a different number you are allowing up to 6 people to basically "share" the same number. This complicates your task tremendously, which I don't mind by the way, as long as there is a valid reason for the user to need that number. Is it a number based on something else like there home phone or something that is easier for the user to remember? Why bend over backwards to let the user have a number they have chosen? I was just being flippant with my first post and there is a solution to your problem it you *must* go in that direction but it is somewhat complicated.

 

[mosh]

ok, here is the problem.

Currently an account is logged onto the db, the account may need to be actioned again but i would not want the previous account to be adjusted. So i though a solution could be to have a letter allocated to the end of accounts, if the account comes up in the next report and needs to be inputted then it will allocate a char to the end of the ref number.
________
PAXIL WITHDRAW

 

[RuralGuy]

I don't really understand what you just said but how do you propose to differentiate the "auction it again" from the user keyed in the number wrong transposing numbers? This sort of problem is best resolved with AutoNumbers. Treat the keyed in number like a LastName. The record is unique because of the AutoNumber and not because of the LastName. A report will show the problem to a human and the system should allow the user to change the keyed in number at will. Sorry to preach like this.

 

[mosh]

I understand what your saying, cant belive i didn't spot this. I have an autonumber ID allocated to each account number, Thanks Rural!!
________
Alisonsexy cam

 

[RuralGuy]

Outstanding! I think you will me *much* happier with this solution. Post back if you need any other "nudging".