Results 1 to 2 of 2
  1. #1
    Join Date
    May 2009
    Posts
    1

    Unanswered: Creating a Customer ID?

    For a school project we have to make a form where customers enter their personal details, and it gets saved in a database. I've got the textboxes and they work fine. (Yes I know this method isn't very good, but we have to do it or we lose marks)

    I've also got a booking number and customer ID textbox (which isnt visable to the user) to the bottom of the form.

    For this project, I need to create a unique CustomerID for the user, with "3 letters from their last name, and a 4 digit number". However, there is already some Customer's in the database, so I don't want to muck them up either.

    From looking on the internet, I've come up with the code:
    Left("LastName",3)(Int(Rnd()*10000),"0000")

    Is this correct? If not, what should it be?

    What do I do now? Where do I put it? How do I get it to work?

    I honestly have no clue with databases. Anything step-by-step would be awesome.

    I've been looking for a solution for the past 4 hours, but haven't had any luck, it's really starting to stress me out, as we haven't learnt anything like this in class before.
    Last edited by Chlozo; 05-06-09 at 16:27.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what you probably need to do is create a function which gets the next available number

    that function should find the current highest number for that prefix

    so you need to run a query which finds what accounts are set up for that prefix. Have a look at the like verb, the order by and top/limit verb

    if no accounts are found return 1
    if an accounts are found then find the highest suffix, add one to it and return that.

    if you were implementing this in a multi user environment you would need to think about a way of dealing with two users trying to secure a new number for the same prefix

    using a random number approach will work, but you will need to wrap the call to test if the account still exists

    there may even be some code in the access codebank that handles this
    or look at posts dated a year ago
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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