?
Solved

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

Posted on 2015-02-21
27
Medium Priority
?
206 Views
Last Modified: 2015-02-22
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_RowNum:
    Exit Function
   
Err_RowNum:
    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.
0
Comment
Question by:Shooter54
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 8
  • 5
  • +1
27 Comments
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 40623508
This code is intended for rearranging the sequence of records, but you may be able to modify it for your need:

http://www.experts-exchange.com/Database/MS_Access/Q_28539652.html#a40388654

/gustav
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 40623852
You can try something like this:
Me.txtLineNo = NZ(DMax("[LineNo]","TableOrQueryUsedInRecordsource"),0) +1

Ron
0
 

Author Comment

by:Shooter54
ID: 40624165
In using the exact string you supply, I receive an error that points to [LineNo]. When substituting [txtLineNo] which is the name of the control I wish to set the value on, it returns the value of 1 each time a line is added. I am putting this snippet in the "before insert" event of my subform.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 51

Expert Comment

by:Gustav Brock
ID: 40624170
[LineNo] is supposed to be the name of the field txtLineNo is bound to.

/gustav
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 40624209
One of the problems with generating numbers like this is that in a multi-user environment, you could potentially have multiple users generate the same number.  To avoid this, when I have a need for this type of functionality, I generally create a table (tbl_NextVal) with fields TableName, FieldName, NextVal

I then use a function to get the NextVal for a particular Table/Field from that table.

Public Function fnNextVal(TableName as string, FieldName as String) as Long

    Dim strCriteria as string
    Dim db as DAO.Database
    Dim rs as DAO.Recordset

    On Error Goto ProcError

    set db = CurrentDb
    set rs = db.Openrecordset("tbl_NextVal", dbOpenDynaset, dbFailOnError)

    strCriteria = "[TableName] = '" & TableName & "' AND " _
                       & "[FieldName] = '" & FieldName & "'"
    rs.findfirst strCriteria
    if rs.NoMatch then
        rs.AddNew
        rs!TableName = TableName
        rs!FieldName = FieldName
        rs!NextVal = 2
    Else
        rs.Edit
        rs!NextVal = rs!NextVal + 1
    End if
    fnNextVal = rs!NextVal - 1
    rs.Update

ProcExit:
    On Error Resume Next
    rs.close
    set rs = nothing
    set db = nothing
    Exit Function

ProcError:
    msgbox err.number & vbcrlf & err.description
    Resume ProcExit

End Function

Open in new window

This allows me to store as many numeric values as I need because each Table/Field combination is unique.
0
 

Author Comment

by:Shooter54
ID: 40624224
Stephen Lebans solution found here is probably the closest I have found to doing what I'm attempting to do in my form, however when using the result of this function to set the value of my "txtLineNo" control, it doesn't prevent re-using numbers when going back and adding rows after a record in the sequence is deleted.

http://www.lebans.com/rownumber.htm
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 40624240
I receive an error that points to [LineNo]
You need to replace LineNo with the name of the actual field in the table or query that you want to update.  As Gustav pointed out, it's the field that txtLineNo is bound to.
0
 

Author Comment

by:Shooter54
ID: 40624242
I guess my intent was unclear.... I'm not looking for the next number sequence in the table, but the next number sequence in my query.  
Me.txtLineNo = NZ(DMax("[LineNo]","qryDetail"),0) +1

this returns the next number in the entire record, which I guess must have been what my intent sounded like. (see screen shot)
screenshot1.jpg
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 40624250
Hey Dale, just wondering how using that function prevents generating the same value.  What if 2 users call the function simultaneously?

Ron
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 40624259
You need to add the purchase order to the criteria.  Assuming you have a textbox in your form called txtPurchOrd, it would look something like this:

Me.txtLineNo = NZ(DMax("[LineNumberField]","qryDetail","PurchaseOrderField = '" & Me.txtPurchOrd & "'"),0) +1

Open in new window

0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 40624363
Ron,

The gap between setting the value and writing the record is miliseconds, but with the NZ(DMAX( ) ) combination, if you don't save that record immediately, then someone else can come along and generate the same number.  You can minimize this risk by using the BeforeUpdate event to retrieve that value, but if you want it visible on the form while editing a record, that is definitely the wrong technique to use.

But I think that is moot as the OP has clarified his/her question.
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 40624366
Shooter,

So, as I understand your clarification, you want to replace the value of 351 that is showing up in your "Line" column with the value of 6?

Dale
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 40624384
Trouble arises when the user wish to reorder the lines.
You should study my link above; the user just enters the desired rank for a record, and all records reorder in a second.

/gustav
0
 

Author Comment

by:Shooter54
ID: 40624404
Dale, that is correct.
IrogSinta, when I use the following in my before insert event of my subform, I get a data type mismatch error.

Me.txtLineNo = Nz(DMax("[Line #]", "qryDetail", "FacListNum = '" & Me.FacListNum & "'"), 0) + 1

Line # is the name of the field in my table, FacListNum is the name of the control which ties my subform to my main form, and also the name of the data field in my detail table. All of these fields are number fields, and so I'm confused by the data type mismatch error.
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 40624420
>  All of these fields are number fields ..

Then it is:

Me!txtLineNo = Nz(DMax("[Line #]", "qryDetail", "FacListNum = " & Me!FacListNum & ""), 0) + 1

/gustav
0
 

Author Comment

by:Shooter54
ID: 40624425
Gustav Brock, this produced a syntax error. See attached screen shot:
screenshot2.jpg
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 40624428
Ah, seems you may have a Null, so:

Me!txtLineNo = Nz(DMax("[Line #]", "qryDetail", "FacListNum = " & Nz(Me!FacListNum, 0) & ""), 0) + 1

/gustav
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 40624436
Perhaps you don't have a textfield in your subform for FacListNum.  Try this:
Me.txtLineNo = NZ(DMax("[LineNumberField]","qryDetail","FacListNum = " & [FacListNum]),0) +1

Open in new window

0
 

Author Comment

by:Shooter54
ID: 40624441
No error this time, but it now returns a result of "1" each time I add a new line, so I have multiple lines numbered "1". I am placing this in the before insert event of my subform. Is that the correct place for this line of code?
0
 

Author Comment

by:Shooter54
ID: 40624447
So far the only solution that returns a result at all without an error is:

Me.txtLineNo = Nz(DMax("[Line #]", "qryDetail", "FacListNum = " & Nz(Me.FacListNum, 0) & ""), 0) + 1

However it returns the same result each time, which is the number 1.
0
 
LVL 51

Accepted Solution

by:
Gustav Brock earned 2000 total points
ID: 40624451
OK, I didn't follow closely. I don't think you can do it this way. You may have to use the RecordsetClone of the form:

Dim rs As DAO.Recordset
Dim Line As Long
Set rs = Me.RecordsetClone

While Not rs.EOF
     If Line < rs!YourLineNumberField.Value Then
         Line = rs!YourLineNumberField.Value
     End If
     rs.MoveNext
Wend
rs.Close

Set rs = Nothing

Me!txtLineNo.Value = Line + 1

/gustav
0
 

Author Comment

by:Shooter54
ID: 40624454
Gustav Brock, where do I place this bit of code? What event, and on what control or form?
0
 

Author Closing Comment

by:Shooter54
ID: 40624466
Bingo! Thanks for hanging in there with me. This works as intended, and behaves as it should when dealing with deleted records etc.
0
 

Author Comment

by:Shooter54
ID: 40624483
This works awesome, as long as there are existing records in my details table. However when creating a new record in my form, and then attempting to add the first record in my subform, I'm unable to do so. Any ideas how to fix this?
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 40624665
How about this modification (air code):

Set rs = Me.RecordsetClone
If rs.RecordCount > 0 Then
    While Not rs.EOF
        If Line < rs!YourLineNumberField.Value Then
            Line = rs!YourLineNumberField.Value
        End If
        rs.MoveNext
    Wend
End If
rs.Close

I guess the BeforeInsert event is the one to use.

/gustav
0
 

Author Comment

by:Shooter54
ID: 40624881
That did the trick! Thanks again for your help.
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 40625148
You are welcome!

/gustav
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

741 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question