Update Query?? or RecordSet??

[bbrazeau]

I have tblParts, tblRfqs, tblRfqDetails,tblQuotes. tblParts has a field txtStatus among others. The value of this field can be "Quoting", "Quoted" or "P.O. Issued"(taken from a value list not a lookup from another table). Table tblRfqDetails is a join table with PartID and RfqID as fields among others. I would like to have a command button on the Quoting form (1 to1 relationship with Rfqs) that would change the status of the Parts that are in the RfqDetails subform to "P.O. Issued". Then open a tblPO to a new record and fill in a field Quote with the current QuoteID.
I have an update query that I think will work.(criteria to find correct Rfq in fqDetails is [Forms]![frmQuotes]![Rfq] now how do I get the command button to run this code? I tried docmd.runquery theres no such command. Can I use the runSQL command? If so what would the code line look like. I've used an SQL statement before to limit choices in a combo box and I had to add double Quote marks around 1 of the where conditions.

[June7]

You can open a saved Access query with DoCmd.OpenQuery. The OpenQuery method opens a select or crosstab query in Datasheet view, Design view, or Print Preview - or runs an action query from code.

Or you can build the SQL in code and run it.
DoCmd.RunSQL "UPDATE ..."

Both of those will provoke warning messages. To avoid them use DoCmd.SetWarnings False and DoCmd.SetWarnings True.

Another method to run SQL actions is (no warning popups):
CurrentDb.Execute "UPDATE ..."

Review this article http://www.databasejournal.com/featu...n-VBA-Code.htm

[bbrazeau]

Thank you June7, Following is the code I used to make this work. I will impliment the DoCmd.SetWarnings you mentioned and the error checking mention in the article you referenced. I'm going to experiment with the SQL I wrote also. I think it may be more complex than it needs to be. I'm going to mark this thread as solved. Again Thank you.

Private Sub cmdNewPo_Click()
Dim strSQL As String
strSQL = "UPDATE tblParts INNER JOIN (tblRfqs INNER JOIN tblRfqDetails ON tblRfqs.RfqID = tblRfqDetails.Rfq) ON tblParts.PartID = tblRfqDetails.Part SET tblParts.PartStatus = ""P.O. Received"" WHERE (((tblRfqDetails.Rfq)=[Forms]![frmQuotes]![Rfq]) AND ((tblRfqDetails.Part)=[tblParts]![PartID]));"
If DCount("Quote", "tblPOs", "Quote =" & Me.QuoteID) = 0 Then
DoCmd.RunSQL strSQL
DoCmd.OpenForm "frmPOsAdd", , , , acFormAdd
Else
MsgBox ("This Quote already has a Purchase Order")
End If
End Sub