Consulting

Results 1 to 6 of 6

Thread: SOLVED: Editable Numbering

  1. #1
    VBAX Tutor GaryB's Avatar
    Joined
    Jun 2004
    Location
    Stockton, California
    Posts
    270
    Location

    SOLVED: Editable Numbering

    I really need some help with this. It has been driving me nuts, so I'm hoping that someone out there may have a brilliant solution.

    I have a form that creates job folders for us and currently it is autonumbered. What I need it to have sequence numbering that is editable so I can modify and duplicate the number. For example, if the number is 12345 and the next number is 12346, I sometimes need to change the next number to 12345b and so on. What happens is, we sometimes get jobs that are multi-part and I need to create, lets say 3 job folders, that need to be numbered 12345A, 12345B, 12345C. I don't need the lettering automated, just the numbering editable so I can change it to suit our needs. Oh, one more thing, I have about 6000+ records that are already numbered and I can't lose that numbering. So whatever we come up with has to continue the sequence from the last numbered record. Easy, Huh! - HA. Well, if anyone feels up to this challege, it would certainly be greatly appreciated.

    SOLVED THE PROBLEM: IN THE TABLE -ORDERENTRY- I CHANGED THE AUTONUMBER TO A TEXT BOX.
    THEN IN THE FORM I ADDED =DMax("JOBID","ORDERENTRY")+1 TO THE DEFAULT VALUE UNDER DATA. WHAT THIS DID: IT ALLOWED THE AUTONUMBERING TO CONTINUE FROM THE LAST NUMBER I ENTERED AND ALSO ALLOWS THE NUMBER TO BE EDITABLE.
    NOTE: IF YOU ADD A LETTER AFTER THE NUMBER WHEN THE NEXT RECORD COMES UP IT WILL SHOW ERROR IN THE NUMBER FIELD. ALL YOU HAVE TO DO IS TYPE IN THE NEXT NUMBER AND IT WILL CONTINUE NUMBERING FROM THAT INPUT.

    GARY

    Thanks

    Gary
    Last edited by GaryB; 06-30-2004 at 03:23 PM. Reason: PROBLEM SOLVED

  2. #2
    VBAX Tutor SJ McAbney's Avatar
    Joined
    May 2004
    Location
    Glasgow
    Posts
    243
    Location
    I would say the first part of this - as with all databases - is to get the table structure correct.

    Here you have one file number and the possibility of many dependants so we have immediately identified a one to many relationship. Ergo we need two tables for this:

    tblJobs
    JobID (autonumber - primary key)
    JobName
    etc.

    tblSubjobs
    SubjobID (Autonumber - primary key)
    JobID (foreign key to tblJobs)
    Subjob etc..

    Can you enlighten me as to what you have?


    It may, even, be prudent to introduce a third table in order to make this a many-to-many relationship which - inevitable - may be best.

  3. #3
    VBAX Tutor GaryB's Avatar
    Joined
    Jun 2004
    Location
    Stockton, California
    Posts
    270
    Location
    the main table for the database is called order entry. This form as well as others reports to this table. In the table resides job number which is the primary key and is autonumbered. We have an estimating program that is access version? based that contains all the customer history, invoice etc. I have linked tables from my database to it to eliminate duplicating entries ie... customer info etc.. so the query for this form has two tables in it now. One order_entry not a linked table and customer1 which is a linked table.

    Gary

  4. #4
    VBAX Tutor SJ McAbney's Avatar
    Joined
    May 2004
    Location
    Glasgow
    Posts
    243
    Location
    GaryB, I started an example on this but I was in a hurry to catch my train at 5 that I never emailed it home. I'll get back to looking at it tomorrow.

  5. #5
    VBAX Tutor GaryB's Avatar
    Joined
    Jun 2004
    Location
    Stockton, California
    Posts
    270
    Location
    Great

    Thanks

    Gary

  6. #6
    VBAX Tutor GaryB's Avatar
    Joined
    Jun 2004
    Location
    Stockton, California
    Posts
    270
    Location
    SOLVED THE PROBLEM: IN THE TABLE -ORDERENTRY- I CHANGED THE AUTONUMBER TO A TEXT BOX.
    THEN IN THE FORM I ADDED =DMax("JOBID","ORDERENTRY")+1 TO THE DEFAULT VALUE UNDER DATA. WHAT THIS DID: IT ALLOWED THE AUTONUMBERING TO CONTINUE FROM THE LAST NUMBER I ENTERED AND ALSO ALLOWS THE NUMBER TO BE EDITABLE.
    NOTE: IF YOU ADD A LETTER AFTER THE NUMBER WHEN THE NEXT RECORD COMES UP IT WILL SHOW ERROR IN THE NUMBER FIELD. ALL YOU HAVE TO DO IS TYPE IN THE NEXT NUMBER AND IT WILL CONTINUE NUMBERING FROM THAT INPUT.

    GARY

Posting Permissions

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