Solved

Using UPDATE statement in Access VBA

Posted on 2014-11-13
3
140 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 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 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 47

Expert Comment

by:Dale Fye (Access MVP)
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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

776 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