Append Queries, table, sql command

[Amateur]

I have a customer table where I can choose, with checkboxes, the payment
method for the clients (yearly- or halfyearpayment)
I have two append queries (1) billcemeteryyear 2) billcemeteryhalfyear)
which should send the data from the choosed paymentmethod to a table called
billing, where the bill gets created.
That means if I choose the payment method “halfyear†the data from the
billcemeteryhalfyear append query should be transferred to the table
“billingâ€. If I choose the payment method “yearly†the data from the
billcemeteryyear append query should be transferred to the table “billingâ€.
So, only one of those two queries should show data which has to be
transferred to create the bill.
The queries are working correct if I run them manually one-by-one.
This process I try to run with a command button on a form using my SQL
command.
If I open the form once and choose the checkbox i.e. Yearly payment, than
run the sql command – everything works fine.
If I realized that I checked the checkbox “yearlypayment†by mistake, and it
should be “Halfyearpayment†I uncheck “yearlypayment†and check
“Halfyearpayment†– and – run the command again.
The following happens :
Either – It is transferring again the data from billcemeteryyear append
query (but the halfyearpayment checkbox is checked and the
billcemeteryhalfyear append query is showing the data from halfyearpayment,
and the billcemeteryyear append query doesn’t show any data - how can the
program append data which is not in the query)
Or – It is not transferring any rows from any query even if the
billcemeteryhalfyear append query is showing one row of data
Or – Nothing happens if I push the command button (not even error messages
or warnings)
This is my 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
****************************
Can someone help me?
Thanks
Klaus

 

[Andy Hull]

Hi

As far as I can tell, this is what is happening...

Step 1: Check Yearly & press button = Yearly info appended
Step 2: Change mind, check Half Yearly & press button = Half Yearly info
appended

So now, we have Yearly and Half Yearly Info appended which we don't want.

This is what I would do...
When the button is pressed run a query to delete any billing info for the
client that is already there then run the queries as you have them.

Note: I don't know your design so be careful not to delete any past billing
info that you require - BACKUP before trying this out.

Regards

Andy Hull

I have a customer table where I can choose, with checkboxes, the payment
method for the clients (yearly- or halfyearpayment)
I have two append queries (1) billcemeteryyear 2) billcemeteryhalfyear)
which should send the data from the choosed paymentmethod to a table called
billing, where the bill gets created.
That means if I choose the payment method “halfyear†the data from the
billcemeteryhalfyear append query should be transferred to the table
“billingâ€. If I choose the payment method “yearly†the data from the
billcemeteryyear append query should be transferred to the table “billingâ€.
So, only one of those two queries should show data which has to be
transferred to create the bill.
The queries are working correct if I run them manually one-by-one.
This process I try to run with a command button on a form using my SQL
command.
If I open the form once and choose the checkbox i.e. Yearly payment, than
run the sql command – everything works fine.
If I realized that I checked the checkbox “yearlypayment†by mistake, and it
should be “Halfyearpayment†I uncheck “yearlypayment†and check
“Halfyearpayment†– and – run the command again.
The following happens :
Either – It is transferring again the data from billcemeteryyear append
query (but the halfyearpayment checkbox is checked and the
billcemeteryhalfyear append query is showing the data from halfyearpayment,
and the billcemeteryyear append query doesn’t show any data - how can the
program append data which is not in the query)
Or – It is not transferring any rows from any query even if the
billcemeteryhalfyear append query is showing one row of data
Or – Nothing happens if I push the command button (not even error messages
or warnings)
This is my 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
****************************
Can someone help me?
Thanks
Klaus

 

[Amateur]

Hello Andy
I believe you misunderstood.
Here is what happening in short:
****************************

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
****************************

Now to your example:
Step 1: Check Yearly & press button = Yearly info appended
Step 2: Change mind, check Half Yearly & press button = Half Yearly info
appended
So now, we have Yearly and Half Yearly Info appended which we don't want.

To step one: correct, yearly checked, yearly appended, halfyear no row to
append, report printed, table deleted
To step 2: Wrong, yearly unchecked, half year checked, data yearly appended
(even if there is no data in the query), no report printed (or sometimes
yearly report printed ), table deleted.

So, the bill table is always empty before I run a new billing procedure. The
problem is that as soon as I run the command a second time it's either doing
nothing or showing data which doesn't exist in the query.
I checked the append queries - everything is correct.
Any other idea?
Thanks
Klaus

Hi

As far as I can tell, this is what is happening...

Step 1: Check Yearly & press button = Yearly info appended
Step 2: Change mind, check Half Yearly & press button = Half Yearly info
appended

So now, we have Yearly and Half Yearly Info appended which we don't want.

This is what I would do...
When the button is pressed run a query to delete any billing info for the
client that is already there then run the queries as you have them.

Note: I don't know your design so be careful not to delete any past billing
info that you require - BACKUP before trying this out.

Regards

Andy Hull

I have a customer table where I can choose, with checkboxes, the payment
method for the clients (yearly- or halfyearpayment)
I have two append queries (1) billcemeteryyear 2) billcemeteryhalfyear)
which should send the data from the choosed paymentmethod to a table called
billing, where the bill gets created.
That means if I choose the payment method “halfyear†the data from the
billcemeteryhalfyear append query should be transferred to the table
“billingâ€. If I choose the payment method “yearly†the data from the
billcemeteryyear append query should be transferred to the table “billingâ€.
So, only one of those two queries should show data which has to be
transferred to create the bill.
The queries are working correct if I run them manually one-by-one.
This process I try to run with a command button on a form using my SQL
command.
If I open the form once and choose the checkbox i.e. Yearly payment, than
run the sql command – everything works fine.
If I realized that I checked the checkbox “yearlypayment†by mistake, and it
should be “Halfyearpayment†I uncheck “yearlypayment†and check
“Halfyearpayment†– and – run the command again.
The following happens :
Either – It is transferring again the data from billcemeteryyear append
query (but the halfyearpayment checkbox is checked and the
billcemeteryhalfyear append query is showing the data from halfyearpayment,
and the billcemeteryyear append query doesn’t show any data - how can the
program append data which is not in the query)
Or – It is not transferring any rows from any query even if the
billcemeteryhalfyear append query is showing one row of data
Or – Nothing happens if I push the command button (not even error messages
or warnings)
This is my 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
****************************
Can someone help me?
Thanks
Klaus

 

[Amateur]

I just realized that, if I run the command for the first time - it's workng.
I tried to run the command the second time and checked the different payment
method - than I went to see if the data is changed in the append queries
(everything OK) than I run the command the second time and it's working.

That means, and now my question:
- If I run the command the first time - it works
- if I run the command the second time and open before running, the append
queries in design view and datasheet view it works as well.
But it's silly, I cannot always open the append queries before running the
command.
Any idea what went wrong?

Hi

As far as I can tell, this is what is happening...

Step 1: Check Yearly & press button = Yearly info appended
Step 2: Change mind, check Half Yearly & press button = Half Yearly info
appended

So now, we have Yearly and Half Yearly Info appended which we don't want.

This is what I would do...
When the button is pressed run a query to delete any billing info for the
client that is already there then run the queries as you have them.

Note: I don't know your design so be careful not to delete any past billing
info that you require - BACKUP before trying this out.

Regards

Andy Hull

I have a customer table where I can choose, with checkboxes, the payment
method for the clients (yearly- or halfyearpayment)
I have two append queries (1) billcemeteryyear 2) billcemeteryhalfyear)
which should send the data from the choosed paymentmethod to a table called
billing, where the bill gets created.
That means if I choose the payment method “halfyear†the data from the
billcemeteryhalfyear append query should be transferred to the table
“billingâ€. If I choose the payment method “yearly†the data from the
billcemeteryyear append query should be transferred to the table “billingâ€.
So, only one of those two queries should show data which has to be
transferred to create the bill.
The queries are working correct if I run them manually one-by-one.
This process I try to run with a command button on a form using my SQL
command.
If I open the form once and choose the checkbox i.e. Yearly payment, than
run the sql command – everything works fine.
If I realized that I checked the checkbox “yearlypayment†by mistake, and it
should be “Halfyearpayment†I uncheck “yearlypayment†and check
“Halfyearpayment†– and – run the command again.
The following happens :
Either – It is transferring again the data from billcemeteryyear append
query (but the halfyearpayment checkbox is checked and the
billcemeteryhalfyear append query is showing the data from halfyearpayment,
and the billcemeteryyear append query doesn’t show any data - how can the
program append data which is not in the query)
Or – It is not transferring any rows from any query even if the
billcemeteryhalfyear append query is showing one row of data
Or – Nothing happens if I push the command button (not even error messages
or warnings)
This is my 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
****************************
Can someone help me?
Thanks
Klaus

 

[Andy Hull]

I presume the queries use the form's check box somehow. Could you post the 2
queries?

As a different approach, would the following work...

Private Sub Command29_Click()
Dim stDocName As String
Dim strWhere As String

DoCmd.OpenQuery “billingtabledeleteâ€, acViewNormal, acEdit

If {yearly checkbox checked} then
DoCmd.OpenQuery “billcemeteryyearâ€, acViewNormal, acEdit
Else
DoCmd.OpenQuery “billcemeteryhalfyearâ€, acViewNormal, acEdit
End If

DoCmd.OpenReport “billingâ€, acViewNormal

End Sub

Note: Obviously I haven't put the right syntax for checking the check box.
Also, I would have the queries append only the relevant client's info by
having something like the following in the where clause...

WHERE billing.ClientID = [Forms]![FormName].[ClientID]

Then there's no need to pass a filter to the report.

Regards

Andy Hull

I just realized that, if I run the command for the first time - it's workng.
I tried to run the command the second time and checked the different payment
method - than I went to see if the data is changed in the append queries
(everything OK) than I run the command the second time and it's working.

That means, and now my question:
- If I run the command the first time - it works
- if I run the command the second time and open before running, the append
queries in design view and datasheet view it works as well.
But it's silly, I cannot always open the append queries before running the
command.
Any idea what went wrong?

Hi

As far as I can tell, this is what is happening...

Step 1: Check Yearly & press button = Yearly info appended
Step 2: Change mind, check Half Yearly & press button = Half Yearly info
appended

So now, we have Yearly and Half Yearly Info appended which we don't want.

This is what I would do...
When the button is pressed run a query to delete any billing info for the
client that is already there then run the queries as you have them.

Note: I don't know your design so be careful not to delete any past billing
info that you require - BACKUP before trying this out.

Regards

Andy Hull

I have a customer table where I can choose, with checkboxes, the payment
method for the clients (yearly- or halfyearpayment)
I have two append queries (1) billcemeteryyear 2) billcemeteryhalfyear)
which should send the data from the choosed paymentmethod to a table called
billing, where the bill gets created.
That means if I choose the payment method “halfyear†the data from the
billcemeteryhalfyear append query should be transferred to the table
“billingâ€. If I choose the payment method “yearly†the data from the
billcemeteryyear append query should be transferred to the table “billingâ€.
So, only one of those two queries should show data which has to be
transferred to create the bill.
The queries are working correct if I run them manually one-by-one.
This process I try to run with a command button on a form using my SQL
command.
If I open the form once and choose the checkbox i.e. Yearly payment, than
run the sql command – everything works fine.
If I realized that I checked the checkbox “yearlypayment†by mistake, and it
should be “Halfyearpayment†I uncheck “yearlypayment†and check
“Halfyearpayment†– and – run the command again.
The following happens :
Either – It is transferring again the data from billcemeteryyear append
query (but the halfyearpayment checkbox is checked and the
billcemeteryhalfyear append query is showing the data from halfyearpayment,
and the billcemeteryyear append query doesn’t show any data - how can the
program append data which is not in the query)
Or – It is not transferring any rows from any query even if the
billcemeteryhalfyear append query is showing one row of data
Or – Nothing happens if I push the command button (not even error messages
or warnings)
This is my 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
****************************
Can someone help me?
Thanks
Klaus