An update query: Not working!

[Martyh]

Hi Guys,

I have racked my brain over this :banghead: !! Probably something very simple

I want to run an update query on a table called "tblPMT_15". The update will be from a query called "[qry6_1_3_Latest]". Fig 1. shows the problem.

Those things which remain constant are the [Question ID] ("R3MyyN48vz") and [Question3] (" SMART Evaluation Objective"). But I also have to change the row number of the question, [QuestRow] and this is a variable which varies with [qry6_1_3_Latest].[QuestNo].

[Comment] is updated to [qry6_1_3_Latest].[SMART Evaluation Objective].

This is NOT working, soooo what am I doing wrong? Or am I looking at this the wrong way... any help would be appreciated!

Thanks,

Marty

 

[Minty]

You have a leading space in (" SMART Evaluation Objective") which may be causing things to not match as you expect?

 

[Martyh]

Sorry Minty ... It's not that!!

 

[Martyh]

I think i might I have to use some VBA ... now that I've banged my head even harder!!

 

[Minty]

I can't see [qry6_1_3_Latest] in your query design? How is it supposed to know those values?

 

[Martyh]

[qry6_1_3_Latest] in my query design is simply a query which lists the "SMART Eval obj" ([qry6_1_3_Latest].[SMART Evaluation Objective])for each Row -- [qry6_1_3_Latest].[QuestNo] ... and the more I think about it, the more I think I need VBA to cycle the rows.

Thanks for making me think --- helps me clarify the real problem!!

Here is a snippet in Fig2.jpg

Marty

 

[Martyh]

FYI Here is my code that I worked out the problem with!! I began to experiment with coded solution once my brain ran out of SQL ideas.. This can easily be expanded to a more general soln.

Does anybody know of a better solution?

Function UpdateSMART_Eval_Obj()

'UPDATE tblPMT_15 SET tblPMT_15.Comment = [qry6_1_3_Latest].[SMART Evaluation Objective]
'WHERE (((tblPMT_15.QuestRow)=[qry6_1_3_Latest].[QuestNo]) AND ((tblPMT_15.[Question ID])="R3MyyN48vz") AND ((tblPMT_15.Question3)=" SMART Evaluation Objective"));
Dim dbs As dao.Database
Dim rstFind As dao.Recordset
Dim rstChange As dao.Recordset

Set dbs = CurrentDb

Set rstInOrder = dbs.OpenRecordset("qry6_1_3_Latest")
Set rstFind = dbs.OpenRecordset("tblPMT_15", dbOpenDynaset)
    Do While Not rstInOrder.EOF
        
        'Find the correct place ie the QuestRow in tblPMT_15
        rstFind.FindFirst "[QuestRow] = " & rstInOrder!QuestNo
        rstFind.Edit

        'Replace the old "Comment" with the new "SMART Evaluation Objective"
        rstFind!Comment = rstInOrder![SMART Evaluation Objective]
        rstFind.Update
        
        rstInOrder.Move 1
    Loop

rstInOrder.Close
rstFind.Close


End Function