Engineering Document Manager - finding next available number

[mmaurigi]

I have developed a Document Manager for my company. It seems to be working pretty good, but I recently realized one feature that it should have.



The document numbering conventions are in the form of XX-YYYYYY-ZZZZ, where XX is a Document Category selected from a dropdown, YYYYYY is a sequential number, and ZZZZ is a Document Type selected from a dropdown. The user currently enters information about the document including the document number into a User Form and that information is placed in a Table called DocumentData. The feature that the Document Manager needs is the ability to look at the DocumentData table and provide the next available sequential number (YYYYYY) for documents that have XX = ST.

Can someone help me with the code to achieve this.

Thank you

[ssanfu]

The document numbering conventions are in the form of XX-YYYYYY-ZZZZ, where XX is a Document Category selected from a dropdown, YYYYYY is a sequential number, and ZZZZ is a Document Type selected from a dropdown

You have 3 fields in the table for the document numbering? (I hope)
Is the sequential number ALWAYS 6 numbers? (the number zero padded?)

Would you post a couple of examples of document numbers?

[mmaurigi]

I have fields in the table for Document Category, Document Type and the Document Number. I do not have a field for the Sequential Number only. The number is always 6 numbers and is zero padded. Currently, we are up to 000382.

Some example of documents are:

ST-000038-LYT01 (a standard layout drawing)
ST-000372-SPC01 (a standard specification drawing)
ST-000312-DET01 (a standard detail drawing)

I'd like the Document Manager to be able to tell me what the next available sequential number is in the ST Document Category.

Thanks.

[ssanfu]

I have fields in the table for Document Category, Document Type and the Document Number. I do not have a field for the Sequential Number only.

Whoops!!!
"ST is the Document Category (understood)
"LYT01" is the Document Type (understood)

"000038" is the Document Number , aka Sequential Number???
Is not the Document Number the same as the Sequential Number?

[mmaurigi]

"Document Number" is a combination of Document Category, Sequential Number, and Document Category.

[ssanfu]

Is this close?

[mmaurigi]

This is very close. Is there a way to get the next sequential number to show up with having to click on the button to get it? For instance, can the next sequential number be displayed once the program is opened, and then updated when a new entry is saved?

[ssanfu]

Is this closer??



In the form "ContinousForm"

Select a category and the next "number" in sequence is displayed.
Then select a type.
Move to a different record and the doc number is generated.



------------------------------------------------------
In the form "SingleForm"

Works the same way, but you don't get to see the Document number until you look at the table or the other form.


------------------------------------------------------

IMO, there should be 3 fields in the table: a field for the Document Category, a field for the Document Type and a field for the 6 character Document Sequence (Number).

The "document number" that looks like "ST-000038-LYT01" can be generated on-the-fly in a query at any time.

[Micron]

I have some experience in document databases, so I'll chime in (sorry if not appreciated). If there are multiple users who might create a document at the same time for ST, I do not think I would do it the way I envison you've done. I say envision because I can't open the db (sigh, again....). If no method was created to ensure each user did not load the same 'next document number' for ST, when each moves to save the record, they will either create the same 6 digit number or generate an error. I agree with the notion of having three fields for this, but don't see anything that tells me if this is a 3 field compound index or if # simply cannot be duplicated in its own field. In the first case, the next number available would be sequential to ST-000382 or whatever, but is AB-000382 permitted?

I'm not sure I agree with showing the number before it is committed, unless there is some reason the user would not proceed if it wasn't to their liking. Rather, my approach would be to create the number, then show it, provided a quick check for duplication found that at the last millisecond, it was unique.
Sorry if I'm intruding.

[ssanfu]

@Micron

Here is a version in A2000 format. Couldn't seem to save it as A2007.

I do have the "doc number" calculated in the form before update event, but I didn't check for duplication of the "number". (my bad)

You raised good points.....

[Micron]

Thanks, Steve. Very kind of you.

I created
- a simple module for checking combos for values to show how I try to reuse functions when possible. I could have just put it in the form code.
- a new form (frmNewDoc)
- a new table (tblDocNum) to split the number portion from the rest of the record
There's only about 27 lines of code; mind you, there's no error handler.

When the create button is clicked, the next number is calculated and immediately appended to the table before shown to the user, then all 3 parts are assembled in the form textbox. This allows each field to be separated, for as we know, field parts should be assembled in forms and reports.

I think it would be virtually impossible for two users to simultaneously to push the button at the exact same time. All assumes that each component of the document number is text. Works for me, at least, but I didn't test it as if I was presenting to my boss . Not exactly a robust document db, but it's one answer to the original post.

SeuNum_A2K_2.zip

[mmaurigi]

Micron:

Your module seems to be what I'm looking for. I'd like to send the database as it is now. How can I include it in a reply?

[Micron]

You shouldn't need to. You should be able to import the module mdlCommonFunctions into your project (Get External Data > Import > Access in 2007 ribbon).
You can also import the form and redesign it or copy and paste the code into your form. For the latter, I presume your form object names would be different, so you'd create the same events for your controls and paste the code between the Sub / End Sub statements so you don't duplicate them.

If you need to upload a copy, compact it, zip it and use the Go Advanced button in your reply to find the attachment link on the reply toolbar. Note that if your copy contains features not supported by 2007, I won't be able to open it, which is why I suggested the other methods. Or maybe someone here can take my code from my uploaded db and merge it with your db.

[ssanfu]

I would add the lines in blue.....

<snip>
    sql2 = "INSERT INTO tblDocNum (DocCategory,DocType,DocNum) VALUES ('" & svCat
    sql2 = sql2 & "','" & svType & "','" & svNum & "')"
    db.Execute sql2, dbFailOnError
    Me.txtDocNum = svCat & "-" & svType & "-" & svNum

    'clean up
    rs.Close
    Set rs = Nothing
    Set db = Nothing
End Sub

I'm just sayin......

[Micron]

You are 158% correct! I forgot a basic coding practice - set to Nothing whatever you Set.
OK, it was late, right?
EDIT: and close recordsets.