Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Error executing update query from VBA?

Discussion in 'Information Technology' started by Chris Burnette, Nov 9, 2005.

  1. Chris Burnette

    Chris Burnette
    Expand Collapse
    Guest

    I am trying to run a couple of SQL queries based on input from a form. I
    know it's possible to use a parameter query to do this, but it doesn't seem
    to work for certain parameters, notably the field that you want to update.

    So, I created a form with a combo box and a couple of command buttons to
    allow me to choose which field I want to update.

    My code is below:

    Private Sub btnChange_Click()

    Dim myField As String
    Dim sSQL As String
    Dim numCount As Integer
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim fld As DAO.Field

    Set db = CurrentDb()
    myField = cboMyField.Value
    Set rst = db.OpenRecordset("EntireSpreadsheet_local")
    Set fld = rst.Fields(myField)

    sSQL = "UPDATE EntireSpreadsheet_local SET fld = CVDate(fld.Value) WHERE
    IsDate(fld) = True"
    db.Execute sSQL, dbFailOnError
    numCount = db.RecordsAffected
    MsgBox numCount & " records were updated by this query"

    End Sub

    The second Sub procedure is the same as the first, except that the SQL
    statement reads like this:

    sSQL = "UPDATE EntireSpreadsheet_local SET fld = '' WHERE fld='0'"

    When I try to run the first Sub procedure, I get an error message saying
    "Too few parameters: expected 2."

    When I try to run the second, I get the message: "Too few parameters:
    expected 1"

    In both cases, the debugger points me to this line:

    db.Execute sSQL, dbFailOnError

    I am not quite sure why I keep getting this error, as I clearly have the
    parameters defined. My SQL statements don't seem to be generating any errors
    in the debug window, and I use both parameters in the Execute method in both
    Sub procedures, yet one tells me 'Expected 2' and the other says 'Expected 1'.

    Does anyone have any idea why I might be getting this error? Any help would
    be appreciated.

    Thanks,

    Chris
     
  2. Loading...


  3. Dirk Goldgar

    Dirk Goldgar
    Expand Collapse
    Guest

    "Chris Burnette" <ChrisBurnette@discussions.microsoft.com> wrote in
    message news:6FFE32B2-6D47-4061-A8A5-D1F0FD9873A6@microsoft.com
    > I am trying to run a couple of SQL queries based on input from a
    > form. I know it's possible to use a parameter query to do this, but
    > it doesn't seem to work for certain parameters, notably the field
    > that you want to update.
    >
    > So, I created a form with a combo box and a couple of command buttons
    > to allow me to choose which field I want to update.
    >
    > My code is below:
    >
    > Private Sub btnChange_Click()
    >
    > Dim myField As String
    > Dim sSQL As String
    > Dim numCount As Integer
    > Dim db As DAO.Database
    > Dim rst As DAO.Recordset
    > Dim fld As DAO.Field
    >
    > Set db = CurrentDb()
    > myField = cboMyField.Value
    > Set rst = db.OpenRecordset("EntireSpreadsheet_local")
    > Set fld = rst.Fields(myField)
    >
    > sSQL = "UPDATE EntireSpreadsheet_local SET fld = CVDate(fld.Value)
    > WHERE IsDate(fld) = True"
    > db.Execute sSQL, dbFailOnError
    > numCount = db.RecordsAffected
    > MsgBox numCount & " records were updated by this query"
    >
    > End Sub
    >
    > The second Sub procedure is the same as the first, except that the SQL
    > statement reads like this:
    >
    > sSQL = "UPDATE EntireSpreadsheet_local SET fld = '' WHERE fld='0'"
    >
    > When I try to run the first Sub procedure, I get an error message
    > saying "Too few parameters: expected 2."
    >
    > When I try to run the second, I get the message: "Too few parameters:
    > expected 1"
    >
    > In both cases, the debugger points me to this line:
    >
    > db.Execute sSQL, dbFailOnError
    >
    > I am not quite sure why I keep getting this error, as I clearly have
    > the parameters defined. My SQL statements don't seem to be
    > generating any errors in the debug window, and I use both parameters
    > in the Execute method in both Sub procedures, yet one tells me
    > 'Expected 2' and the other says 'Expected 1'.
    >
    > Does anyone have any idea why I might be getting this error? Any
    > help would be appreciated.


    Access doesn't know anything about your recordset or your fld object, so
    all it knows is that it doesn't recognize the word "fld" as the name of
    any field in the table. But since you're getting the name of the field
    from the combo box and building your SQL string on the fly, you can just
    put the name of teh field directly into the SQL string:

    '----- start of revised code -----
    Private Sub btnChange_Click()

    Dim myField As String
    Dim sSQL As String
    Dim numCount As Integer
    Dim db As DAO.Database

    Set db = CurrentDb()
    myField = cboMyField.Value

    sSQL = _
    "UPDATE EntireSpreadsheet_local " & _
    "SET [" & myField & "] = CVDate([" & fld & "]) " & _
    "WHERE IsDate([" & fld & "]) = True"

    db.Execute sSQL, dbFailOnError
    numCount = db.RecordsAffected
    MsgBox numCount & " records were updated by this query"

    End Sub

    '----- end of revised code -----

    Note: I added the square brackets around the field name, wherever it is
    embedded in the SQL string, in case the name should contain spaces or
    other nonstandard characters.

    --
    Dirk Goldgar, MS Access MVP
    www.datagnostics.com

    (please reply to the newsgroup)
     
  4. Chris Burnette

    Chris Burnette
    Expand Collapse
    Guest

    Thanks Dirk, I tried your code and I am no longer getting an error message.
    The only other thing I had to change in order to get it to work properly was
    to change my WHERE statement to read:

    WHERE IsDate(CVDate([" & myField & "])) = True

    Anyway, it works!

    Thanks,

    -Chris

    "Dirk Goldgar" wrote:

    > "Chris Burnette" <ChrisBurnette@discussions.microsoft.com> wrote in
    > message news:6FFE32B2-6D47-4061-A8A5-D1F0FD9873A6@microsoft.com
    > > I am trying to run a couple of SQL queries based on input from a
    > > form. I know it's possible to use a parameter query to do this, but
    > > it doesn't seem to work for certain parameters, notably the field
    > > that you want to update.
    > >
    > > So, I created a form with a combo box and a couple of command buttons
    > > to allow me to choose which field I want to update.
    > >
    > > My code is below:
    > >
    > > Private Sub btnChange_Click()
    > >
    > > Dim myField As String
    > > Dim sSQL As String
    > > Dim numCount As Integer
    > > Dim db As DAO.Database
    > > Dim rst As DAO.Recordset
    > > Dim fld As DAO.Field
    > >
    > > Set db = CurrentDb()
    > > myField = cboMyField.Value
    > > Set rst = db.OpenRecordset("EntireSpreadsheet_local")
    > > Set fld = rst.Fields(myField)
    > >
    > > sSQL = "UPDATE EntireSpreadsheet_local SET fld = CVDate(fld.Value)
    > > WHERE IsDate(fld) = True"
    > > db.Execute sSQL, dbFailOnError
    > > numCount = db.RecordsAffected
    > > MsgBox numCount & " records were updated by this query"
    > >
    > > End Sub
    > >
    > > The second Sub procedure is the same as the first, except that the SQL
    > > statement reads like this:
    > >
    > > sSQL = "UPDATE EntireSpreadsheet_local SET fld = '' WHERE fld='0'"
    > >
    > > When I try to run the first Sub procedure, I get an error message
    > > saying "Too few parameters: expected 2."
    > >
    > > When I try to run the second, I get the message: "Too few parameters:
    > > expected 1"
    > >
    > > In both cases, the debugger points me to this line:
    > >
    > > db.Execute sSQL, dbFailOnError
    > >
    > > I am not quite sure why I keep getting this error, as I clearly have
    > > the parameters defined. My SQL statements don't seem to be
    > > generating any errors in the debug window, and I use both parameters
    > > in the Execute method in both Sub procedures, yet one tells me
    > > 'Expected 2' and the other says 'Expected 1'.
    > >
    > > Does anyone have any idea why I might be getting this error? Any
    > > help would be appreciated.

    >
    > Access doesn't know anything about your recordset or your fld object, so
    > all it knows is that it doesn't recognize the word "fld" as the name of
    > any field in the table. But since you're getting the name of the field
    > from the combo box and building your SQL string on the fly, you can just
    > put the name of teh field directly into the SQL string:
    >
    > '----- start of revised code -----
    > Private Sub btnChange_Click()
    >
    > Dim myField As String
    > Dim sSQL As String
    > Dim numCount As Integer
    > Dim db As DAO.Database
    >
    > Set db = CurrentDb()
    > myField = cboMyField.Value
    >
    > sSQL = _
    > "UPDATE EntireSpreadsheet_local " & _
    > "SET [" & myField & "] = CVDate([" & fld & "]) " & _
    > "WHERE IsDate([" & fld & "]) = True"
    >
    > db.Execute sSQL, dbFailOnError
    > numCount = db.RecordsAffected
    > MsgBox numCount & " records were updated by this query"
    >
    > End Sub
    >
    > '----- end of revised code -----
    >
    > Note: I added the square brackets around the field name, wherever it is
    > embedded in the SQL string, in case the name should contain spaces or
    > other nonstandard characters.
    >
    > --
    > Dirk Goldgar, MS Access MVP
    > www.datagnostics.com
    >
    > (please reply to the newsgroup)
    >
    >
    >
     
  5. Dirk Goldgar

    Dirk Goldgar
    Expand Collapse
    Guest

    "Chris Burnette" <ChrisBurnette@discussions.microsoft.com> wrote in
    message news:6876146F-7F67-4DA0-9FFD-0FD1F55BDAD7@microsoft.com
    > Thanks Dirk, I tried your code and I am no longer getting an error
    > message. The only other thing I had to change in order to get it to
    > work properly was to change my WHERE statement to read:
    >
    > WHERE IsDate(CVDate([" & myField & "])) = True


    Ah, I see I forgot to change from "fld" to "myfield" throughout the
    statement. Sorry about the typo.

    --
    Dirk Goldgar, MS Access MVP
    www.datagnostics.com

    (please reply to the newsgroup)
     

Share This Page