update auto incrementing check# to records on table

[scousineau]

There could be 50 vendors which we are going to send payments to, and
each vendor could have up to 100 records on the table for payment. I
need a method to enter a check# once then the system would put that #
on all the records for one vendor then add 1 to the check# and then
update all the records for the next vendor and so on untill done. I am
not well versed on VBA so if anyone can point me in the right direction
thank you in advance. The system in Office XP Pro/Access 2003.

[Squirrel]

Hi,

This code isn't tested but should give you an idea of how to approach this
task:

You'll need a reference to Microsoft DAO 3.6 Object library.
Call the function with the starting check number.

function updateRec(check Number as integer)

dim rst as recordset

set rst = currentdb.openr ecordset("selec t distinct vendor from mytable;"

rst.movefirst
while not rst.eof
docmd.runsql ("update mytable set checkNo = " & checkNumber & _
" where vendor = " & rst!vendor
checkNumber = checkNumber + 1
rst.movenext
wend

rst.close
set rst = nothing

end function

HTH -Linda
<sc********@oae usa.com> wrote in message
news:11******** *************@z 14g2000cwz.goog legroups.com...

There could be 50 vendors which we are going to send payments to, and
each vendor could have up to 100 records on the table for payment. I
need a method to enter a check# once then the system would put that #
on all the records for one vendor then add 1 to the check# and then
update all the records for the next vendor and so on untill done. I am
not well versed on VBA so if anyone can point me in the right direction
thank you in advance. The system in Office XP Pro/Access 2003.