code req'd to specify numbering system

[retrieve]

G'day,

I am setting up a document management system for several projects and am required to follow the filing system already used in the company.

I need help to write some code to determine the number assigned to a document based on its type and then sequentially increase as documents are added once the type is determined by using a drop down list.

an example number is a follows:
A80273.8200.cjb


The process of adding documents is as follows:

1. Documents are selected as either a Drawing or a Document etc- this determines the first letter in the sequence (A for a drawing, D for a document, C for correspondence).

2. The project number that the document relates to is selected next from a dropdown list. (eg the 80273 above)

3. The next number will depend on the type of drawing or document eg for a drawing, if the drawing is a civil drawing then the numbering sequence will start at 1000-1999 for the project, or if the drawing is a mechanical drawing then the sequential number will start at 2000 - 2499 etc...

4. There is an option to put the initials of the originator at the end eg cjb.

For each different project the numbers will need to have their own sequence, ie for project number 1234, civil drawings need to start sequentially at 1000, and if your next entry is for project 5678 then the civil drawings for this project also needs to start sequentially from 1000.

so, being a mere civil engineer, doing this in Access is a bit of a challenge for me as my programming skills are extremely basic. If I can't sort out how to do these numbering systems then I will have to resort to excel, which I would rather not do!

hope that one of you can help

regards,
~Cath

 

[Blorf]

It seems to me there are two ways to do this. One is to have a docs table that you check to see the last number used, and go to the next for the project in question. This is risky because you may some day purge records.

Next is just to have a project and number table that keeps tabs on the last or next available number for each project. This is Probably safer.

Do you have anything built so for that we can expand on, or are you starting from scratch?

 

[Lightwave]

Retrieve,

Some ideas.

Create two tables
One called Project number with the fields ID (autonumber) and Project Number which is the number relating to your subjective proj no system
ID is the primary key and will be created automatically

Second table called project docs this will have the following fields
ID - a primary key set to autonumber
Project Number - set to long integer will come to this soon
Document Type - this is whether its a drawing or document etc. It only needs to be long enough to hold your prefix eg A for drawing.
Or AM for specific mechanical drawings
Name - person or engineer entering the information or whom doc belongs to.

Now go into relationships and relate the two tables so that the project numbers are related in one to many relationship.

Now create a form and a sub form that shows both the header table and the document type sub form in it.....

I would then set up integers to count each of the differing document types of data stored with each project. You mention that for drawings the mechanical and civil drawings have differing drawing systems. You need to tell the database whether a drawing is mechanical or civil therefore the document type field within the project documents tble will have to have a subdivision of the document type. Eg the user will enter AM for Mechanical Drawings and then AC for Civil Drawings Documents would be noted as D within this field.

Example code for counting the number of docs within the subform related to a particular project would be similar to this

Dim intAM as integer

intAM = Dcount("[ID]","tblNamingofDocs","[Document type]='AM'")+2000

What this does is counts the number of times AM appears within the Document type field (all items in square brackets relate to a field) within the sub form "tblNamingofDocs" and adds it to 2000.

Note this 2000 will depend on your number range for your specific type of document in this case mechanical drawings. For civil drawings this would be 1000.

You then string all your fields together using yet more variables and place in a field ...

Haven't worked out all the details but this would work... maybe take it to a friend if you are confused but with tweaking is a start.

Good Luck

 

[retrieve]

thanks

Hi guys,

thanks for your comments, I will look at them and see how I go - seems a bit complicated to me!

cheers