Shooter54
asked on
Auto number a field in a form that isn't an autonumber field.
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.
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.
You can try something like this:
Me.txtLineNo = NZ(DMax("[LineNo]","TableO rQueryUsed InRecordso urce"),0) +1
Ron
Me.txtLineNo = NZ(DMax("[LineNo]","TableO
Ron
ASKER
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.
[LineNo] is supposed to be the name of the field txtLineNo is bound to.
/gustav
/gustav
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.
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
This allows me to store as many numeric values as I need because each Table/Field combination is unique.
ASKER
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
http://www.lebans.com/rownumber.htm
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.
ASKER
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]","qryDet ail"),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
Me.txtLineNo = NZ(DMax("[LineNo]","qryDet
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
Hey Dale, just wondering how using that function prevents generating the same value. What if 2 users call the function simultaneously?
Ron
Ron
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
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.
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.
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
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
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
You should study my link above; the user just enters the desired rank for a record, and all records reorder in a second.
/gustav
ASKER
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.
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.
> All of these fields are number fields ..
Then it is:
Me!txtLineNo = Nz(DMax("[Line #]", "qryDetail", "FacListNum = " & Me!FacListNum & ""), 0) + 1
/gustav
Then it is:
Me!txtLineNo = Nz(DMax("[Line #]", "qryDetail", "FacListNum = " & Me!FacListNum & ""), 0) + 1
/gustav
ASKER
Gustav Brock, this produced a syntax error. See attached screen shot:
screenshot2.jpg
screenshot2.jpg
Ah, seems you may have a Null, so:
Me!txtLineNo = Nz(DMax("[Line #]", "qryDetail", "FacListNum = " & Nz(Me!FacListNum, 0) & ""), 0) + 1
/gustav
Me!txtLineNo = Nz(DMax("[Line #]", "qryDetail", "FacListNum = " & Nz(Me!FacListNum, 0) & ""), 0) + 1
/gustav
Perhaps you don't have a textfield in your subform for FacListNum. Try this:
Me.txtLineNo = NZ(DMax("[LineNumberField]","qryDetail","FacListNum = " & [FacListNum]),0) +1
ASKER
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?
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Gustav Brock, where do I place this bit of code? What event, and on what control or form?
ASKER
Bingo! Thanks for hanging in there with me. This works as intended, and behaves as it should when dealing with deleted records etc.
ASKER
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?
How about this modification (air code):
Set rs = Me.RecordsetClone
If rs.RecordCount > 0 Then
While Not rs.EOF
If Line < rs!YourLineNumberField.Val ue Then
Line = rs!YourLineNumberField.Val ue
End If
rs.MoveNext
Wend
End If
rs.Close
I guess the BeforeInsert event is the one to use.
/gustav
Set rs = Me.RecordsetClone
If rs.RecordCount > 0 Then
While Not rs.EOF
If Line < rs!YourLineNumberField.Val
Line = rs!YourLineNumberField.Val
End If
rs.MoveNext
Wend
End If
rs.Close
I guess the BeforeInsert event is the one to use.
/gustav
ASKER
That did the trick! Thanks again for your help.
You are welcome!
/gustav
/gustav
https://www.experts-exchange.com/questions/28539652/Access-form-list-re-order-priority.html?anchorAnswerId=40388654#a40388654
/gustav