Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2004
    Posts
    83

    Unanswered: Very frustrated with this

    Our database has a unique field that stores a text data. When the users click on a button, info is grabbed from a bunch of other fields to popuate the text one. Now since it is unique I do a Dcount in the code and append the approprate number onto the end

    Now this has all been working fine...until now. Somone deleted one of the records. So here's what happened (if you havn't already guessed)...

    Lets say the last record in the text field is BOB18

    - Info in gathered, result is BOB
    - DCOUNT is preformed, there are 17 records now
    - count is incrimented by one
    - result is BOB18

    Which means that the record cannot be saved b/c it conflicts with the other BOB18.

    So looks like I will have to find a way to incriment though each individual record and check the number on the end to find a free didgit. Seems easier said than done though, I've been pulling my hair out on it for the last week. Is there a simple solution I'm just overlooking?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you could write a function to retrieve the maximum value of the child record, return increment it by one.

    You cold get the value either by sorting in descending order or MAX predicate.

  3. #3
    Join Date
    Mar 2004
    Posts
    83
    What I would like to get to is where if they delete #5, then the next suggested then becomes 5

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so write your function to find the first available number, sort the recordset in ascending order and then iterate through the recordset to find the first missing number

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Is there a specific reason why you would like the record to be replaced? sounds like you have some bizarre schema going on...


    Anywho, I would probably attack this by parsing the numeric portion of the key and incrementing THAT value, then putting your prefix back on.

    Actually, I would forgo the alpha-numeric key altogether and take better advantage of the rdbms capabilities at your fingertips...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Mar 2004
    Posts
    83
    Actually, I would forgo the alpha-numeric key altogether and take better advantage of the rdbms capabilities at your fingertips...
    My fingertips are fine, it's the users who are screwing around with it

    It's a database listing of PCs around an area. And the code creates a hostname based on the physical building/location. The combination is easy enough to find the exact spot of the PC but complicated enough so the users foramt it wrong. Thats why we have a button to generate it for them.

    So how would I go about this,
    - Run a query to return all the records based on the prefix, sort it
    - Loop through looking to see if theres a number out of place

    The guy who designed the naming convention put three didgits at the end incase there was ever 100 PCs in an area. The actual problem im running into is (maybe just been looking at it too long) that I keep getting an error compairing 001 to 010. There must be a trim function to hack off the zero(s) at the start

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    write a function in vba with an string return variable
    pass it the prefix
    open a recordset limited to the prefix sorted in ascending order
    establish a testvar say testvar=1
    while recordset.eof =false
    find the first missing number (if testvar<recordset.var then exit loop
    testvar=testvar+1
    end while

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by High_D
    It's a database listing of PCs around an area. And the code creates a hostname based on the physical building/location.
    MORE TABLES!!!

    You would be far better off creating a seperate table for buildings/locations and referencing it with a foriegn key. Then you can make it idiot proof by providing a combo box with all the locations. That's not to mention the additional flexibility you'll gain in reporting, say for instance you want to produce an inventory by location... with your current setup you'll have to parse out the alpha part of the key and group based on that field with varying degrees of accuracy. With another table it's a matter of a simple join statement.
    Last edited by Teddy; 11-30-04 at 15:58.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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