Update query with Where condition

  • Thread starter Thread starter TM
  • Start date Start date
T

TM

I am trying to run an update query in VBA but keep
getting the error message #3061, too few parameters,
expected 1"

I looked this up and find that I apparently need to use
the EVAL() statement. Only, I am confused as to why and
where!? I understand, I think, what it does, but I as I
said, why and where. I am just trying to update a field
in a table if a customer is selected from a list box.
The where condition is on the customerid field, so there
is always an entry for it. The field to be updated is
default of "no" and needs to be "yes" if selected. So,
there is no value in that field to begin with.

Here is my statement:
db.Execute("UPDATE [tblCustomer] SET tblCustomer.
[newsletter] = Yes WHERE (((tblCustomer.CustomerID)
=varID")
 
Hi,

You have, in all, four ( but only two )



Try:

db.Execute("UPDATE tblCustomer SET newsletter= -1WHERE CustomerID=" &
varID)



assuming CustomerID is numerical.

Hoping it may help,
Vanderghast, Access MVP
 
Okay, well it worked on the first one, now I am getting
an error... "object variable or with block variable not
set". The error is occurring on the db.execute line. I
don't understand why I am getting this error here and not
on the other. They are both used within a "for each"
loop - and, as far as I have noticed so far, all the
variables are declared. (obviously, the line does not
continue on in my actual code) Here is the code:

Dim varPosition As Variant
Dim varItem As Variant
Dim db As Database

If Customers.ItemsSelected.Count = 0 Then
MsgBox "You must select at least one customer."
Exit Sub
End If

For Each varPosition In Customers.ItemsSelected
varItem = Customers.ItemData(varPosition)
** db.Execute ("UPDATE tblCustomer SET newsletter= -
1 WHERE CustomerID=" & varItem)
Next varPosition

DoCmd.OpenReport "rptMarketing Mindset", acPreview

Me("Box229").Visible = False
Me("label6").Visible = False
Me("Customers").Visible = False
Me("Update").Visible = False
 
Hi,


Instead of

Dim db As Database


try

Dim db As Database : Set db=CurrentDb



Hoping it may help,
Vanderghast, Access MVP
 
Okay, DUH!!!! THANKS!!!! That was it.
-----Original Message-----
Hi,


Instead of

Dim db As Database


try

Dim db As Database : Set db=CurrentDb



Hoping it may help,
Vanderghast, Access MVP





.
Click to expand...
 
Back
Top