CODEWITHSUNDEEP
vbams-accessupdatesdao
davidzxc574
davidzxc574

Reputation: 491

Access VBA Docmd.OpenQuery Can Run an update query but Currentdb.Excecute Cannot

I created a saved update query as below, which has control values and IIf function.

UPDATE SYS_AAAA_AAAH 
SET SYS_AAAA_AAAH.AAK = AAA & " " & AAB & IIf(IsNull(AAC),"","(" & AAC & ")") & IIf(IsNull(AAF),""," not null") & " comment '" & AAH & "',"
WHERE (((SYS_AAAA_AAAH.AAO)=[forms]![frmAdmiTabl]![CombSAAO]));

DoCmd.OpenQuery can run it while Currentdb.Execute gives an error message 'too few parameters'. I created another saved update query without input from control or function and Currentdb.Execute worked. I don't want to see the warning message from Docmd.OpenQuery and I dont want to mess around by turning on and off the warning. Anyway of getting Currentdb.Execute work on this?

Upvotes: 0

Views: 619

Answers (1)

davidzxc574
davidzxc574

Reputation: 491

When you want to update a column of some certain records with another column's value and IIf function, it is better to use DAO.recordset edit and update 

  Dim Rs_AAAH As DAO.Recordset

    Set Rs_AAAH = CurrentDb.OpenRecordset("select * from Table where AAO='" & Me.CombSAAO.Value & "'", dbOpenDynaset)

    Rs_AAAH.MoveFirst

    Do Until Rs_AAAH.EOF

        With Rs_AAAH
            .Edit
            .Fields("AAK").Value = Rs_AAAH.Fields("AAA") & " " & Rs_AAAH.Fields("AAB") & IIf(IsNull(Rs_AAAH.Fields("AAC")), "", "(" & Rs_AAAH.Fields("AAC") & ")") & IIf(IsNull(Rs_AAAH.Fields("AAF")), "", " not null") & " comment '" & Rs_AAAH.Fields("AAH") & "',"
            .Update
        End With

        Rs_AAAH.MoveNext
    Loop

    Rs_AAAH.Close
    Set Rs_AAAH = Nothing

Upvotes: 0

Related Questions