Can't get update query to work with recordset values 2

[cdgeer]

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

 

[Andrzejek]

Try:

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.

 

[dhookom]

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.

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

 

[cdgeer]

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!!

 

[dhookom]

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

 

[cdgeer]

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

 

[Andrzejek]

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

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

Do Until rst.EOF[green]
    'strRegionOrder = rst![RegionOrder]
    'strInScope = rst![InScope][/green]
    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'

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.

 

[cdgeer]

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