Check Box

[lead 27]

Hi
I have a form where there is demand information with a check box for on demand this is used in a query for items on demand so I dont want people to forget to check / uncheck it. Is there any code I could write so that if the qty in the demand info is greater than 0 the check box is checked and if the qty is 0 it is unchecked

thank you

Adam

 

[WayneRyan]

Adam,

My first thought is that you don't need a checkbox at all.

If the one's to be checked ALL have DemandQty > 0 then you
can just query on that. Obviously the checkbox can't always
be relied upon.

It's easy enough to get the checkbox back in "sync":

Update YourTableName
Set    YourCheckBox = True
Where  DemandQty > 0

But, then it is only guaranteed to be valid for an instant.

If you had to maintain the checkbox, don't let your form's users
touch it (Locked = Yes) and update it using the AfterUpdate event
of the DemandQty field.

Wayne

 

[lead 27]

Thank for your help will have a look at it

 

[lead 27]

Hi
I have just tried but keep getting Compile Error, Expected Go
I am not very experienced with codes so could you tell me exactly what to do and put
The names are:
table = 11 Sqn
Check box = on demand
Demand Qty = demand qty

I hope you can help
Thanks

 

[WayneRyan]

Adam,

Open a new query.
Select your table.
Then right-click and choose SQL view.

Paste in the following:

Update [11 Sqn]
Set    [on demand] = -1
Where  [demand qty] > 0

Right-Click and go back to the QBE view.

You can run it by selecting the red exclamation mark on the menu bar.

The embedded spaces in your names makes this a bit tougher. That's
why we need all of those silly little brackets.

DemandQty, OnDemand and tbl11Sqn would be better names.

Wayne

 

[lead 27]

I have made that query but it doesnt change anything on the form do you know why, do I need to run a macro or change the after event?

 

[WayneRyan]

Adam,

Your form won't display the correct data until you requery it, or reopen
the form.

You could do the same thing with a command button on your form:

DoCmd.RunSQL "Update [11 Sqn] ..." <-- Same pasted query
Me.Requery

Then, your form will stay in sync.

But, if you go to any record with quantity > 0 and set the quantity
equal to 0, you're out of sync again.

You need a better long-term solution.

I'd still vote to get rid of the checkbox entirely. Trying to maintain an
"extra" piece of info leads to these types of errors.

Wayne

 

[lead 27]

yeah i see what you mean

 

[lead 27]

I have sorted it now
Let me know if anyone would like to know how
thank you for your help