Create update query to increment a number field in each record 1

[theSizz]

I want to update a field called BinNo in each existing record of a table called tblInventory. BinNo data type is number and field size is long integer. I want the update query to start with the first record and enter 1000 in the BinNo field and then increment each following record by 10. In other words form a series 1010, 1020, 1030, etc. I thought about creating the series in Excel and then just import the data into my Access table. I would rather learn the method using an update query. Any help would be appreciated.

 

[WildHare]

It would be a little difficult to do this in a QUERY, because a query is 'set' oriented - it doesn't really do this record-to-record operation very well.

But you can do this very easily with a few lines of code. Place this routine in a STANDARD module, and then simply RUN it from the code window (F5) (?? i think...)

Access97 Version:

Public Sub SetNums()
Dim rs as recordset
Dim startVal as integer
StartVal = 1000

Set RS = CurrentDB.OpenRecordset("{your table name here}&quot

RS.MoveFirst

While NOT RS.BOF
Do
rs.Edit
RS.BinNo = StartVal
rs.Update
rs.MoveNext
StartVal = StartVal + 10
Loop

Rs.close
set rs = nothing
End sub

If you're using Access2K or 2002, you need to use ADO references, or make sure your DAO 3.6 object library is linked in. If you're not sure of what that all means, post back.

Jim







How many of you believe in telekinesis? Raise my hand...
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[theSizz]

Jim
The code works up to the line RS.BinNo = StartVal
It produces a message bos that says "Method or Data Member not found" I've got the DAO 3.6 object library linked in on top of ActiveX Data Objects 2.1 library.
I've tried putting enclosing BinNo in "" and also in [ ] and still get the same error. This is the reference to the field to update right?
Please advise.
Thanks,
Rich

 

[theSizz]

Jim,
I figured out the problem with the field reference, and I changed the loop statement.
This code produces the desired results.


Public Sub SetNums()
Dim rs as recordset
Dim startVal as integer
StartVal = 1000

Set RS = CurrentDB.OpenRecordset("{your table name here}&quot

rs.MoveFirst

Do
rs.Edit
rs.Fields("BinNo&quot = startval
rs.Update
rs.MoveNext
startval = startval + 10
Loop Until rs.EOF

rs.Close
Set rs = Nothing

End Sub

Thanks for your help you got me pointed in the right direction. Everything works fine.

My next task is to figure out how to make this update only specific records rather than the whole table.I'm guessing that this might be accomplished using a seek method.

 

[rajeessh]

Seeking in the recordset is quite costly
rather than filter the required records using where condition in recordset

Set RS = CurrentDB.OpenRecordset("{your table name here}&quot

Set RS = CurrentDB.OpenRecordset(&quot;Select <RequiredFields> from <table name> where <Condn>&quot

Jim: Is my suggestion correct.

 

[WildHare]

Raj/TheSizz

I think that suggestion to filter in the recordset method will work.

Another way is to simply design a query with the same filtering criteria and use it as the recordset, I believe. Two methods, same cat.

Jim How many of you believe in telekinesis? Raise my hand...
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[theSizz]

Raj/Jim

Thanks guys the sql statement did it. Simple and precise.

theSizz