Creating A/C Numbers

CharlesWhiteman

Registered User.
Local time
Today, 18:29
Joined
Feb 26, 2007
Messages
421
Hi All, I am using the following code which, combined with another bit of code which takes the first two letters of a company name, to generate an account number.

If Me.NewRecord Then
On Error Resume Next
Me!CompanyCode = Nz(DMax("[CompanyCode]", "TblDatabase"), 0) + 1
End If

What would be better is if the Db can look at the a/c number AB001 and if there are no other AB's give me a/c number AB002

any pointers most welcome and thanks.
 
hi,

say GetCode is the function used to get the first 2 letters, you could do the following to get the whole code:

CompCode = GetCode & Format(DCOUNT("*","[TblDatabase]","Left([CompCode],2) = '" & GetCode & "'") + 1, "000")

HTH,
Chris
 
I modified and used the code, as shown below but for some reason doesnt recognise that the field AccountNumber already has the same account code and doesnt show 002

Private Sub CompanyName_AfterUpdate()
Me.txtPartA = Left([CompanyName], 2)
Me.txtPartB = Me.txtPartA & Format(DCount("*", "[TblDatabase]", "Left([CompanyCode],2) = '" & txtPartB & "'") + 1, "000")
End Sub
 
Could you post the DB, as the code you have posted has introduced many extra questions that I'm not prepared to second-guess without enought information? you can strip out the data, I just really need the form and the table structure.
Hazarding 1 wild guess, I'm assuming that txtPartA is just the 2 letter bit, txtPartB is just the numeric code and CompanyCode holds the full code in the format AA001. On that assumption, I would recode as follows - look carefully as it is subtly different near the end of the line (The Left(companyCode,2) is compared to txtPartA not txtPartB - using txtPartB does not make sense as it is not 2 letters long):
Code:
Me.txtPartB = Format(DCount("*","[tblDatabase]","Left(CompanyCode],") = '" & txtPartA & "'") + 1, "000")
If txtPartB is the whole code just put instead:
Code:
Me.txtPartB = txtPartA & Format(DCount("*","[tblDatabase]","Left(CompanyCode],") = '" & txtPartA & "'") + 1, "000")

If this is not right, post the db or I'm not going to even try again.

Chris
 
Last edited:
A account number (per say) should be distinct from any other number be it strictly numeric of even alphanumeric. Because you are incrementing the number it really doesn't matter if AB comes up again since you are incrementing the number by one every time AB arises.

If you are going to use the first two letters of a company name then you can have account numbers that appear as:

AB001
CD001
DS001
AB002
CC001
CD002
etc....

Nothing wrong with that. Each account number remains distinct or unique to a specific customer.

Within the AfterUpdate event for the CompanyName entry Field:
Code:
If Me.NewRecord And IsNull(Me.CompanyName) = False Then
   Dim Chars As String
 
   Chars = UCase(Left$(Me.CompanyName, 2))
   Me!CompanyCode = Chars & Format(Replace(Nz(DMax("[CompanyCode]", _
                               "tblDatabase", "Left([CompanyCode],2)='" & _
                               Chars & "'"), 0), Chars, "") + 1, "##000")
End If

.
 
Last edited:
Thanks for your replies, I am working on the coding, time allowing, and will feedback.
 

Users who are viewing this thread

Back
Top Bottom