Enter Parameter Value when using UPDATE in Visual Basic

[aJay]

Hi,

I'm trying to update a record using sql through visual basic, but get
an 'Enter Parameter Value' box when running it. Seems like linkCriteria
is not being passed to the sql, but why? I've used a MsgBox to check
linkCriteria is getting a value. Me.List4.Column(0) contains an
integer.

Any held appriciated.

Thanks.
_______________________________________________

Private Sub delete_Click()

Dim linkCriteria As Integer
Dim strSQL As String

linkCriteria = Me.List4.Column(0)
sql = "UPDATE investors SET investors.deleted = Yes WHERE
investors.investorid = linkCriteria;"
DoCmd.RunSQL sql

End Sub
_______________________________________________

 

[Douglas J. Steele]

You need to put linkCriteria outside of the string, so that it picks up the
value, not the variable name. If investorid is numeric, use

sql = "UPDATE investors SET investors.deleted = True " & _
"WHERE investors.investorid = " & linkCriteria

If it's text, you'll need to put quotes around the value:

sql = "UPDATE investors SET investors.deleted = True " & _
"WHERE investors.investorid = '" & linkCriteria & "'"

or

sql = "UPDATE investors SET investors.deleted = True " & _
"WHERE investors.investorid = " & Chr$(34) & linkCriteria & Chr$(34)

Note, too, that I changed the Yes you had to True: while it may be called a
Yes/No field in Access (and while Access may be coerced to display Yes and
No as values), you need to use True or False (or -1 or 0) as values.

 

['69 Camaro]

Hi, aJay.

I'm trying to update a record using sql through visual basic

Your code is missing Option Explicit in the Declarations Section of the current
module, so you need to add that. For more information, please see Access MVP
Tom Wickerath's Gem Tip on "Option Explicit" on the following Web page:

http://www.access.qbuilt.com/html/gem_tips.html

Try:

Private Sub delete_Click()

On Error GoTo ErrHandler

CurrentDb().Execute "UPDATE investors " & _
"SET investors.deleted = TRUE " & _
"WHERE investors.investorid = " & Me!List4.Column(0) & ";",
dbFailOnError

Exit Sub

ErrHandler:

MsgBox "Error in delete_Click( ) in" & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear

End Sub

I'd also recommend changing the name of your button from "Delete" to something
else, since Delete is a reserved word. List4 can be confusing as to what it
references, especially if you have 50 or more controls on the form with default
names and numbers, so changing that to something more descriptive, such as
lstInvestor, may help during future programming maintenance.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

 

[aJay]

This worked fine. Thanks.

aJay