Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Number generator
- Thread starter aliciutza
- Start date
I've done this by keeping the current highest number in a table (by itself). When I have to insert a new record, I read the table, increment the number, use it for the new record and save it back to the table. Or you could use a count query to determine the current number of records.
Of course, there is still a problem when deleting records. What do you want to do with the open slots? Reuse them on the next insert? Change all the keys in the remaining records?
Or did you mean you just want to number the records as you are displaying them? You could insert them into a temporary table with an additional autonumber column before retrieving them for display. Just thinking out loud! Ideas are a dime a dozen...
Frank
Of course, there is still a problem when deleting records. What do you want to do with the open slots? Reuse them on the next insert? Change all the keys in the remaining records?
Or did you mean you just want to number the records as you are displaying them? You could insert them into a temporary table with an additional autonumber column before retrieving them for display. Just thinking out loud! Ideas are a dime a dozen...
Frank
Hi
Am I right in thinking you wish to reuse numbers where there is a gap? For example:
Rec No
1
2
4
New Rec No=3
If so you could use something like:
Function NewRecNo()
NewRecNo = 0
Set rs = CurrentDb.OpenRecordset("SELECT [RecNo] from tblTable ORDER BY [RecNo]")
Do While True
NewRecNo = rs!RecNo + 1
rs.MoveNext
If rs.EOF Then
Debug.Print NewRecNo
Exit Function
ElseIf rs!RecNo <> NewRecNo Then
Debug.Print NewRecNo
Exit Function
End If
Loop
End Function
Am I right in thinking you wish to reuse numbers where there is a gap? For example:
Rec No
1
2
4
New Rec No=3
If so you could use something like:
Function NewRecNo()
NewRecNo = 0
Set rs = CurrentDb.OpenRecordset("SELECT [RecNo] from tblTable ORDER BY [RecNo]")
Do While True
NewRecNo = rs!RecNo + 1
rs.MoveNext
If rs.EOF Then
Debug.Print NewRecNo
Exit Function
ElseIf rs!RecNo <> NewRecNo Then
Debug.Print NewRecNo
Exit Function
End If
Loop
End Function
- Thread starter
- #4
Hi again, and thaks a lot for your answers.
I need to use this number for autonumbering invoices. The numbers must be unique for each invoice and I am not alowed to skip numbers.
The solution in this post
is almost what I needed but I have more than one table (3 in fact) unrelated to each other that contains the data for three different kind of invoices that must be numbered as I wrote before.
I was thinking to create a table that will hold this numbers but I don't know how to relate it to the others. No need to renumber after deletion, as you can see is the worst thing that can happen. Just to be sure that the correct number is taken. Deletion may occur as a mistake or if I decide not to make the invoice anyway after saving the record.
Thanks again.
I need to use this number for autonumbering invoices. The numbers must be unique for each invoice and I am not alowed to skip numbers.
The solution in this post
is almost what I needed but I have more than one table (3 in fact) unrelated to each other that contains the data for three different kind of invoices that must be numbered as I wrote before.
I was thinking to create a table that will hold this numbers but I don't know how to relate it to the others. No need to renumber after deletion, as you can see is the worst thing that can happen. Just to be sure that the correct number is taken. Deletion may occur as a mistake or if I decide not to make the invoice anyway after saving the record.
Thanks again.
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question
- Locked
- Question