One click event, several action queries

[Joan]

Hi,

I have a click event on an Invoice form where I want to run several action
queries. The problem is that the click event runs the first action query
but not the others. Why is it doing this??

Below is the code behind the click event:

Private Sub RecordInvoice_Click()
On Error GoTo Err_RecordInvoice_Click

Dim stDocName As String
Dim stQueryName As String
Dim FirstUpdate As String

FirstUpdate = "SalesRetUpdateQuery"
DoCmd.SetWarnings False
DoCmd.OpenQuery FirstUpdate, acViewNormal, acEdit
DoCmd.SetWarnings True

stDocName = "SalesAppendQuery"
DoCmd.SetWarnings False
DoCmd.OpenQuery stDocName, acViewNormal, acEdit
DoCmd.SetWarnings True

stQueryName = "qryUpdateAdjustments"

DoCmd.SetWarnings False
DoCmd.OpenQuery stQueryName, acViewNormal, acEdit
DoCmd.SetWarnings True
Me.cboType.SetFocus
Me.RecordInvoice.Enabled = False
Me.Recalc
Exit_RecordInvoice_Click:
Exit Sub
Err_RecordInvoice_Click:
MsgBox Err.Description
Resume Exit_RecordInvoice_Click

End Sub

Below are the SQLs for the action queries:

SalesRetUpdateQuery: UPDATE Sales SET Sales.ReturnedSaleDate =
Forms!InvoiceForm!DateSold, Sales.ReturnedInvoice =
Forms!InvoiceForm!txtInvoiceNumber
WHERE (((Sales.Returned)="Y") AND ((Sales.[Dog
Number])=[Forms]![InvoiceForm]![InvoiceSubform]![Dog Number]));


SalesAppendQuery: PARAMETERS [Forms]![InvoiceForm]![StoreCode] Text (
255 );
INSERT INTO Sales ( [Dog Number], [Invoice Number] )
SELECT qryInvoiceSubform2.[Dog Number], Invoices.[Invoice Number]
FROM Invoices, qryInvoiceSubform2
WHERE (((Invoices.[Invoice
Number])=[Forms]![InvoiceForm]![txtInvoiceNumber]) AND
((qryInvoiceSubform2.Store)=[Forms]![InvoiceForm]![StoreCode]));

qryUpdateAdjustments: PARAMETERS [Forms]![InvoiceForm]![StoreCode] Text
( 255 ), [Forms]![InvoiceForm]![DateSold] DateTime;
UPDATE Invoices SET Invoices.OnInvoice = Forms!InvoiceForm!txtInvoiceNumber
WHERE (((Invoices.Type)<>"INV") AND
((Invoices.Store)=[Forms]![InvoiceForm]![StoreCode]) AND
((Invoices.DateSold)>[Forms]![InvoiceForm]![DateSold]-7));


SalesRetUpdateQuery updates Sales so that if a returned dog is sold again,
the sale date and Invoice Number of the resale is entered in
Sales.ReturnedSaleDate and Sales.ReturnedInvoice of the first sale record.

SalesAppendQuery adds the Invoice Number from the InvoiceForm and the Dog
Number from the InvoiceSubform to the Sales table.

qryUpdateAdjustments records the Invoice Number of the invoice that shows a
credit or adjustment in the OnInvoice field of the credit or adjustment
transaction record.

Thanks ahead of time for any assistance in figuring this out.

Joan

 

[Allen Browne]

Temporarily comment out the error handler until you get everything working
properly. This makes it easier to see where an error is occurring.

Instead of OpenQuery, try the Execute method with the dbFailOnError switch.
This way to get to hear about it if something doesn't work. There is no need
to SetWarnings off and on again with the Execute method either.

Your queries contain parameters. You will need to programmatically provide
the parameters to the QueryDef before you Execute the QueryDef.

Alternatively, you could just build the query string in your code based on
text boxes in the form. Generally that's a nicer interface for the user, is
simpler than supplying parameters, reduces the number of saves queries, and
avoids the dependency between your code and the saved query.

If you want an all-or-nothing result from your action queries, you may also
wish to place them in a transaction. More info on how to work with
transactions:
http://allenbrowne.com/ser-37.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi,

I have a click event on an Invoice form where I want to run several action
queries. The problem is that the click event runs the first action query
but not the others. Why is it doing this??

Below is the code behind the click event:

Private Sub RecordInvoice_Click()
On Error GoTo Err_RecordInvoice_Click

Dim stDocName As String
Dim stQueryName As String
Dim FirstUpdate As String

FirstUpdate = "SalesRetUpdateQuery"
DoCmd.SetWarnings False
DoCmd.OpenQuery FirstUpdate, acViewNormal, acEdit
DoCmd.SetWarnings True

stDocName = "SalesAppendQuery"
DoCmd.SetWarnings False
DoCmd.OpenQuery stDocName, acViewNormal, acEdit
DoCmd.SetWarnings True

stQueryName = "qryUpdateAdjustments"

DoCmd.SetWarnings False
DoCmd.OpenQuery stQueryName, acViewNormal, acEdit
DoCmd.SetWarnings True
Me.cboType.SetFocus
Me.RecordInvoice.Enabled = False
Me.Recalc
Exit_RecordInvoice_Click:
Exit Sub
Err_RecordInvoice_Click:
MsgBox Err.Description
Resume Exit_RecordInvoice_Click

End Sub

Below are the SQLs for the action queries:

SalesRetUpdateQuery: UPDATE Sales SET Sales.ReturnedSaleDate =
Forms!InvoiceForm!DateSold, Sales.ReturnedInvoice =
Forms!InvoiceForm!txtInvoiceNumber
WHERE (((Sales.Returned)="Y") AND ((Sales.[Dog
Number])=[Forms]![InvoiceForm]![InvoiceSubform]![Dog Number]));


SalesAppendQuery: PARAMETERS [Forms]![InvoiceForm]![StoreCode] Text (
255 );
INSERT INTO Sales ( [Dog Number], [Invoice Number] )
SELECT qryInvoiceSubform2.[Dog Number], Invoices.[Invoice Number]
FROM Invoices, qryInvoiceSubform2
WHERE (((Invoices.[Invoice
Number])=[Forms]![InvoiceForm]![txtInvoiceNumber]) AND
((qryInvoiceSubform2.Store)=[Forms]![InvoiceForm]![StoreCode]));

qryUpdateAdjustments: PARAMETERS [Forms]![InvoiceForm]![StoreCode] Text
( 255 ), [Forms]![InvoiceForm]![DateSold] DateTime;
UPDATE Invoices SET Invoices.OnInvoice = Forms!InvoiceForm!txtInvoiceNumber
WHERE (((Invoices.Type)<>"INV") AND
((Invoices.Store)=[Forms]![InvoiceForm]![StoreCode]) AND
((Invoices.DateSold)>[Forms]![InvoiceForm]![DateSold]-7));


SalesRetUpdateQuery updates Sales so that if a returned dog is sold again,
the sale date and Invoice Number of the resale is entered in
Sales.ReturnedSaleDate and Sales.ReturnedInvoice of the first sale record.

SalesAppendQuery adds the Invoice Number from the InvoiceForm and the Dog
Number from the InvoiceSubform to the Sales table.

qryUpdateAdjustments records the Invoice Number of the invoice that shows a
credit or adjustment in the OnInvoice field of the credit or adjustment
transaction record.

Thanks ahead of time for any assistance in figuring this out.

Joan