Update query after an append query?+

[Joan]

Could someone tell me what I need to do when I am running 3 action queries
in the OnClick event procedure of a button on a form ( InvoiceForm)? In
particular when I first run an append query followed by an update query.
When I run my code below, the first query, an append query works just fine,
but the second query, an update query doesn't work. Correction, the update
query works on my computer which has Access 2002 but not on the customer's
computer which has Access 2003. Nothing else as far a I can tell is
dissimiliar between the two. Cannot find anything in the literature on
Access 2003 which would indicate I need to code this differently so am
puzzled by why it won't work.

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 query, UpdateFinalStoreRetDog, is supposed to put the Store that the dog
is sold to in the FinalStore field, which will in effect take the dog record
off of the inventory screen because of criteria set in the inventory
screen's underlying query. The dog's record however does not come off of
the inventory screen when the dog is invoiced like it is supposed to. Below
is the SQL for the update query which will not work in Access 2003.

UPDATE Dogs SET Dogs.FinalStore = Forms!InvoiceForm!StoreCode
WHERE (((Dogs.[Dog Number])=[Forms]![InvoiceForm]![InvoiceSubform]![Dog
Number]));

Here is the SQL for the append query which precedes the update query:
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 would so appreciate any help with this. It is driving me bonkers.

Joan

 

[Guest]

Joan
First all you need to do is DoCmd.SetWarnings to False once and then set it back to True at the end of your code
Second, Are you on a network? If you are try the DoCmd.acSaveRecord on your Table File. If you are not you might have too much running in background

I always have problems with Access going from one version to another and I have over 375 schools that I am the application programmer for. The best thing is to try to work on the new Access machine if possible

Also you only need the me.recalc at the end of your update query.

You can also try the DoEvents command to make sure everything is checked

You can probably remove about 40% of your code. In object orented programming to want to use as few command as possible. On locating the last Invoice number, you might want to tr

Dim db as Databas
Dim rst as Recordse
Dim LastInv as Intege

Set db = CurrentD

Set rst = db.openrecordset("SELECT * FROM DOGFILE"
rst.Movelas
LastInv = rst!InvNumber +
rst.clos

Now move LastInv to whereever you need it

God Bless and Good Luc

Len

 

[Joan]

Len,
Thanks for your reply.
Still not having much luck. First of all, I installed Access 2003 on my
computer so that I could tell when and if I get the update query to work. I
put the DoCmd.SetWarnings in my code like you recommended. I just put
me.recalc at the end of my update query. I am not sure where to put
DoEvents in my code as I do not quite understand what DoEvents does. I
tried putting it in different places in my code, i.e. before opening the
update query and then after, to no avail. I am also not sure where to put
DoCmd.Save acTable, "Dogs" in my code. The query does not work on my
computer now and my computer is not on a network anyway. Further, I did not
understand the purpose of locating the last Invoice Number as it is the Dog
record(s) in the current form's ( read current invoice's) subform that I am
concerned with. With some of the above changes to my code, the update query
still did not work.

So then I tried running the update query in an event all by itself and it
still did not work. So I believe the problem is with the query itself. I
found something in Help in my new Access 2003 under "Troubleshoot queries"
that if you created your query under a different ANSI SQL query mode than
the current mode of your Microsoft Access database then your update query
may not work. The two ANSI SQL query modes, ANSI-89 and ANSI-92 are not
compatible. I'm not sure if this applies in my case or not. When I first
started developing this database a long time ago, I started development in
Access 2000. Then I upgraded to Access 2002 with no problems. Several weeks
ago, I made some major changes to some of the table structures, and got
everything working fine. When I went to install it at the customer's , I
noticed that the application was in the Access 2000 mode so I converted it
to Access 2003. Not sure how it got in Access 2000 in the first place
unless when I was reworking part of the database, I started with an older
copy of the database inadvertantly. At any rate, everything else works fine
except this update query. So then believing that it was a problem with the
query, I deleted it from the database and rebuilt it with a different name.
I also substituted this different name in my code, but it still doesn't
work. What do you think? Could it be a problem with the query being
created under a different ANSI SQL query mode than the current mode of my
database? If so, what do I do?

Code as I have it now:

Private Sub RecordInvoice_Click()
On Error GoTo Err_RecordInvoice_Click

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

DoCmd.SetWarnings False

'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.OpenQuery stDocName, acViewNormal, acEdit

'For dogs that have been sold on this invoice, append the StoreCode
' from this form to the dog's FinalStore field.
FirstUpdate = "UpdateFinalStore"
DoCmd.OpenQuery FirstUpdate, acViewNormal, acEdit
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.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

SQL of UpdateFinalStore query:
UPDATE Dogs SET Dogs.FinalStore =
[Forms]![InvoiceForm]![InvoiceSubform]![txtStore]
WHERE (((Dogs.[Dog
Number])=[Forms]![InvoiceForm]![InvoiceSubform]![txtDogNum]));

I changed the name of the control, [Dog Number] on the subform to txtDogNum
as I thought maybe
the query did not like a control with the same name as a field name.

Don't know where to go from here.

Joan

Joan;
First all you need to do is DoCmd.SetWarnings to False once and

then set it back to True at the end of your code.

Second, Are you on a network? If you are try the

DoCmd.acSaveRecord on your Table File. If you are not you might have too
much running in background.

I always have problems with Access going from one version to

another and I have over 375 schools that I am the application programmer
for. The best thing is to try to work on the new Access machine if
possible.

Also you only need the me.recalc at the end of your update query.

You can also try the DoEvents command to make sure everything is checked.

You can probably remove about 40% of your code. In object orented

programming to want to use as few command as possible. On locating the last
Invoice number, you might want to try

 

[Joan]

Len,
Out of desperation, I went back to my query in design view and used the
Expression builder for the Update To and Criteria expressions. Below is
the new query and it works if there is only one dog on the subform. Notice
the WHERE statement where it put .[Form]! after [InvoiceSubform]. Also I
changed the Update To expression to equal the Store control on the main form
instead of the one on the subform. This is the only difference as far as I
can tell. It works if there is only one dog on the subform. If there is
more than one dog, it only updates the first dog record on the subform and
not the rest. What do I need to do differently, to get all of the dogs
records to update?


UPDATE Dogs SET Dogs.FinalStore = [Forms]![InvoiceForm]![StoreCode]
WHERE (((Dogs.[Dog
Number])=[Forms]![InvoiceForm]![InvoiceSubform].[Form]![txtDogNum]));

Joan

Joan;
First all you need to do is DoCmd.SetWarnings to False once and

then set it back to True at the end of your code.

Second, Are you on a network? If you are try the

DoCmd.acSaveRecord on your Table File. If you are not you might have too
much running in background.

I always have problems with Access going from one version to

another and I have over 375 schools that I am the application programmer
for. The best thing is to try to work on the new Access machine if
possible.

Also you only need the me.recalc at the end of your update query.

You can also try the DoEvents command to make sure everything is checked.

You can probably remove about 40% of your code. In object orented

programming to want to use as few command as possible. On locating the last
Invoice number, you might want to try

 

[Jonathan Parminter]

-----Original Message-----
Len,
Out of desperation, I went back to my query in design view and used the
Expression builder for the Update To and Criteria expressions. Below is
the new query and it works if there is only one dog on the subform. Notice
the WHERE statement where it put .[Form]! after [InvoiceSubform]. Also I
changed the Update To expression to equal the Store control on the main form
instead of the one on the subform. This is the only difference as far as I
can tell. It works if there is only one dog on the subform. If there is
more than one dog, it only updates the first dog record on the subform and
not the rest. What do I need to do differently, to get all of the dogs
records to update?


UPDATE Dogs SET Dogs.FinalStore = [Forms]![InvoiceForm]! [StoreCode]
WHERE (((Dogs.[Dog
Number])=[Forms]![InvoiceForm]![InvoiceSubform].[Form]! [txtDogNum]));

Joan

Joan can you confirm that the update was successful? In
other words is the problem only that the subform does not
show the correct/updated recordset?
If 'yes' then try me.requery instead of me.recalc

Luck
Jonathan

 

[Joan]

Jonathan,
I got the update query to update all dog records on the subform.
Thanks.

Joan

-----Original Message-----
Len,
Out of desperation, I went back to my query in design view and used the
Expression builder for the Update To and Criteria expressions. Below is
the new query and it works if there is only one dog on the subform. Notice
the WHERE statement where it put .[Form]! after [InvoiceSubform]. Also I
changed the Update To expression to equal the Store control on the main form
instead of the one on the subform. This is the only difference as far as I
can tell. It works if there is only one dog on the subform. If there is
more than one dog, it only updates the first dog record on the subform and
not the rest. What do I need to do differently, to get all of the dogs
records to update?


UPDATE Dogs SET Dogs.FinalStore = [Forms]![InvoiceForm]! [StoreCode]
WHERE (((Dogs.[Dog
Number])=[Forms]![InvoiceForm]![InvoiceSubform].[Form]! [txtDogNum]));

Joan

Joan can you confirm that the update was successful? In
other words is the problem only that the subform does not
show the correct/updated recordset?
If 'yes' then try me.requery instead of me.recalc

Luck
Jonathan