troubleshooting Question

Updating an Access table using VBA

Avatar of Liberty4all
Liberty4allFlag for United States of America asked on
Microsoft AccessSQL
8 Comments1 Solution297 ViewsLast Modified:
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"
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 8 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 8 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros