[AccessD] Numbering Different Criteria

The numbering scheme has been used for awhile now & kept in a paper log
(ooohh). We need to keep the same numbering for history and searching for
documents.
 
Anyway, how do you create a form that will look at the last sequence of
numbers and assign the next number based on all the combinations, Document
Number, Section, Revision, & Change?
 
What I was thinking - from a dropdown list use the document number to filter
for only the numbers for documents (AB1234). I was not able to concatenate
the document number & the section & still filter the records for only Civil
(AB1234-C). I tried the below code (didn't work)
 
' Find the record that matches the control.
    Me.RecordsetClone.FindFirst "[DocNo] = '" & Me![cboDocNum] & " AND
[Section] = " & Me![cboDocNum] & "'"
 
How do I have it determine what the last (or the next) revision & change
letter is for the Civil document AB1234?
 
Virginia
 

-----Original Message-----
From: John Colby [mailto:jcolby at colbyconsulting.com]
Sent: Thursday, May 15, 2003 8:13 AM
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] Numbering Different Criteria


Virginia,
 
What you are describing are simply properties of a document, and as such
need their own field in the document table.  Once you have done that any of
the various pieces can be adjusted independently of any other.  Do NOT try
to deal with this as a single entity.  Assign the pieces, then pull the
pieces out and append them all together in a formatted string for the
"document number".
 
As far as answering the question "what is the next number in the sequence"
you need an algorithm to discuss how the pieces "roll over" into the next
field.
 
With no intimate knowledge of the numbering scheme, I would guess the first
two characters mean something, i.e. AB has a meaning, CD has a meaning etc.
Next the numeric portion.  Of course you have defined the single character
in the middle and everything after that.  Completely breaking it down at
least allows you to go to the people who thought up this scheme and discuss
this thing a piece at a time.  Get inside that pea brain and you will be
trapped... uh... be able to figure out where to go next.
 
John W. Colby
www.colbyconsulting.com 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Hollis,Virginia
Sent: Thursday, May 15, 2003 8:33 AM
To: 'accessd at databaseadvisors.com'
Subject: [AccessD] Numbering Different Criteria


I need to set up a way to assign sequential numbers based on different
criteria.
 
The first part of the number is a document number, it can be either AB1234
or CD6789, the next part is for the Engineering section (C for Civil,
M-Mechanical, E-Electrical). So the numbering scheme would be:
 
AB1234-C001 (tracking number - section, sequential number)
AB1234-C002
AB1234-M001
CD6789-E001
CD6789-E002
CD6789-M001
 
The numbers also contain revisions and change letters. If I want the next
Civil number for document tracking number AB1234, it would be:
 
AB1234-C003, Rev 0, Ch 0 (document number - section, sequential number,
revision, change)
then AB1234-C003, Rev 0, Ch A
or AB1234-C003, Rev 1, Ch 0
AB1234-C003, Rev 1, Ch A
 
I thought if I could have the form select the tracking number & the section,
I could use a query to designate what the next sequential number would be
based on the tracking number & the section. Then I couldn't get the two
different criteria to work together. 
 
   ' Find the record that matches the control.
    Me.RecordsetClone.FindFirst "[DocID] = '" & Me![cboDocNum] & " AND
[Section] = " & Me![cboDocNum] & "'"
 
Now I got to wondering how I would handle the Revision & Change Letter. Any
ideas how I can put all this together?
 
Virginia


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030515/09a6ded9/attachment-0001.html>