DAO to Update Table Recordset - increment

[Trevor G]

Hi All,

It has been sometime since I have had to look at Access VBA. What I am asking is can someone look at the following code and correct it please.

I have inherited an old database with a new field to add some new invoice numbers and increment the number to each recordset by + 1. Starting at 001. I have formatted the field to a Number Field and formatted it to 000"/2015". I now need to populate over 2000 records with the sequential numbers for each record.

I have created the following code as my attempt. Of course it doesn't work!

Sub AddNumbertoRecords()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim counter As Long
counter = 1
Set db = CurrentDb()
Set rst = db.OpenRecordset("tblInvoices", dbOpenDynaset)
Do While rst.EOF
    rst.MoveFirst
    rst.Edit
    rst!["NewInvoiceNumber"] = counter + counter + 1
    rst.Update
    rst.MoveNext
Loop
rst.Close
rst = Nothing
End Sub

 

[jdraw]

What exactly does It doesn't work mean?

Comments?

Counter may be reserved in Access (it used to be equivalent to autonumber)

Do While rst.EOF I think you mean while NOT EOF

rst.MoveFirst Doing this within the Do Loop means you are always working with the first record You don't need this line. Rst gets positioned to first record with Openrecordset.

rst!["NewInvoiceNumber"] = counter + counter + 1
I think this should be

rst!NewInvoiceNumber = counter
then a new line counter = counter + 1

Good luck

 

[Trevor G]

Thank you for the reply and advice. Sorry to say I haven't been able to make it work. I have adjusted the code as follows:

It now falls over on this line with Error Number 3265

rst!["NewInvoiceNumber"] = counter1

Sub AddNumbertoRecords()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim counter1 As Long
counter = 1
Set db = CurrentDb()
Set rst = db.OpenRecordset("tblInvoices", dbOpenDynaset)
Do While Not rst.EOF
    rst.Edit
    rst!["NewInvoiceNumber"] = counter1
    counter1 = counter1 + 1
    rst.Update
    rst.MoveNext
Loop
rst.Close
End Sub

 

[pbaldy]

You missed the correction to that line. Should be:

rst!NewInvoiceNumber = counter1

 

[jdraw]

It was an issue with bracket

should be
rst!NewInvoiceNumber = counter1 '<----changed the bracketing and "


Here's the result:
ID Client NewInvoiceNumber
1 Microsoft 000/2015
2 Apple 001/2015
3 Cars 002/2015
4 Lorries 003/2015
5 Adventure 004/2015
6 Comics 005/2015
7 Papers 006/2015

 

[pbaldy]

Sorry for jumping in jdraw; your indicator light was off so I thought you were offline.

 

[jdraw]

No problem. I thought I changed the line, but then saw I had only started to change the 1 reference and not the other.
Thanks.

 

[Trevor G]

Thank you both. Have checked it and it works perfectly.