<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<META content="MSHTML 5.00.3314.2100" name=GENERATOR></HEAD>
<BODY>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN class=260392713-15052003>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.</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=260392713-15052003></SPAN></FONT> </DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=260392713-15052003>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?</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=260392713-15052003></SPAN></FONT> </DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN class=260392713-15052003>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)</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=260392713-15052003></SPAN></FONT> </DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN class=260392713-15052003>' Find
the record that matches the control.<BR>
Me.RecordsetClone.FindFirst "[DocNo] = '" & Me![cboDocNum] & " AND
[Section] = " & Me![cboDocNum] & "'"</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=260392713-15052003></SPAN></FONT> </DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN class=260392713-15052003>How do
I have it determine what the last (or the next) revision & change
letter is for the Civil document AB1234?</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=260392713-15052003></SPAN></FONT> </DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=260392713-15052003>Virginia</DIV>
<DIV><FONT face=Arial size=2><SPAN
class=800250212-15052003></SPAN></FONT> </SPAN></FONT><FONT color=#0000ff
face=Arial size=2><SPAN class=260392713-15052003></SPAN></FONT></DIV>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV align=left class=OutlookMessageHeader dir=ltr><FONT face=Tahoma
size=2>-----Original Message-----<BR><B>From:</B> John Colby
[mailto:jcolby@colbyconsulting.com]<BR><B>Sent:</B> Thursday, May 15, 2003
8:13 AM<BR><B>To:</B> accessd@databaseadvisors.com<BR><B>Subject:</B> RE:
[AccessD] Numbering Different Criteria<BR><BR></DIV></FONT>
<DIV><SPAN class=093220113-15052003><FONT color=#0000ff face=Arial
size=2>Virginia,</FONT></SPAN></DIV>
<DIV><SPAN class=093220113-15052003><FONT color=#0000ff face=Arial
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=093220113-15052003><FONT color=#0000ff face=Arial size=2>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".</FONT></SPAN></DIV>
<DIV><SPAN class=093220113-15052003><FONT color=#0000ff face=Arial
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=093220113-15052003><FONT color=#0000ff face=Arial size=2>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.</FONT></SPAN></DIV>
<DIV><SPAN class=093220113-15052003><FONT color=#0000ff face=Arial
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=093220113-15052003><FONT color=#0000ff face=Arial size=2>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.</FONT></SPAN></DIV>
<DIV><FONT size=2></FONT> </DIV>
<DIV><FONT size=2>John W. Colby<BR>www.colbyconsulting.com</FONT> </DIV>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV align=left class=OutlookMessageHeader dir=ltr><FONT face=Tahoma
size=2>-----Original Message-----<BR><B>From:</B>
accessd-bounces@databaseadvisors.com
[mailto:accessd-bounces@databaseadvisors.com]<B>On Behalf Of
</B>Hollis,Virginia<BR><B>Sent:</B> Thursday, May 15, 2003 8:33
AM<BR><B>To:</B> 'accessd@databaseadvisors.com'<BR><B>Subject:</B> [AccessD]
Numbering Different Criteria<BR><BR></FONT></DIV>
<DIV><FONT face=Arial size=2><SPAN class=800250212-15052003>I need to set up
a way to assign sequential numbers based on different
criteria.</SPAN></FONT></DIV>
<DIV><FONT face=Arial size=2><SPAN
class=800250212-15052003></SPAN></FONT> </DIV>
<DIV><FONT face=Arial size=2><SPAN class=800250212-15052003>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:</SPAN></FONT></DIV>
<DIV><FONT face=Arial size=2><SPAN
class=800250212-15052003></SPAN></FONT> </DIV>
<DIV><FONT face=Arial size=2><SPAN class=800250212-15052003>AB1234-C001
(tracking number - section, sequential number)</SPAN></FONT></DIV>
<DIV><FONT face=Arial size=2><SPAN
class=800250212-15052003>AB1234-C002</SPAN></FONT></DIV>
<DIV><FONT face=Arial size=2><SPAN
class=800250212-15052003>AB1234-M001</SPAN></FONT></DIV>
<DIV><FONT face=Arial size=2><SPAN
class=800250212-15052003>CD6789-E001</SPAN></FONT></DIV>
<DIV><FONT face=Arial size=2><SPAN class=800250212-15052003><FONT face=Arial
size=2><SPAN
class=800250212-15052003>CD6789-E002</SPAN></FONT></SPAN></FONT></DIV>
<DIV><FONT face=Arial size=2><SPAN class=800250212-15052003><FONT face=Arial
size=2><SPAN class=800250212-15052003><FONT face=Arial size=2><SPAN
class=800250212-15052003>CD6789-M001</SPAN></FONT></SPAN></FONT></SPAN></FONT></DIV>
<DIV><FONT face=Arial size=2><SPAN
class=800250212-15052003></SPAN></FONT> </DIV>
<DIV><FONT face=Arial size=2><SPAN class=800250212-15052003>The numbers also
contain revisions and change letters. I</SPAN></FONT><FONT face=Arial
size=2><SPAN class=800250212-15052003>f I want the next Civil number for
document tracking number AB1234, it would be:</SPAN></FONT></DIV>
<DIV><FONT face=Arial size=2><SPAN
class=800250212-15052003></SPAN></FONT> </DIV>
<DIV><FONT face=Arial size=2><SPAN class=800250212-15052003>AB1234-C003, Rev
0, Ch 0 (document number - section, sequential number, revision,
change)</SPAN></FONT></DIV>
<DIV><FONT face=Arial size=2><SPAN class=800250212-15052003>then
AB1234-C003, Rev 0, Ch A</SPAN></FONT></DIV>
<DIV><FONT face=Arial size=2><SPAN class=800250212-15052003>or AB1234-C003,
Rev 1, Ch 0</SPAN></FONT></DIV>
<DIV><FONT face=Arial size=2><SPAN class=800250212-15052003>AB1234-C003, Rev
1, Ch A</SPAN></FONT></DIV>
<DIV><FONT face=Arial size=2><SPAN
class=800250212-15052003></SPAN></FONT> </DIV>
<DIV><FONT face=Arial size=2><SPAN class=800250212-15052003>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. </SPAN></FONT></DIV>
<DIV><FONT face=Arial size=2><SPAN
class=800250212-15052003></SPAN></FONT><FONT face=Arial size=2><SPAN
class=800250212-15052003></SPAN></FONT> </DIV>
<DIV><FONT face=Arial size=2><SPAN class=800250212-15052003> '
Find the record that matches the control.<BR>
Me.RecordsetClone.FindFirst "[DocID] = '" & Me![cboDocNum] & " AND
[Section] = " & Me![cboDocNum] & "'"</SPAN></FONT></DIV>
<DIV><FONT face=Arial size=2><SPAN
class=800250212-15052003></SPAN></FONT> </DIV>
<DIV><FONT face=Arial size=2><SPAN class=800250212-15052003>Now I got to
wondering how I would handle the Revision & Change Letter. Any ideas how
I can put all this together?</SPAN></FONT></DIV>
<DIV><FONT face=Arial size=2><SPAN
class=800250212-15052003></SPAN></FONT> </DIV>
<DIV><FONT face=Arial size=2><SPAN
class=800250212-15052003>Virginia<BR></DIV></BLOCKQUOTE></BLOCKQUOTE></SPAN></FONT></BODY></HTML>