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

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.

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

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. :)

Great

Thanks

Gary

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