View Single Post
  #2 (permalink)  
Old May 10th, 2005, 03:07 PM
echovue echovue is offline
Friend of Wrox
 
Join Date: Oct 2004
Location: Clinton, UT, USA.
Posts: 564
Thanks: 0
Thanked 4 Times in 4 Posts
Default

I used to shy away from autonumber for this exact reason, but if you can figure out a way to use it, it probably be best for you in the long run.

However if it definitely won't work for you...

I used this code to figure out the next number in the sequence
where strPrimary key is the field Name of the sequential number
and strTable is the table in question.

strSQL = "SELECT * FROM " & strTable & " ORDER BY " & strPrimaryKey
    rs.Open strSQL, cn

    If rs.EOF And rs.BOF Then
        theKey = 1
    Else
        rs.MoveLast
        theKey = rs.Fields(strPrimaryKey) + 1
    End If

If you don't want the number to be added until you save the record, use an unbound form, and have a save button. The on Click event then pulls each of the values, gets the sequential number and then inserts into your table. This way if you exit without saving the record, nothing is added.

Hope that helps.

Mike

Mike
EchoVue.com
Reply With Quote