Run update query if a check box is ticked

[MikeSA]

Hi
Probably an old question....
I'm trying to run an update query which needs to update files in a table if a check box is ticked.
Please advise.
Thanks!
MikeSA

[Luke Bailey]

Please provide more information about what you are trying to do. Are you trying to update data on a form? Update fields in records in a table? What are you updating and what do you want the end result to be?

[MikeSA]

Please provide more information about what you are trying to do. Are you trying to update data on a form? Update fields in records in a table? What are you updating and what do you want the end result to be?

Hi Luke
I have an Orders Table with a field called VAT% that must be updated to a value held in a table called Constants, with one record only, containing a field called VATRate. In the Orders table I have a check box field (Yes/No) called VATable that is used as the criteria for the query to decide which records in Orders Table needs to be updated. This works successfully, update query works fine.

But on the Orders Form derived from the Orders Query (select query), I need the query to execute automatically after the Check Box changes state, automatically updating the Orders Table.

Idea is to be able to change the VATRate in one place selectively according to status of Check Box, updating all the relevant records in the Orders Table.

Hope I am making myself understood. Perhaps their is a better way of doing this that I am missing. (So far I have not needed to go the VBA route).

Please advise
Thanks!
MikeSA

[Luke Bailey]

Hi Luke
I have an Orders Table with a field called VAT% that must be updated to a value held in a table called Constants, with one record only, containing a field called VATRate. In the Orders table I have a check box field (Yes/No) called VATable that is used as the criteria for the query to decide which records in Orders Table needs to be updated. This works successfully, update query works fine.

But on the Orders Form derived from the Orders Query (select query), I need the query to execute automatically after the Check Box changes state, automatically updating the Orders Table.

Idea is to be able to change the VATRate in one place selectively according to status of Check Box, updating all the relevant records in the Orders Table.

Hope I am making myself understood. Perhaps their is a better way of doing this that I am missing. (So far I have not needed to go the VBA route).

Please advise
Thanks!
MikeSA

Here is my understand:

You have two tables: Orders, Constants
Order Table has fields: VAT%, VATable (y/n), etc.
Constants has a field: VATRate

You have a Form: frmOrders
Forms is based off of the Orders Query

I don't understand:
What the Orders Query is based off. Is it a combination of the Orders and Constants Tables?

What does your update query do? Does it change the VATable to yes or no?

You want to change the VATRate in the Constants table to a different value based on the checkbox in your form? After which you your update query to do whatever it does?

I believe that I can instruct you on how to accomplish these tasks, but I still don't quite understand exactly what you are trying to accomplish.

if you want, feel free to send me a test file of your database at [e-mail] (please don't distribute)

[MikeSA]

Here is my understand:

You have two tables: Orders, Constants
Order Table has fields: VAT%, VATable (y/n), etc.
Constants has a field: VATRate

You have a Form: frmOrders
Forms is based off of the Orders Query

I don't understand:
What the Orders Query is based off. Is it a combination of the Orders and Constants Tables?

What does your update query do? Does it change the VATable to yes or no?

You want to change the VATRate in the Constants table to a different value based on the checkbox in your form? After which you your update query to do whatever it does?

I believe that I can instruct you on how to accomplish these tasks, but I still don't quite understand exactly what you are trying to accomplish.

if you want, feel free to send me a test file of your database at [e-mail] (please don't distribute)

Hi Luke
Thanks for the help, I will try to explain:

The Update Query updates the Orders Table VAT% Field from the Constants Table VATRate field if the check box VATable is ticked by the user.

The Constants Table contains the field VATRate, so that the VAT Rate can be changed in the table by the user, only having to change it in one place, using the update query to populate the changes throughout the rest of the Orders Table, those records where the the check box has been ticked by the user.

The Orders Query (select query) at this time is based off the Orders Table only, and is used to derive the Orders Form.

Main goal: If the user ticks the VATable checkbox on the orders form, then that record's VAT% field must automatically be updated with the value in the field VATRate from the Constants Table.

Make sense?

[Luke Bailey]

Hi Luke
Thanks for the help, I will try to explain:

The Update Query updates the Orders Table VAT% Field from the Constants Table VATRate field if the check box VATable is ticked by the user.

The Constants Table contains the field VATRate, so that the VAT Rate can be changed in the table by the user, only having to change it in one place, using the update query to populate the changes throughout the rest of the Orders Table, those records where the the check box has been ticked by the user.

The Orders Query (select query) at this time is based off the Orders Table only, and is used to derive the Orders Form.

Main goal: If the user ticks the VATable checkbox on the orders form, then that record's VAT% field must automatically be updated with the value in the field VATRate from the Constants Table.

Make sense?

I believe I understand what you want to do. If you are ok with using VBA I can tell you really quickly how to do it. Otherwise, I'm working on a non-VBA solution atm.

[Luke Bailey]

MikeSA,

I've got to get going today and I wont be checking this till next week so I wanted to at least post to you what I've got in the hopes that it will help get you over the ledge. I have duplicated your setup and created a form that, when the checkbox is clicked, updates the VAT% with the VATRate from the constants table. The only problem with my solution so far is that since you have the form open, when you click the checkbox, it updates the Orders table, but then there is a write conflict with the form and the table (since the form isn't updated). You can just click "Drop Changes" to drop the forms changes and accept the update queries changes, but it's annoying nonetheless. I normally do all this stuff in VBA so I'm not familiar yet with how to get around that. Anyway, here is my solution so far:

Tables:

Orders
Fields: VATID, VAT%, VATable
Constants
Fields: ConstantID, VATRate

Queries:
OrdersQuery: a Select query on the Orders table

UpdateQuery:
--------------
Field: VAT%
Table: Orders
Update To: forms!frmorders.vatrate

Field: VATable
Table: Orders
Update To: Yes

Field: VATID
Table: Orders
Criteria: [forms]![frmOrders].[VatID]

Field: [forms]![frmOrders].[VATable]
Criteria: True
--------------

Forms:
frmOrders

Controls in Form:

VATID, VAT%, VATable (all from the OrdersQuery)
CurrentVATRate: unbound control: =DLookup("VATRate","Constants","ConstantID=1")

Macros:

UpdateRecord:
------------
SetWarnings: Warnings On=No
OpenQuery: Query Name=UpdateQuery, View=Datasheet, DataMode = Add
SetWarnings: Warnings On = Yes
------------


SUMMARY: The form has the Current VATRate listed on each record so that you can see what it is currently set to. The UpdateQuery uses that field to enter into the VAT% if the box is checked. In properties set the OnClick or AfterUpdate events for the checkbox to the UpdateRecord macro. The macro will run the UpdateQuery which checks for the checkbox in the true position and assigns the value based on the VATID that the form is currently on.

I hope this helps you get past your block!

~Luke