VBA Code to perform calculations on data in a table or Query 1

[psarros]

What is the code to:
Open a table, and then perform calculations on fields within the table?

Thanks

 

[KenReay]

Hi

You do not necessarily need code to do this, you can do it with a query, use a Select query if yoou just want to see the calculations, or an update query if you actauuly want to update the table (eg say increase price by 10% ) Regards

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

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

UK

 

[psarros]

I would like to do it with code, because there asre some other calculations I would like to do.

 

[KenReay]

Hi

OK

Using DAO

Dim Db as DAO.Database
Dim Rs as DAO.Recordset
Set Db = CurrentDb()
Set Rs = Db.OPenRecordset("SELECT * FROM MyTable;&quot
WIth Rs
If .REcordCount > 0 Then
Do Until .EOF
.Edit
!MyField = !Myfied * 1.10
.Update
.MoveNExt
Loop
End If
End With
Rs.Close
Set Rs = Nothing
Set Db = Nothing

you must ust your ouwn table name, column names 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

 

[psarros]

Thanks this is what I want,

but I keep getting an error
User-defined type not defined

Any ideas?


Thanks

 

[KenReay]

Hi

Where (ie on which line) do you the the error?

is it on Dim Db as DAO.Database

if yes, you do not have a reference to DAO library set

Open any code module in design view, choose toos\references from the emnu and set a refrence to the Microsoft DAO.Library

For future refernce it would help if you quoted the Access version you are using (eg Access97, Access2000, AccessXP) when posting a problem Regards

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

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

UK

 

[psarros]

Thanks for your help!!