Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Using UPDATE statement in Access VBA

Posted on 2014-11-13
3
Medium Priority
?
178 Views
Last Modified: 2014-11-13
I am having a problem getting the below statement to work.  It doesn't give me an error but also does not update the value in my table.

strSQL = "Select * from [tblSourcedHires] where SourceHireID = " & intSourceHireID & " "

                    Set rs = db.OpenRecordset(strSQL)

                        If rs.RecordCount > 0 Then

                            strSQL = "UPDATE [tblSourcedHires] SET(RebateApplied) = " & strRebateApplied & " where SourceHireID = " & intSourceHireID & ""

                            db.Execute (strSQL)
                        End If

Open in new window

0
Comment
Question by:marku24
  • 2
3 Comments
 
LVL 49

Accepted Solution

by:
Dale Fye earned 2000 total points
ID: 40441257
Is this supposed to only update a single record, or multiple?
strSQL = "SELECT * from [tblSourcedHires] " _
       & "WHERE SourceHireID = " & intSourceHireID

Set rs = db.OpenRecordset(strSQL)

While not rs.EOF
    rs.Edit
    rs!RebateApplied = strRebateApplied
    rs.Update
    rs.movenext 
WEND

rs.Close
Set rs = nothing

Open in new window

0
 

Author Comment

by:marku24
ID: 40441266
single record
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 40441299
The syntax I provided above would handle single or multiple, although I prefer to use an update query if there are multiples.

Or you could simply use:
strSQL = "UPDATE [tblSourcedHires] " _
     & "SET [RebateApplied] = " & strRebateApplied & " " _
     & "WHERE SourceHireID = " & intSourceHireID
debug.print strsql
Currentdb.Execute strsql, dbfailonerror

Open in new window

If strRebateApplied is actually a string, then you would probably want to use:
strSQL = "UPDATE [tblSourcedHires] " _
     & "SET [RebateApplied] = '" & strRebateApplied & "' " _
     & "WHERE SourceHireID = " & intSourceHireID
debug.print strsql
Currentdb.Execute strsql, dbfailonerror

Open in new window


'note that this latter version contains single quotes.
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question