Error executing update query from VBA?

[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

 

[Dirk Goldgar]

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.

 

[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

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)

 

[Dirk Goldgar]

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.