Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2007
    Posts
    7

    Unanswered: VB Help - Generating a Customer ID

    Hello

    I'm trying to get a form to generate (with VB) a customer ID based on a surname and a number.

    The form (linked to table - "Customer Details") will have the surname entered into the field "Surname". Then in the field "Customer ID" I want it to generate an ID based on the first 4 characters of the surname + a two digit number.

    So for example the 1st 'Smith' would be SMIT01, but if there was already a SMIT01 in "Customer Details" I want it to automatically put SMIT02 etc. Can anyone help? I've played about with DLookup etc but my knowledge is very limited!

    Thanks!
    Garry

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    A couple of suggestions...

    1. First, if Customer ID is not a primary key in the table, make it one so duplicates cannot be created (so it will never save two SMIT01 ids.)

    2. A little bit of code writing might make this easier to do. Since you're always using the first 4 characters of the surname plus 2 digits, it makes it easier. For example, you might have some code which opens a recordset based on the first 4 character match (note: I did not check the syntax on this and I used ADO code but you should get the idea)....

    Dim strLCusID as string
    strLCusID = left(me!SurName,4)
    Dim strSQL as string
    strSQL = "Select CustomerID from MyTableName where left([CustomerID],4) = ' " & strLCusID & " ' " (I put spaces between ' and " to show which punctuation to use.)
    Dim rs as adodb.recordset
    set rs = new adodb.recordset
    rs.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockReadOnly
    if rs.eof and rs.bof then
    me!CustomerID = strLCusID & "01"
    else
    Dim intCounter as integer
    intCounter = right(rs!CustomerID, 2)
    intCounter = intCounter + 1
    me!CustomerID = strLCusID & Format(intCounter, "00")
    End if
    rs.close
    set rs = nothing
    Last edited by pkstormy; 12-09-07 at 20:15.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8

    Thumbs up

    Try some like this


    This is just and idea off top of head no testing

    check = 1

    do until check = 0
    Look4 = Ucase(left("[customer Details]",4) & format(Check,"00"))

    CheckThis = dcount("*","Customer Table","[Customer ID] = '" & look4 & "'")

    if Checkthis = 0 then ' new customer
    Exit loop
    check = 0
    else
    check = check + 1
    end if
    loop
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    2 examples on how to do it although I don't particularly like using the dcount. Myle - you may want to show how to generate the new Customer ID in your code and I'm not sure I'd use the wording: Check as a variable.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    dcount may be fine but it will be a performance pig if say you are looking for FRUS, and you have already allocated FRUS00 to FRUS90.

    Mind you Id suggest you index the account number and use a descending sort order on PKStormy's SQL. then the first record found is the highest ranking existing account... if nothing is found then your account number is 01
    ie
    strSQL = "Select CustomerID from MyTableName where left([CustomerID],4) = ' " & strLCusID & " ' ORDER BY CUSTOMERID DESC"

    Myles answer would be great if say you have to re-allocate the account numbers...

    BTW unless you know that you will never ever have more than 99 accounts then Id suggest using say 3 digits or possibly more

    there are a heck of a lot of Jones, Smiths and Browns in the English speaking world, and Im certain each other language has its own variations.. I dread to think of the number of Chinese family names there are, and how many potential accounts you may have.,

    You need to extend PKStormy's answer in my books to handle the situation where the account number over flows.. But that's be the silution Id want to run with first.

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Thanks healdem. I knew I forgot something important with the strSQL select. ORDER. (Guess I should've tested it verses doing it blindly).

    I'm still wondering though if the reason for doing this is to prevent duplicates. If so, it won't necessarily accomplish the task. The same SMITH record can be entered twice and you'd end up with a SMIT01 and a SMIT02 where both of them represent the same SMITH.
    Last edited by pkstormy; 12-12-07 at 01:00.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    .. theres no automated way round that as far as I'm aware
    thats going to have to be down to a manual procedure to check that the prospective customer is actually a new account. Otherwise you are going to be trying to use all manner of algolrythms to check for misspellings, or information missing or what ever. if the customer supplied perfect information then you may be able to automate a match.

    Personally Id expect the app to request the customers name, then do a search and display all matching surnames (probably including a Soundex or Izy's variation on the theme) and offer the user a chance to see if the account already exists, then if not I'd go on to create the new account.

    But as ever the devil is in the detail......

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •