By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,561 Members | 3,127 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,561 IT Pros & Developers. It's quick & easy.

update auto incrementing check# to records on table

P: n/a
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.

Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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(checkNumber as integer)

dim rst as recordset

set rst = currentdb.openrecordset("select 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********@oaeusa.com> wrote in message
news:11*********************@z14g2000cwz.googlegro ups.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.

Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.