Update Query Using Recordset But Criteria Comes From Out Of Set 1

[DomDom3]

Hi there,

I have an update query shown below:-

rs.Open "select [date] from vreport_workpackage_dates WHERE (WPID = 2878) and (milestone = 'integration end')", conn

strSQL = "UPDATE WorkPackages1 SET [planned start date]='" & Format(rs.Fields(0), "yyyy-mm-dd") & "' WHERE WPID=2878"
docmd.runsql

Where I have the criteria as 2878 in the rs.open statement I'd like replace this the value in a control on the form that triggers the query.

My problem is that if I put the control name in there it errors because the control is not part of the recordset.

Can anyone help with this please?

 

[Remou]

This should be possible. Have you tried (?):

[tt]strSQL = "UPDATE WorkPackages1 SET [planned start date]=#" & Format(rs.Fields(0), "yyyy-mm-dd") & "# WHERE WPID=" & Me.WPID
docmd.runsql[/tt]

Date fields need hash (#) delimiters. If WPID is not numeric, you will need single quotes.

 

[DomDom3]

Thanks Remou,

That was my first action to try "me.WPID" but it errored, that's what led me to think I had find another way to reference a control outside of the recordset - but when I added in your suggestion it worked (without the # delimiters - the result of another thread).

Alls well that ends well though. Thanks