troubleshooting Question

Auto number a field in a form that isn't an autonumber field.

Avatar of Shooter54
Shooter54 asked on
Microsoft Access
27 Comments1 Solution388 ViewsLast Modified:
I am using the following in my "before insert" event on a subform:

Me.txtLineNo = Me.CurrentRecord

As long as my work flow is linear and no lines are ever deleted from the records, it works as intended. I am getting some unexpected and undesirable results however when my workflow is not linear, for instance a line is deleted and I add another line at a later date, the line numbering will repeat previously used numbers. What I need is a function or some code which will determine what my maximum number is in the list, and will +1 to it, and insert it into the linenumber control. Does anyone have a simple solution for this?

I tried this public function I pinched from Stephen Lebans, but could never get it to work as I thought it should:
'Copyright Stephen Lebans 1999
'May not be resold
'Please include my 1 line Copyright notice
'in your code if you use these functions

'I left a bunch of development code in here in case anyone decides to go
'down the same paths I did.
'Created by Stephen Lebans with help from Chris Bergmans
' Updated by Allen Browne Oct/2002
'Production version of GetLineNumberForm
'Works in Form or SubForm mode
'Set controlsource of unbound Text box to
'= RowNum([Form])
'Type exactly as above

Public Function RowNum(frm As Form) As Variant
On Error GoTo Err_RowNum
    'Purpose:   Numbering the rows on a form.
    'Usage:     Text box with ControlSource of:  =RowNum([Form])
    With frm.RecordsetClone
        .Bookmark = frm.Bookmark
        RowNum = .AbsolutePosition + 1
    End With
    Exit Function
    If Err.Number <> 3021& Then  'Ignore "No bookmark" at new row.
        Debug.Print "RowNum() error " & Err.Number & " - " & Err.Description
    End If
    RowNum = Null
    Resume Exit_RowNum
End Function

Thanks in advance.
Join our community to see this answer!
Unlock 1 Answer and 27 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 27 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros