Avatar of Liberty4all
Liberty4all
Flag 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"
Microsoft AccessSQL

Avatar of undefined
Last Comment
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Rimvis

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Rimvis

BTW, are you sure you want update EVERY record in tblMedWtdAvgPctGoalMetByStateRpt with same value?
macksm

You have to specify where clause in Update. Otherwise the access wo'nt know which record to update
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/
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
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
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

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  ?
Liberty4all

ASKER
Thank you Rimvis for the quick response!  I never would have thought to add the ampersands before and after dblMedian.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

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