Hi all and thanks in advance for any help you might be able to provide,

I have a query that looks at a data table of risk item (questions) responses as fields (this is legacy data I am trying to incorporate) and then uses another query to find the recode value to construct a sql query string. I am using this approach because the questions that comprise the scale can change or or be modified and because I want the user to be able to select all risks or a risk domain (a risk type).

Here is the query I have made:

SELECT "UPDATE [rcData] SET [rcData].[" & [Variable] & "] = " & Nz([qryGetValues].[rcValue],"NULL") & " WHERE ([rcData].[" & [Variable] & "] = " & [qryGetValues].[Valuenumber] & ")" AS strSource, qryGetValues.fkRisks, qryGetValues.ID, qryGetValues.rcValue, qryGetValues.Variable, qryGetValues.Valuenumber, qryGetValues.Valuelabel
FROM qryGetValues
WHERE ((("UPDATE [rcData] SET [rcData].[" & [Variable] & "] = " & Nz([qryGetValues].[rcValue],"NULL") & " WHERE ([rcData].[" & [Variable] & "] = " & [qryGetValues].[Valuenumber] & ")") Not Like "*" & "] = WHERE (" & "*"))
ORDER BY qryGetValues.Valuenumber;



I have tried making a macro and converting it to vba it is as follows:

Function mroRecode()
On Error GoTo mroRecode_Err

    DoCmd.OpenQuery "qryRecode", acViewNormal, acReadOnly
    DoCmd.RunSQL "qryRecode!strSource.value", -1
    DoCmd.FindNext


mroRecode_Exit:
    Exit Function

mroRecode_Err:
    MsgBox Error$
    Resume mroRecode_Exit

End Function

I am really more of a data person than a programmer, so I think it needs a loop maybe? Any ideas?

I am still not sure the actions this should be taking. What is the flow control? Are you trying to update all of your legacy data at once, or just as needed? The query syntax seems wrong. I would try breaking this into several queries and then looping inside the queries as you iterate through a larger recordset.

What does the data look like before the action, and what should it look like after? Then we can do flow control.

HTH

mmcdonal