J
Joan
Hi,
I have an InvoiceForm where I have a "Record Invoice" button. The click
event of this button is supposed to run 3 action queries, an append query
and two update queries. The problem that I am having is that my code which
I included below works fine on my machine but not on my customer's. I have
Access 2002 on my machine and they have Access 2003 on there's. The
application is designed in Access 2002. Could anyone tell me after looking
at my code and the action query if there is some reason why this is not
working on my customer's computer but is on mine? It is just the second
query, "UpdateFinalStoreRetDog", in my code which doesn't work on their
machine.
Private Sub RecordInvoice_Click()
On Error GoTo Err_RecordInvoice_Click
Dim stDocName As String
Dim stQueryName As String
Dim FirstUpdate As String
'Run action query to append Dog Numbers of dogs on invoice, the _
Invoice Number of this invoice and the SalesPrice for each dog on the _
invoice to the Sales table.
stDocName = "SalesAppendQuery"
DoCmd.SetWarnings False
DoCmd.OpenQuery stDocName, acViewNormal, acEdit
DoCmd.SetWarnings True
Me.Recalc
'For dogs that have been sold on this invoice, append the StoreCode
' from this form to the dog's FinalStore field.
FirstUpdate = "UpdateFinalStoreRetDog"
DoCmd.SetWarnings False
DoCmd.OpenQuery FirstUpdate, acViewNormal, acEdit
DoCmd.SetWarnings True
Me.Recalc
' action query to enter Invoice Number in OnInvoice field of any
adjustments records made _
since the last invoice to the store.
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
The second query is supposed to update the FinalStore field in every dog's
record on the subform to the value of txtstore in the subform. This action
will effectively take the dog out of inventory as the criteria for the
Inventory form is when FinalStore is Null. Here is the SQL of the
"UpdateFinalStoreRetDog" query which isn't working.
UPDATE Dogs SET Dogs.FinalStore = Forms!InvoiceForm!InvoiceSubform!txtstore
WHERE (((Dogs.[Dog Number]) In (SELECT [Dog Number] FROM qryInvoiceSubform2
WHERE [Store] = [Store])));
I am very puzzled as to why this is happening as it seems like it should
work.
Joan
PS. The SalesAppendQuery right before the query which isn't working:
PARAMETERS [Forms]![InvoiceForm]![StoreCode] Text ( 255 );
INSERT INTO Sales ( [Dog Number], InvSalePrice, [Invoice Number] )
SELECT qryInvoiceSubform2.[Dog Number], qryInvoiceSubform2.SalesPrice,
Invoices.[Invoice Number]
FROM qryInvoiceSubform2, Invoices
WHERE (((Invoices.[Invoice
Number])=[Forms]![InvoiceForm]![txtInvoiceNumber]) AND
((qryInvoiceSubform2.Store)=[Forms]![InvoiceForm]![StoreCode]));
I have an InvoiceForm where I have a "Record Invoice" button. The click
event of this button is supposed to run 3 action queries, an append query
and two update queries. The problem that I am having is that my code which
I included below works fine on my machine but not on my customer's. I have
Access 2002 on my machine and they have Access 2003 on there's. The
application is designed in Access 2002. Could anyone tell me after looking
at my code and the action query if there is some reason why this is not
working on my customer's computer but is on mine? It is just the second
query, "UpdateFinalStoreRetDog", in my code which doesn't work on their
machine.
Private Sub RecordInvoice_Click()
On Error GoTo Err_RecordInvoice_Click
Dim stDocName As String
Dim stQueryName As String
Dim FirstUpdate As String
'Run action query to append Dog Numbers of dogs on invoice, the _
Invoice Number of this invoice and the SalesPrice for each dog on the _
invoice to the Sales table.
stDocName = "SalesAppendQuery"
DoCmd.SetWarnings False
DoCmd.OpenQuery stDocName, acViewNormal, acEdit
DoCmd.SetWarnings True
Me.Recalc
'For dogs that have been sold on this invoice, append the StoreCode
' from this form to the dog's FinalStore field.
FirstUpdate = "UpdateFinalStoreRetDog"
DoCmd.SetWarnings False
DoCmd.OpenQuery FirstUpdate, acViewNormal, acEdit
DoCmd.SetWarnings True
Me.Recalc
' action query to enter Invoice Number in OnInvoice field of any
adjustments records made _
since the last invoice to the store.
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
The second query is supposed to update the FinalStore field in every dog's
record on the subform to the value of txtstore in the subform. This action
will effectively take the dog out of inventory as the criteria for the
Inventory form is when FinalStore is Null. Here is the SQL of the
"UpdateFinalStoreRetDog" query which isn't working.
UPDATE Dogs SET Dogs.FinalStore = Forms!InvoiceForm!InvoiceSubform!txtstore
WHERE (((Dogs.[Dog Number]) In (SELECT [Dog Number] FROM qryInvoiceSubform2
WHERE [Store] = [Store])));
I am very puzzled as to why this is happening as it seems like it should
work.
Joan
PS. The SalesAppendQuery right before the query which isn't working:
PARAMETERS [Forms]![InvoiceForm]![StoreCode] Text ( 255 );
INSERT INTO Sales ( [Dog Number], InvSalePrice, [Invoice Number] )
SELECT qryInvoiceSubform2.[Dog Number], qryInvoiceSubform2.SalesPrice,
Invoices.[Invoice Number]
FROM qryInvoiceSubform2, Invoices
WHERE (((Invoices.[Invoice
Number])=[Forms]![InvoiceForm]![txtInvoiceNumber]) AND
((qryInvoiceSubform2.Store)=[Forms]![InvoiceForm]![StoreCode]));