<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=us-ascii">
<TITLE>Message</TITLE>
<META content="MSHTML 6.00.2726.2500" name=GENERATOR></HEAD>
<BODY>
<DIV><SPAN class=592535017-09072003><FONT face=Arial color=#0000ff
size=2>Welcome back, Ginny!</FONT></SPAN></DIV>
<DIV><FONT face=Arial color=#0000ff size=2></FONT> </DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN class=592535017-09072003>You
have to make a decision about who assigns the numbers: users or the system. It
reads as if you want the latter, but the problem may be that the existing
numbers may not conform to the rules. The solution is: who cares? Accept all
existing entries as valid and proceed from there.</SPAN></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=592535017-09072003></SPAN></FONT> </DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN class=592535017-09072003>IMO
you are best off normalizing these virtual columns into actual columns. Why?
Ease of coding. For example, given inputs such as:</SPAN></FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN
class=592535017-09072003></SPAN></FONT> </DIV>
<DIV><FONT><SPAN class=592535017-09072003>
<DIV><FONT face=Arial color=#0000ff size=2>DC-2101-0001</FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2>DC-2204-0001</FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2>DC-2301-0001</FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2>LA-2605-0001</FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2>LA-2605-0002</FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2>LA-2605-0003</FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2>NR-2605-0001</FONT></DIV>
<DIV><FONT face=Arial color=#0000ff size=2></FONT> </DIV>
<DIV><SPAN class=592535017-09072003><FONT face=Arial color=#0000ff size=2>IMO
these three parts belong in separate columns. It looks like the first column is
a table of a very few entries. The second column is... who knows? Input?
Derived? Doesn't much matter, but if derived it can be a lookup. Column 3 is
also derived, something like =Format(DMax("myPK", "myTable", "myParentPK = "
& nnnn), myLeadingZeroesFormat ). </FONT></SPAN></DIV>
<DIV><SPAN class=592535017-09072003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=592535017-09072003><FONT face=Arial color=#0000ff size=2>Write
a function that returns the next ChildID something like
this:</FONT></SPAN></DIV>
<DIV><SPAN class=592535017-09072003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=592535017-09072003><FONT face=Arial color=#0000ff
size=2>Function NextChildID( strPK as String, strTable as
String, strParentKey as String, lngParentKey as Long ) As
Long</FONT></SPAN></DIV>
<DIV><SPAN class=592535017-09072003> <FONT face=Arial
color=#0000ff size=2>Dim lngLastID as Long</FONT></SPAN></DIV>
<DIV><SPAN class=592535017-09072003> <FONT face=Arial
color=#0000ff size=2>lngLastID = DMax( strPK, strTable,
strParentKey & "=" &
lngParentKey )</FONT></SPAN></DIV>
<DIV><SPAN class=592535017-09072003> <FONT face=Arial
color=#0000ff size=2>NextChildID = lngLastID + 1</FONT></SPAN></DIV>
<DIV><FONT face=Arial><FONT color=#0000ff><FONT size=2><SPAN
class=592535017-09072003>End Function</SPAN></FONT></FONT></FONT></DIV>
<DIV><FONT face=Arial><FONT color=#0000ff><FONT size=2><SPAN
class=592535017-09072003></SPAN></FONT></FONT></FONT> </DIV>
<DIV><FONT face=Arial><FONT color=#0000ff><FONT size=2><SPAN
class=592535017-09072003>Invoke it in the BeforeInsert event of the
form.</SPAN></FONT></FONT></FONT></DIV>
<DIV><FONT face=Arial><FONT color=#0000ff><FONT size=2><SPAN
class=592535017-09072003></SPAN></FONT></FONT></FONT> </DIV>
<DIV><FONT face=Arial><FONT color=#0000ff><FONT size=2><SPAN
class=592535017-09072003>Arthur</SPAN></FONT></FONT></FONT></SPAN></FONT></DIV></DIV>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV></DIV>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left><SPAN
class=800250212-15052003><FONT size=2><FONT color=#0000ff><FONT
face=Arial></FONT></FONT></FONT> </DIV></BLOCKQUOTE></SPAN></BODY></HTML>