Assigning sequential numbers

[John]

Hi

I need to assign sequential numbers to a field to some records in a table. I
am opening a recordset and assigning the numbers as follows;

Sub MyButton_Click()
While Not rs.EOF
If rs![Account Type] = "Account" Then
rs.Edit
If IsNull(rs![Invoice No]) Then
rs![Invoice No] = Forms![MyMainForm]![lastinvno].Form![lastinvno]
+ 1
Me.lastinvno.Requery
Me.Requery
Me.Refresh
End If
rs.MoveNext
Wend
End Sub

MyMainForm is the form with the button that is running this code. lastinvno
is a field on a subform lastinvno. The lastinvno field is assigned the
expression; Max(IIf(IsNull([Invoice No]),0,[Invoice No])).

The problem I have is that some records are being assigned duplicate numbers
and as you can imagine, duplicate invoice numbers are not good. How can I
ensure that records are assigned unique values?

Thanks

Regards

 

[Steve Schapel]

John,

What is the recordset based on? What is its purpose? What is your
purpose... are you trying to assign Invoice Numbers to all records that
haven't got one already, or are you just trying to assign an Invoice
Number to the form's current record?

 

[John]

The record set based on the "orders" table which contains an [invoice no]
field. It is like;

set rs = currentdb().openrecordset("Select * from orders where order_date =
#26/06/2005#")

So all orders records for today need to be assigned invoice numbers in
sequence.

Thanks

Regards

John,

What is the recordset based on? What is its purpose? What is your
purpose... are you trying to assign Invoice Numbers to all records that
haven't got one already, or are you just trying to assign an Invoice
Number to the form's current record?

--
Steve Schapel, Microsoft Access MVP

Hi

I need to assign sequential numbers to a field to some records in a
table. I am opening a recordset and assigning the numbers as follows;

Sub MyButton_Click()
While Not rs.EOF
If rs![Account Type] = "Account" Then
rs.Edit
If IsNull(rs![Invoice No]) Then
rs![Invoice No] =
Forms![MyMainForm]![lastinvno].Form![lastinvno] + 1
Me.lastinvno.Requery
Me.Requery
Me.Refresh
End If
rs.MoveNext
Wend
End Sub

MyMainForm is the form with the button that is running this code.
lastinvno is a field on a subform lastinvno. The lastinvno field is
assigned the expression; Max(IIf(IsNull([Invoice No]),0,[Invoice No])).

The problem I have is that some records are being assigned duplicate
numbers and as you can imagine, duplicate invoice numbers are not good.
How can I ensure that records are assigned unique values?

Thanks

Regards

 

[Steve Schapel]

John,

Thanks for the further explanation. I'm sort of getting the idea.

I supopose one option would be to assign the Invoice Number at the time
that the Order record is created. You could do this by setting the
Default Value property of the Invoice No control on the form to...
DMax("[Invoice No]","Orders")+1
.... or else you could use code on the Before Update event of the form to
allocate the next number in the sequence if there isn't already an
invoice number entered.
Is there any reason why either of these approaches won't work?

Otherwise, if it really is the best approach to allocate the Invoice
Numbers for all the day's orders at once, I would do it more like this...
Sub MyButton_Click()
Dim rs as DAO.Recordset
Dim NextInvoice As Integer
Set rs = CurrentDb.OpenRecordset("SELECT * FROM orders WHERE
order_date = Date()")
NextInvoice = DMax("[Invoice No]","Orders")+1
With rs
Do Until .EOF
If ![Account Type] = "Account" Then
If IsNull(![Invoice No]) Then
.Edit
![Invoice No] = NextInvoice
.Update
.MoveNext
NextInvoice = NextInvoice + 1
End If
End If
Loop
End With
End Sub

 

[John]

Sometimes orders are not confirmed (provisional orders) or are actually
cancelled after being confirmed initially. This will leave gaps in invoice
numbers , as the ones assigned to the cancelled orders will not be used.

Thanks

Regards

John,

Thanks for the further explanation. I'm sort of getting the idea.

I supopose one option would be to assign the Invoice Number at the time
that the Order record is created. You could do this by setting the
Default Value property of the Invoice No control on the form to...
DMax("[Invoice No]","Orders")+1
... or else you could use code on the Before Update event of the form to
allocate the next number in the sequence if there isn't already an invoice
number entered.
Is there any reason why either of these approaches won't work?

Otherwise, if it really is the best approach to allocate the Invoice
Numbers for all the day's orders at once, I would do it more like this...
Sub MyButton_Click()
Dim rs as DAO.Recordset
Dim NextInvoice As Integer
Set rs = CurrentDb.OpenRecordset("SELECT * FROM orders WHERE
order_date = Date()")
NextInvoice = DMax("[Invoice No]","Orders")+1
With rs
Do Until .EOF
If ![Account Type] = "Account" Then
If IsNull(![Invoice No]) Then
.Edit
![Invoice No] = NextInvoice
.Update
.MoveNext
NextInvoice = NextInvoice + 1
End If
End If
Loop
End With
End Sub

--
Steve Schapel, Microsoft Access MVP

The record set based on the "orders" table which contains an [invoice no]
field. It is like;

set rs = currentdb().openrecordset("Select * from orders where order_date
= #26/06/2005#")

So all orders records for today need to be assigned invoice numbers in
sequence.

 

[John]

PS: The code seems to be working. The system is going live tomorrow, will
know for sure then.

Thanks

Regards

John,

Thanks for the further explanation. I'm sort of getting the idea.

I supopose one option would be to assign the Invoice Number at the time
that the Order record is created. You could do this by setting the
Default Value property of the Invoice No control on the form to...
DMax("[Invoice No]","Orders")+1
... or else you could use code on the Before Update event of the form to
allocate the next number in the sequence if there isn't already an invoice
number entered.
Is there any reason why either of these approaches won't work?

Otherwise, if it really is the best approach to allocate the Invoice
Numbers for all the day's orders at once, I would do it more like this...
Sub MyButton_Click()
Dim rs as DAO.Recordset
Dim NextInvoice As Integer
Set rs = CurrentDb.OpenRecordset("SELECT * FROM orders WHERE
order_date = Date()")
NextInvoice = DMax("[Invoice No]","Orders")+1
With rs
Do Until .EOF
If ![Account Type] = "Account" Then
If IsNull(![Invoice No]) Then
.Edit
![Invoice No] = NextInvoice
.Update
.MoveNext
NextInvoice = NextInvoice + 1
End If
End If
Loop
End With
End Sub

--
Steve Schapel, Microsoft Access MVP

The record set based on the "orders" table which contains an [invoice no]
field. It is like;

set rs = currentdb().openrecordset("Select * from orders where order_date
= #26/06/2005#")

So all orders records for today need to be assigned invoice numbers in
sequence.

 

[QQ]

Nobaby can help you only yoursely

John,

Thanks for the further explanation. I'm sort of getting the idea.

I supopose one option would be to assign the Invoice Number at the time
that the Order record is created. You could do this by setting the
Default Value property of the Invoice No control on the form to...
DMax("[Invoice No]","Orders")+1
... or else you could use code on the Before Update event of the form to
allocate the next number in the sequence if there isn't already an
invoice number entered.
Is there any reason why either of these approaches won't work?

Otherwise, if it really is the best approach to allocate the Invoice
Numbers for all the day's orders at once, I would do it more like this...
Sub MyButton_Click()
Dim rs as DAO.Recordset
Dim NextInvoice As Integer
Set rs = CurrentDb.OpenRecordset("SELECT * FROM orders WHERE
order_date = Date()")
NextInvoice = DMax("[Invoice No]","Orders")+1
With rs
Do Until .EOF
If ![Account Type] = "Account" Then
If IsNull(![Invoice No]) Then
.Edit
![Invoice No] = NextInvoice
.Update
.MoveNext
NextInvoice = NextInvoice + 1
End If
End If
Loop
End With
End Sub

--
Steve Schapel, Microsoft Access MVP

The record set based on the "orders" table which contains an [invoice no]
field. It is like;

set rs = currentdb().openrecordset("Select * from orders where order_date =
#26/06/2005#")

So all orders records for today need to be assigned invoice numbers in
sequence.