SQL Commands

[Guest]

I do have the following problem:
I would like to combine several commands in SQL with one command button on
my Form.
a) Run an append query to append data to a table
b) Run another append query to append data to the SAME table as in a)
c) Print a report which is based on that table
d) Run a delete query to delete the table

Now I have the following SQL:
****************************
Private Sub Command29_Click()
Dim stDocName As String
Dim strWhere As String

DoCmd.SetWarnings False
stDocName = “yearbillappendâ€
stDocName = “halfyearbillappendâ€
DoCmd.OpenQuery stDocName, acNormal, acEdit
strWhere = “[clientID] = “ & Me.[clientID]
DoCmd.OpenReport “billingâ€, acViewNormal, , strWhere
DoCmd.SetWarnings False
stDocName = “billingtabledeleteâ€
DoCmd.OpenQuery stDocName, acNormal, acEdit

End Sub
****************************
My append queries are showing the correct data. If I run them separately
they are appending to my table correct, and of course I can print the report
and than I can delete my table – That’s if I run everything separate.
If I try SQL, or even if I try to run a Macro the program is doing what it
wants – or the wrong queries are appended, or it not printing a report.
Has anyone an idea how to solve this problem?
I have to say that I need those to append queries appended to that table
because the client has a choice to pay on a yearly basis or by half a year
(anyway I think that’s not so important because those queries are running
correct if I do everything manually).
Thanks for help
Klaus

 

[Roger Carlson]

You've got a couple of errors in your code.

Try running it like this:

DoCmd.OpenQuery "yearbillappend", acNormal, acEdit
DoCmd.OpenQuery "halfyearbillappend", acNormal, acEdit
strWhere = "[clientID] = " & Me.[clientID]
DoCmd.OpenReport "billing", acViewNormal, , strWhere
DoCmd.OpenQuery "billingtabledelete", acNormal, acEdit

Then see if any error messages pop up. The problem with the SetWarnings is
that it also suppresses legitimate error messages. You should never add
them until you're sure the code works.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Guest]

Now I have the following SQL:
****************************
Private Sub Command29_Click()
Dim stDocName As String
Dim strWhere As String

DoCmd.OpenQuery “billcemeteryyearâ€, acViewNormal, acEdit
DoCmd.OpenQuery “billcemeteryhalfyearâ€, acViewNormal, acEdit
strWhere = “[clientID] = “ & Me.[clientID]
DoCmd.OpenReport “billingâ€, acViewNormal, , strWhere
DoCmd.OpenQuery “billingtabledeleteâ€, acViewNormal, acEdit

End Sub
****************************
Result:
If I run this code once it’s working like it should do.
If I would like to run it a second time – it’s a) sometimes not doing
anything b) printing the yearly payment even if I checked the checkbox for
halfyearpayment.
I checked again my append queries they have the correct data.
Any other idea?
Thanks
Klaus
Ps. By the way - I do not receive any error message

You've got a couple of errors in your code.

Try running it like this:

DoCmd.OpenQuery "yearbillappend", acNormal, acEdit
DoCmd.OpenQuery "halfyearbillappend", acNormal, acEdit
strWhere = "[clientID] = " & Me.[clientID]
DoCmd.OpenReport "billing", acViewNormal, , strWhere
DoCmd.OpenQuery "billingtabledelete", acNormal, acEdit

Then see if any error messages pop up. The problem with the SetWarnings is
that it also suppresses legitimate error messages. You should never add
them until you're sure the code works.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

I do have the following problem:
I would like to combine several commands in SQL with one command button on
my Form.
a) Run an append query to append data to a table
b) Run another append query to append data to the SAME table as in a)
c) Print a report which is based on that table
d) Run a delete query to delete the table

Now I have the following SQL:
****************************
Private Sub Command29_Click()
Dim stDocName As String
Dim strWhere As String

DoCmd.SetWarnings False
stDocName = "yearbillappend"
stDocName = "halfyearbillappend"
DoCmd.OpenQuery stDocName, acNormal, acEdit
strWhere = "[clientID] = " & Me.[clientID]
DoCmd.OpenReport "billing", acViewNormal, , strWhere
DoCmd.SetWarnings False
stDocName = "billingtabledelete"
DoCmd.OpenQuery stDocName, acNormal, acEdit

End Sub
****************************
My append queries are showing the correct data. If I run them separately
they are appending to my table correct, and of course I can print the
report
and than I can delete my table - That's if I run everything separate.
If I try SQL, or even if I try to run a Macro the program is doing what it
wants - or the wrong queries are appended, or it not printing a report.
Has anyone an idea how to solve this problem?
I have to say that I need those to append queries appended to that table
because the client has a choice to pay on a yearly basis or by half a year
(anyway I think that's not so important because those queries are running
correct if I do everything manually).
Thanks for help
Klaus

 

[Guest]

I do believe that the problem is in the section where the append queries have
to get appended to the same table.
What I don’t understand is, that even if the correct data is shown in the
query it get’s wrong appended or in other words: If the record from the
yearlybill append query should be appended to the table, the program is
appending the halfyearbill append query with data which doesn’t exist in that
query.

You've got a couple of errors in your code.

Try running it like this:

DoCmd.OpenQuery "yearbillappend", acNormal, acEdit
DoCmd.OpenQuery "halfyearbillappend", acNormal, acEdit
strWhere = "[clientID] = " & Me.[clientID]
DoCmd.OpenReport "billing", acViewNormal, , strWhere
DoCmd.OpenQuery "billingtabledelete", acNormal, acEdit

Then see if any error messages pop up. The problem with the SetWarnings is
that it also suppresses legitimate error messages. You should never add
them until you're sure the code works.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

I do have the following problem:
I would like to combine several commands in SQL with one command button on
my Form.
a) Run an append query to append data to a table
b) Run another append query to append data to the SAME table as in a)
c) Print a report which is based on that table
d) Run a delete query to delete the table

Now I have the following SQL:
****************************
Private Sub Command29_Click()
Dim stDocName As String
Dim strWhere As String

DoCmd.SetWarnings False
stDocName = "yearbillappend"
stDocName = "halfyearbillappend"
DoCmd.OpenQuery stDocName, acNormal, acEdit
strWhere = "[clientID] = " & Me.[clientID]
DoCmd.OpenReport "billing", acViewNormal, , strWhere
DoCmd.SetWarnings False
stDocName = "billingtabledelete"
DoCmd.OpenQuery stDocName, acNormal, acEdit

End Sub
****************************
My append queries are showing the correct data. If I run them separately
they are appending to my table correct, and of course I can print the
report
and than I can delete my table - That's if I run everything separate.
If I try SQL, or even if I try to run a Macro the program is doing what it
wants - or the wrong queries are appended, or it not printing a report.
Has anyone an idea how to solve this problem?
I have to say that I need those to append queries appended to that table
because the client has a choice to pay on a yearly basis or by half a year
(anyway I think that's not so important because those queries are running
correct if I do everything manually).
Thanks for help
Klaus