How to Generate Unbound Autonumbers for displayOnly in the form.

[FA]

How can i generate autonumbers in an unbound textbox?
I have a texbox called txtAutoNum and i want the autonumber to apear in
this textbox. the format of the numbers should be 001,002,003.
I also have a another textbox called txtSYS_CODE. The autonumber should
be based on SYS_CODE so for each SYS_CODE the autonumber should start
from 001, 002,003...... and appear in the txtbox called txtAutoNum
These autonumber are for display only and will not be stored anywhere.


Can someone help me please...

Thanks

Moe

 

[Guest]

Hi,
I think you will need to store them! You can't really save a value in an
unbound control so that the next time it will increase it. Have a look at the
dmax function you can use that to create a custom increasing number.
HTH
Good luck

 

[Roger Carlson]

You don't want an Autonumber, you want a sequential number. Question is,
how do you know what the last number used is if you don't store it?

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Marshall Barton]

How can i generate autonumbers in an unbound textbox?
I have a texbox called txtAutoNum and i want the autonumber to apear in
this textbox. the format of the numbers should be 001,002,003.
I also have a another textbox called txtSYS_CODE. The autonumber should
be based on SYS_CODE so for each SYS_CODE the autonumber should start
from 001, 002,003...... and appear in the txtbox called txtAutoNum
These autonumber are for display only and will not be stored anywhere.

Is this what you want?

http://www.lebans.com/rownumber.htm

 

[FA]

Ok as per your recommendation i created a feild in the table called
AutoSerial (Varchar) . Now what do i need to do to get the above
functionality?
i am very new to VBA so i am not getting anywhere close to dmax
funtion. if you can help i would really appreciate it.

Your respond and comments would be greatly appreciated.

 

[FA]

The function in your link is taking three values:

Function Serialize(qryname As String, keyname As String, keyvalue) As
Long
Dim rs As Recordset
On Error GoTo Err_Serialize
Set rs = CurrentDb.OpenRecordset(qryname, dbOpenDynaset, dbReadOnly)
rs.FindFirst Application.BuildCriteria(keyname,
rs.Fields(keyname).Type, keyvalue)
Serialize = Nz(rs.AbsolutePosition, -1) + 1
Err_Serialize:
rs.Close
Set rs = Nothing
End Function
I do not want to pass any value to the function, instead i want a
function in a form load event that checks
For the same
Me.SYS_CODE
output Serial Numbers
Add 1 for the next record for Me.SYS_CODE

something like this where function prints the autoserial number like
001 checks the Me.SYS_CODE if it is the same then add 1 to the serial
number and display it in the texbox. For that purpose i created a temp
field in a table called AutoSerial (Number) in table tblTemp to hold
the values .

If some can help me please reply.

Thanks Millions

Moe

 

[Marshall Barton]

Sorry, I can't think of a way to do what you ask

 

[Guest]

Marshall,
I tried Lebans RowNum function and followed it exactly but I get a #Name?
result in the unbound text box where I have the control source set to
=RowNum([Form]). The textbox is on a subform.

Below is Leban's function code. Any ideas what may the cause of the #Name?
result I am getting?
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

 

[Stephen Lebans]

That code requires a Reference to the MS DAO object library for your
particular version of Access. You probably have a Reference set to ADO
instead. If you need to keep the ADO Ref then simply move the DAO Ref ABOVE
the ADO Ref.

--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.

Marshall,
I tried Lebans RowNum function and followed it exactly but I get a #Name?
result in the unbound text box where I have the control source set to
=RowNum([Form]). The textbox is on a subform.

Below is Leban's function code. Any ideas what may the cause of the
#Name?
result I am getting?
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

Is this what you want?

http://www.lebans.com/rownumber.htm

 

[Guest]

Nevermind.....The problem was that I named the Module exactly the same as the
function name (i.e RowNum) and it would not execute the function for some
reason. I changed the name of the module and it works good now.

Chace

Marshall,
I tried Lebans RowNum function and followed it exactly but I get a #Name?
result in the unbound text box where I have the control source set to
=RowNum([Form]). The textbox is on a subform.

Below is Leban's function code. Any ideas what may the cause of the #Name?
result I am getting?
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

Is this what you want?

http://www.lebans.com/rownumber.htm

 

[Tom Ellison]

Dear Chace:

That is a very common problem (well it was for me at one time). Be sure to
use some convention that prevents this. I like to name modules with a
prefix that identifies what kind of module: cls for class, mdl for standard
module, etc. This is just good housekeeping. It's a price we pay for
having some of the power we get from object oriented programming.

Tom Ellison

Nevermind.....The problem was that I named the Module exactly the same as
the
function name (i.e RowNum) and it would not execute the function for some
reason. I changed the name of the module and it works good now.

Chace

Marshall,
I tried Lebans RowNum function and followed it exactly but I get a #Name?
result in the unbound text box where I have the control source set to
=RowNum([Form]). The textbox is on a subform.

Below is Leban's function code. Any ideas what may the cause of the
#Name?
result I am getting?
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

FA wrote:

How can i generate autonumbers in an unbound textbox?
I have a texbox called txtAutoNum and i want the autonumber to apear
in
this textbox. the format of the numbers should be 001,002,003.
I also have a another textbox called txtSYS_CODE. The autonumber
should
be based on SYS_CODE so for each SYS_CODE the autonumber should start
from 001, 002,003...... and appear in the txtbox called txtAutoNum
These autonumber are for display only and will not be stored
anywhere.


Is this what you want?

http://www.lebans.com/rownumber.htm