Auto number generator

[Rino]

Greetings,

I am having problem with the following codes for some reason and I
cannot figure it out where the problem is. However, what I'm trying to
achive is to have a auto number generator for my quote form, so
basically this generator would create a quote number that will change
as new records are added. I am doing this in MS Access 2000.

here is the code:

-----------------------------------------------
Private Sub Form_BeforeInsert(Cancel As Integer)
Dim rst As DAO.Recordset
Dim i As Integer
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
End Sub
-----------------------------------------------------

My guess is that problem lays in the next one

------------------------------------------------------
Private Sub txtQuoteNo_Change()
Dim rst As DAO.Recordset, intI As Integer
Set rst = Me.RecordsetClone
If IsNull(rst![Quote_Num]) Then
intI = rst![Quote_Num]
Me![Quote_Num] = intI + 2
End If
End Sub
------------------------------------------------------

Could you please help me figure this out.

Thanks in advance


-Rino

 

[clintonG]

Rino I think you'll find people that are still working with this old stuff
at news://microsoft.public.inetserver.asp.db


<%= Clinton Gallagher
NET csgallagher AT metromilwaukee.com
URL http://www.metromilwaukee.com/clintongallagher/

Greetings,

I am having problem with the following codes for some reason and I
cannot figure it out where the problem is. However, what I'm trying to
achive is to have a auto number generator for my quote form, so
basically this generator would create a quote number that will change
as new records are added. I am doing this in MS Access 2000.

here is the code:

-----------------------------------------------
Private Sub Form_BeforeInsert(Cancel As Integer)
Dim rst As DAO.Recordset
Dim i As Integer
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
End Sub
-----------------------------------------------------

My guess is that problem lays in the next one

------------------------------------------------------
Private Sub txtQuoteNo_Change()
Dim rst As DAO.Recordset, intI As Integer
Set rst = Me.RecordsetClone
If IsNull(rst![Quote_Num]) Then
intI = rst![Quote_Num]
Me![Quote_Num] = intI + 2
End If
End Sub
------------------------------------------------------

Could you please help me figure this out.

Thanks in advance


-Rino

 

[AMDRIT]

There are a number of things you can do to implement an autonumber solution.

1. You are using Access, so let it perform the Autonumbering for you.
2. Create a table to hold configuration settings. One of them would be to
store the next number to use.

'Begin Trans

dim rstConfigRow as DAO.Recordset ' Holds incrementer data
iValue = rstConfigRow("NextQuoteNumber")
rstConfigRow("NextQuoteNumber") = iValue + 1

'You will be the only one to get this number, it will never repeat

'End Trans

'Begin Trans

dim rstQuoteRow as DAO.Recordset ' Holds new row for insert
rstQuoteRow("Quote_Num") = iValue

'End Trans

HTH

Greetings,

I am having problem with the following codes for some reason and I
cannot figure it out where the problem is. However, what I'm trying to
achive is to have a auto number generator for my quote form, so
basically this generator would create a quote number that will change
as new records are added. I am doing this in MS Access 2000.

here is the code:

-----------------------------------------------
Private Sub Form_BeforeInsert(Cancel As Integer)
Dim rst As DAO.Recordset
Dim i As Integer
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
End Sub
-----------------------------------------------------

My guess is that problem lays in the next one

------------------------------------------------------
Private Sub txtQuoteNo_Change()
Dim rst As DAO.Recordset, intI As Integer
Set rst = Me.RecordsetClone
If IsNull(rst![Quote_Num]) Then
intI = rst![Quote_Num]
Me![Quote_Num] = intI + 2
End If
End Sub
------------------------------------------------------

Could you please help me figure this out.

Thanks in advance


-Rino

 

[Rino]

I'd like to thank you guys for your inputs to my post. I honestly
appriciate it.

Anyway, I've used all of your suggestions and no matter what it won't
work.


The code I've provided above however, is functional it works great in
Access 97
I've tested it there but for some reason it won't loop in MS Access
2000.


I also created new form from scratch thinking maybe it doesn't work
because I have previous records in the table but that is not the case.
I've totally mimicked the situation that I have in MS Access 97.


Any toughts about this? This thing causes me some serious headache
since I'm unable to generate the quote number that would start eather
from the last record or as I did in my code from desired number which
in my case is 1630.


Any ideas are greatelly appriciated.


Have a wonderful day


-Rino

 

[Earl]

You need to get in the right newsgroup Rino.

microsoft.public.access or microsoft.public.vb.database might help.

 

[Cor Ligthert [MVP]]

Rino,

Why not let the system create the best identifier there is at the world, the
Global Unique Identifier (GUID)?

It is in VB just
\\\
dim myAutoID = New Guid()
///

I hope this helps,

Cor

Greetings,

I am having problem with the following codes for some reason and I
cannot figure it out where the problem is. However, what I'm trying to
achive is to have a auto number generator for my quote form, so
basically this generator would create a quote number that will change
as new records are added. I am doing this in MS Access 2000.

here is the code:

-----------------------------------------------
Private Sub Form_BeforeInsert(Cancel As Integer)
Dim rst As DAO.Recordset
Dim i As Integer
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
End Sub
-----------------------------------------------------

My guess is that problem lays in the next one

------------------------------------------------------
Private Sub txtQuoteNo_Change()
Dim rst As DAO.Recordset, intI As Integer
Set rst = Me.RecordsetClone
If IsNull(rst![Quote_Num]) Then
intI = rst![Quote_Num]
Me![Quote_Num] = intI + 2
End If
End Sub
------------------------------------------------------

Could you please help me figure this out.

Thanks in advance


-Rino