0

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?

braX
  • 11,506
  • 5
  • 20
  • 33
davidzxc574
  • 471
  • 1
  • 8
  • 21
  • 1
    They behave differently: [How do I use parameters in VBA in the different contexts in Microsoft Access?](https://stackoverflow.com/questions/49509615/how-do-i-use-parameters-in-vba-in-the-different-contexts-in-microsoft-access) – Andre Dec 20 '19 at 14:20
  • It is good to know this but it did not really pointing the direction of a solution to my problem – davidzxc574 Dec 23 '19 at 06:31

1 Answers1

0

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
davidzxc574
  • 471
  • 1
  • 8
  • 21