INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Can't get update query to work with recordset values

Can't get update query to work with recordset values

(OP)
I'm having trouble getting an UPDATE query to pick up values from a recordset. When I run the code, I get parameter requests for the string values in the statement. When I go to Debug and hover over the string = rst![field] , it shows the value being picked up from the recordset but the SQL statement isn't picking it up. Could anyone let me know what I'm missing? Thanks in advance!

Private Sub cmdOpenMPLreport_Click()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim strSQLRST As String
Dim strRegionOrder As String
Dim strInScope As String
Dim Regions As AccessObject
Dim MPL_Report As AccessObject

strSQLRST = "SELECT RegionOrder, InScope FROM Regions;"
Set db = CurrentDb
Set rst = db.OpenRecordset(strSQLRST)

Do Until rst.EOF
strRegionOrder = rst![RegionOrder]
strInScope = rst![InScope]
strSQL = "UPDATE [MPL_Report] SET strRegionOrder = 'TBD' WHERE (((strRegionOrder)Is Null) AND ((strInScope)= Yes));"

DoCmd.RunSQL strSQL

rst.MoveNext

Loop


End Sub

RE: Can't get update query to work with recordset values

Try:

CODE

strSQLRST = "SELECT RegionOrder, InScope FROM Regions;"
Set db = CurrentDb
Set rst = db.OpenRecordset(strSQLRST)

Do Until rst.EOF
    strRegionOrder = rst![RegionOrder]
    strInScope = rst![InScope]
    strSQL = "UPDATE [MPL_Report] " _
        & " SET " & strRegionOrder & " = 'TBD' " _
        & " WHERE (((" & strRegionOrder & ")Is Null) " _
        & " AND ((" & strInScope & ")= Yes));"

    Debug.Print strSQL

    DoCmd.RunSQL strSQL
    rst.MoveNext
Loop 

But it looks like you are using rst Values in place of field Names

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

RE: Can't get update query to work with recordset values

To augment Andy's code, I would add [] in the event the strings contain spaces. I would also assume the Yes is a bit or yes/no field so I would use -1. Also note Andy's use of the debug.print statement which should help with debugging.

CODE --> vba

strSQLRST = "SELECT RegionOrder, InScope FROM Regions;"
Set db = CurrentDb
Set rst = db.OpenRecordset(strSQLRST)

Do Until rst.EOF
    strRegionOrder = rst![RegionOrder]
    strInScope = rst![InScope]
    strSQL = "UPDATE [MPL_Report] " _
        & " SET [" & strRegionOrder & "] = 'TBD' " _
        & " WHERE [" & strRegionOrder & "] Is Null " _
        & " AND [" & strInScope & "] = -1;"

    Debug.Print strSQL

    DoCmd.RunSQL strSQL
    rst.MoveNext
Loop 

Duane
Hook'D on Access
MS Access MVP

RE: Can't get update query to work with recordset values

(OP)
Thanks Duane! I know none of the strings had spaces but, I put them in anyway and I think that leaving out the parenthesis in the WHERE statement and the -1 for the yes/no value did the trick.
Works perfectly!!

RE: Can't get update query to work with recordset values

You should also note the good practice of using the code TGML tag. Tek-Tips makes it easy to add this formatting and your post would be easier to read. Compare your post with Andy's and mine.

Duane
Hook'D on Access
MS Access MVP

RE: Can't get update query to work with recordset values

(OP)

CODE --> VB

OK, Not sure how to do that. So I'm testing. 

RE: Can't get update query to work with recordset values

To modify the code even more, you can skip all the string variables and do this:

CODE

strSQLRST = "SELECT RegionOrder, InScope FROM Regions;"
Set db = CurrentDb
Set rst = db.OpenRecordset(strSQLRST)

Do Until rst.EOF
    'strRegionOrder = rst![RegionOrder]
    'strInScope = rst![InScope]
    strSQL = "UPDATE [MPL_Report] " _
        & "   SET [" & rst![RegionOrder] & "] = 'TBD' " _
        & " WHERE [" & rst![RegionOrder] & "] Is Null " _
        & "   AND [" & rst![InScope] & "] = -1;"

    Debug.Print strSQL

    DoCmd.RunSQL strSQL
    rst.MoveNext
Loop 

I hope you don’t have any single quotes in your rst/Update sql, because that’s another 'can of worms' smile

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

RE: Can't get update query to work with recordset values

(OP)
Thanks Andy! I didn't think of that. That saves another step. :)
I'll definitely use that in the future!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close