Link to home
Create AccountLog in
Avatar of Bryan Schmidt
Bryan SchmidtFlag for United States of America

asked on

Updating an Access table using VBA

The following code calculates a median value (dblMedian) for the field SBstateGoalPctCurrent.  This process works correctly, but I'm unable to update this value to another table named tblMedWtdAvgPctGoalMetByStateRpt in a field named SBgeoPctGoalMetMed.  I get run-time error 3061: Too few parameters.  Expected 1.

I'm not using any parameters so this error makes no sense to me.  If I eliminate the CurrentDb.Execute strSQL then no error occurs, but even though the code runs no updating occurs.  Please advise what changes I need to make in my updating process.

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim dblMedian As Double
   
strSQL = "SELECT tblMedWtdAvgPctGoalMetByState.SBstateGoalPctCurrent FROM tblMedWtdAvgPctGoalMetByState;"
    Set db = CurrentDb
    Set rst = db.OpenRecordset(strSQL)
    If rst.RecordCount Then
        rst.MoveLast
        rst.Move (-1 * Fix(rst.RecordCount / 2))
        dblMedian = rst.Fields(0).Value
        If rst.RecordCount Mod 2 = 0 Then
            rst.MovePrevious
            dblMedian = ((dblMedian + rst.Fields(0).Value) / 2)
        End If
     End If
     
strSQL = "UPDATE tblMedWtdAvgPctGoalMetByStateRpt SET tblMedWtdAvgPctGoalMetByStateRpt.SBgeoPctGoalMetMed = dblMedian;"
         
CurrentDb.Execute strSQL
         
MsgBox "Finished"
ASKER CERTIFIED SOLUTION
Avatar of Rimvis
Rimvis
Flag of Lithuania image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
BTW, are you sure you want update EVERY record in tblMedWtdAvgPctGoalMetByStateRpt with same value?
You have to specify where clause in Update. Otherwise the access wo'nt know which record to update
Avatar of jo_m
jo_m

hello there

you may not be using a parameter but some functions need an argument or 2 to work

dbs.OpenRecordset("strSql", dbOpenDynaset)

that would be my initial starting point to resolve this issue

tx

jo




this URL gives you a clear explanation

http://social.msdn.microsoft.com/Forums/en-US/isvvba/thread/ebc5172f-4382-44dc-9ea0-8640dd206c83/
Avatar of DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
What is the value of dblMedian just before the Execute?

Also, are you missing a WHERE clause in the UPDATE statement ?

mx
I don't  think there is anything wrong with the syntax, since dblMedian is Numeric.  And the Semi-Colon is optional.

Is there maybe a typo in tblMedWtdAvgPctGoalMetByStateRpt or SBgeoPctGoalMetMed  ?
Avatar of Bryan Schmidt

ASKER

Thank you Rimvis for the quick response!  I never would have thought to add the ampersands before and after dblMedian.
Well ... the 'syntax' is most likely missing a WHERE clause, unless the intent is to update EVERY record ... but maybe there is only one record ?

mx