Command Button code that affect all records, not just current one 2

[Viv1]

Hi,

I am looking to find out how to create code to make a command button affect the value of 1 field in a table for EVERY record rather than just the one being shown. I have a field called BALANCE that I would like to be increased for every record when a command button is clicked on the form (or a different form), the value to be used to increase BALANCE for each record is taken from a field called SUBSCRIPTIONS on the same form.

E.g. Credit_OnClick()
BALANCE = SUBSCRIPTIONS
End Sub

If anyone has any ideas I'd be grateful.

Thanks, Viv

 

[RoyVidar]

Hi!

From what you say, I'm gettin the impression that all balances on the form should equal the form control SUBSCRIPTIONS, if so, perhaps looping thru the form recordset:

[tt]dim rs as dao.recordset
set rs=me.recordsetclone
rs.movefirst
do whilte not rs.eof
rs.edit
rs!BALANCE = Me!SUBSCRIPTIONS
rs.update
rs.movenext
loop
set rs=nothing[/tt]

- some errorhandling, and testing might be needed, but this should hopefully get you in the right direction.

If you don't have it, set a reference to Microsoft DAO 3.# Object Library (in any module Tools | References)

Roy-Vidar

 

[KenReay]

Hi

Or an SQL Update

Dim strSQL

strSQL = "UPDATE MyTable SET Balance = " & Me.Subscription & ";"
docmd.setwarnings false
docmd.runsql strsql
docmd.setwarnings true

you will have to substitute you names for table, columns etc of course

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Viv1]

Thanks for the tips, I will try them and see if they work. I think I can just about see what I have to do...I'm a beginner with this and trying to teach myself with the help of this page!

Thanks, Viv

 

[KenReay]

Hi

noticed a typo in one of the posts

do whilte not rs.eof

should be

do while not rs.eof


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[RoyVidar]

Thanks KenReay!

Should probably add "typed not tested" to my sig;-)

Viv1:
Any problems, post back with your current code, errormsg...

Roy-Vidar

 

[Viv1]

Hi,

Thanks! Got that up and running but I've discovered another problem due to having calculated fields. One of the fields called AGE is calculated using the DOB field, the DateDiff code is stored in the ControlSource property box but when the records are saved the calculated age only appears on the form and not in the table. This would be ok but when queries using the AGE field are performed they are not accurate as the data is not linked back to the table. Is there a simple way to refresh the calculated data shown on the form in the table as well?

Thanks, Viv

 

[RoyVidar]

Nice to hear it's working, thanx for the star!

Regarding storing a calculated field, I don't think you'll find much encouragement for that here.

It is much better to calculate it on the fly (as you do on the form) as long as the calculated value can be derived from values alredy in the table everytime you're going to use it. For instance, you could probably use rather similar syntax in the query grid of the query to calculate the age - and the benefit, it would be correct (datediff on DOB and todays date) and not rely on someone to remember (or not) to recalculate.

Calculated values are bound to give headaches...

If you have really, really, really thought thru all the conseqences of storing caclulated values, and are 100% sure how to set up a shceme to recalculate as often as needed, setting up tests to verify the recalculations are performed etc (see - it's often more work dealing with calculated values than calculating on the fly;-)) then using the method in my suggestion, just perform the same calculation:

[tt] rs!MyAge.Value = DateDiff("yyyy",rs!DOB,date)[/tt]

- very simplified (again untested) calculation

Roy-Vidar

 

[KenReay]

Hi

I echo Roy's commenst ref calculated columns, do not store them in the table, you are asking for trouble, but if you must you just need to modify the update query:

strSQL = "UPDATE MyTable SET Balance = " & Me.Subscription & ", Age = CalcAge(DOB) ;"

but again I say do no do it, it will only be right at the instant you do the update, every day following that it will become more incorrect


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Viv1]

Thanks, got that sorted too...I've managed to contain all the calculations in the VBA on AfterUpdate and Enter etc.

Thanks for you help!