[AccessD] Problem converting code

John,

    My sample db named AppendSequence might be of interest to you. It is available at Rogers Access Library (other developers library). Link - http://www.rogersaccesslibrary.com

    You could adapt the underlying approach suitably, for your specific needs.

Best wishes,
A.D.Tejpal
--------------

  ----- Original Message ----- 
  From: John Clark 
  To: accessd at databaseadvisors.com 
  Sent: Thursday, January 05, 2006 19:47
  Subject: [AccessD] Problem converting code


  Somebody on this list helped me with code to automatically place the next number in a series into a control number. I'm really sorry I don't remember who...I usually make note of it, but I've recently relocated my office and I can't even find the code docs...there probably in a box somewhere.

  Anyhow, what the code does is auto number cases, in the form of 05-001, 05-002,...until the new year, upon which is will restart with 06-001. 
  This worked fine and dandy but for two things...first my client was adamant about there being no possibility of hitting 1000 cases in a year, which as you can probably guess they did. And they were also adamant about there not being an extra zero, when I tried placing it inthere just in case.

  The bigger problem though is that this was done in A97 and I am now using A2K. I will be redoing this program in A2K and the program I am currently working on and trying to reuse the code in, is also A2K. I am getting errors on the line, "Set rst = CodeDb.OpenRecordset(strSQL)" that says too few parameters. I am either being totally dense (a distinct possibility) or it is a DAO vs. ADO problem, which I can not get through my skull and always have problems with...making me dense again...hmmmm.

  Can anyone please point me in the right direction to rectify this
  problem?

  I make the default value for the field set as, "=NewControlNum()"

  ****************************** CODE
  ***********************************************************************
  Function NewControlNum() As String
  Dim strSQL As String
  Dim strNewID As String
  Dim rst As Recordset
  strSQL = "SELECT nz(Max(Mid([tblIndictment]![IndictmentNumber],4)),0)+1
  AS lngMaxID," & _
           "Right(Year(Now()),2)" & _
           "& '-' & [lngMaxID] AS lngNewID " & _
           "FROM tblIndictment " & _
           "WHERE
  (((Left([tblIndictment]![IndictmentNumber],2))=Right(Year(Now()),2)));"
  Set rst = CodeDb.OpenRecordset(strSQL)
  With rst
     strNewID = rst!lngNewID
     rst.Close
     Set rst = Nothing
  End With
  NewControlNum = strNewID

  Rem
  ***************************************************************************

  Select Case Len(NewControlNum)
      Case 4
          NewControlNum = Left(NewControlNum, 3) & "00" &
  Right(NewControlNum, 1)
      Case 5
          NewControlNum = Left(NewControlNum, 3) & "0" &
  Right(NewControlNum, 2)
  End Select


  Thank you!

  John W Clark