How to generate unique number

[Rino]

Greetings good people,

I am wondering if there is anyone out there that could help me get me
out of my misery.

This is a problem... I am trying to generate a unique number from the
last record in the table, for a specific field and than add 1 to it.
How can I do this.

So for example in the table called QuoteMain I have a field called
Quote_num. The last record in the field Quote_Num is 1631. The next
time I open the form a new number should be generated whch is 1632 and
when I click ok buttone that value should populated the table so the
next time I open the form the generated number is 1633 etc.

Do not ask me for any code since I am a new one to this kind of you
know what.

I actually developed this code but since filter is involved I cannot
use recordset:

Dim rst As DAO.Recordset
Dim nMaxVal As Integer
Dim n As Integer

nMaxVal = 1630
Set rst = Me.RecordsetClone
If Not rst.BOF Then
rst.MoveFirst
End If
Do While Not rst.EOF
n = rst![Quote_Num]
If n > nMaxVal Then
nMaxVal = n
End If
rst.MoveNext
Loop
Me![Quote_Num] = nMaxVal + 1

For God's sake please help!!!

Thanks in advance

-Rino

 

[Guest]

perhaps I misunderstand, still new myself, but it seems like an autonumber
field would work just as well. If you are putting any information in your
table and saving it the next time you open up the autonumber will be on the
next number. If you just go to design mode, add a field and then set the type
to autonumber. If the issue is not starting at 0, there is an example of that
on mvps.org

http://www.mvps.org/access/tables/tbl0005.htm

Hope that helps. Sorry if I missed the point.

Ryan

Greetings good people,

I am wondering if there is anyone out there that could help me get me
out of my misery.

This is a problem... I am trying to generate a unique number from the
last record in the table, for a specific field and than add 1 to it.
How can I do this.

So for example in the table called QuoteMain I have a field called
Quote_num. The last record in the field Quote_Num is 1631. The next
time I open the form a new number should be generated whch is 1632 and
when I click ok buttone that value should populated the table so the
next time I open the form the generated number is 1633 etc.

Do not ask me for any code since I am a new one to this kind of you
know what.

I actually developed this code but since filter is involved I cannot
use recordset:

Dim rst As DAO.Recordset
Dim nMaxVal As Integer
Dim n As Integer

nMaxVal = 1630
Set rst = Me.RecordsetClone
If Not rst.BOF Then
rst.MoveFirst
End If
Do While Not rst.EOF
n = rst![Quote_Num]
If n > nMaxVal Then
nMaxVal = n
End If
rst.MoveNext
Loop
Me![Quote_Num] = nMaxVal + 1

For God's sake please help!!!

Thanks in advance

-Rino

 

[John Vinson]

Greetings good people,

I am wondering if there is anyone out there that could help me get me
out of my misery.

This is a problem... I am trying to generate a unique number from the
last record in the table, for a specific field and than add 1 to it.
How can I do this.

So for example in the table called QuoteMain I have a field called
Quote_num. The last record in the field Quote_Num is 1631. The next
time I open the form a new number should be generated whch is 1632 and
when I click ok buttone that value should populated the table so the
next time I open the form the generated number is 1633 etc.

It's important to ask: is this a multiuser application (with several
people with different frontends all connecting to the same data table)
or a single-user one? The techniques required are quite different.

Ryan's suggestion of an Autonumber will work - but with some pretty
serious limitations. For one, autonumbers will ALWAYS have gaps - even
if you type one keystroke in a new record and then cancel it (much
less delete a record), you'll "use up" an autonumber; and if you
replicate your database, all your autonumbers become random.

You can do this with a little bit (three lines) of simple VBA code -
but it depends on whether it's single or multi-user how best to do it.

John W. Vinson[MVP]

 

[Rino]

John,

First of all, I'd like to thank you and Ryan for your replys to my
post.

Second, the database sists on the network and will be accessed by
number of users.

Also, what I need to generate is the Quote Number. It can't be auto
number because this number must start from 1631 and autonumber last
record is 768.

I hope this clerify my situation.

Once again thanks a lot for helping me out.

-Rino.

 

[John Vinson]

John,

First of all, I'd like to thank you and Ryan for your replys to my
post.

Second, the database sists on the network and will be accessed by
number of users.

Also, what I need to generate is the Quote Number. It can't be auto
number because this number must start from 1631 and autonumber last
record is 768.

I hope this clerify my situation.

Once again thanks a lot for helping me out.

-Rino.

Rino, I just (re)found this message after a far too long delay. My
apologies! Did you ever get a solution?

<emailed and posted, reply by either>

John W. Vinson[MVP]