Check Checkboxes Based on Status

[CeVaEs_64]

Hi, I have SubForm inside a Form... I have the Status Field that contains Delivered, Later and Pending... If we chose to filter to only pending ones, then we have to go and check all the checkboxes for every pending one by one on the red arrows column... sometimes up to 100 times and we do that about every 10 mins, its gettin Old fast

Is there any way where if the Status Field gets filtered to pending it will automatically check the box on the pending fields ONLY for the red arrows column??

Many Thanks

Sorry about me blocking everything, this is sentitive Data with sensitive information.

[amrut]

You have to use VBA or Macros.
You have to create an Update query to UPDATE the required number of records to "Checked" WHERE Status is "Pending".

[CeVaEs_64]

You have to use VBA or Macros.
You have to create an Update query to UPDATE the required number of records to "Checked" WHERE Status is "Pending".

Got it.. That sounds doable, LOL let me give it a try and see if i can do it, Thanks.

[John_G]

I don't see why you would need to do that - you could just as easily use the text "Pending" for any filtering or selection. Besides, once the boxes are checked, how would you uncheck them (e.g. if you filtered on another status)?

In addition to that, if the checkboxes are not part of the record source, i.e. they are not bound to a table field, what you want to do won't work anyway.

Can you clarify, please?

[CeVaEs_64]

Can i just creat a button that after i set my filter i can just click on it, with something like:

If Cell whatever = pending then Check i just have to cfind a code that would work

[CeVaEs_64]

I don't see why you would need to do that - you could just as easily use the text "Pending" for any filtering or selection. Besides, once the boxes are checked, how would you uncheck them (e.g. if you filtered on another status)?

In addition to that, if the checkboxes are not part of the record source, i.e. they are not bound to a table field, what you want to do won't work anyway.

Can you clarify, please?

Yes we have them in the subform all together, w/o filtering, but the completed or Later ones we dont need, so we go to status field, filter only the ones we want (Pending) then we have to go and check the boxes one by one, thats why i want to create somehting that would check them all for me, like another button that after i filter i can go and click and do something like an IF condition, to get the boxes checked.

All the data is imported from a webpage, i have a Macro that does that, it imports from there to a table. thats where de date comes from

[CeVaEs_64]

You have to use VBA or Macros.
You have to create an Update query to UPDATE the required number of records to "Checked" WHERE Status is "Pending".

Hi Amrut, Today i sat with the person that requested this change and she gave me a bit more explanation of how this worked..

I have a Form that inside has a Subform based off a table, While in the form she applies the filter to the "status" field to show only the Transactions that are "Pending", after that she has to go and check a checkbox to everysingle line that has the pending value.. this case was 376 lines that she had to manually check.

Now after all checkboxes have been checked she clicks on a button that like you said will activate a macro to run a update query to update the values of the table where all this data came from originally, it will update 2 values of that table.

That being explained a bit better is there any way where she could avoid to click 376 times?

Thanks

[John_G]

Why do you keep wanting to do this checkbox thing? You don't have to - since you are filtering on "Pending", that is all you need. Checking all all the checkboxes in the records you see is completely redundant, is it not? A where clause like "Status = 'Pending'" will give you exactly the same record set as setting the checkboxes (by hand or automatically) and then using the checkbox field will.

You have not said whether or not the checkboxes are part of the form's record source, if they are NOT, i.e. if the checkboxes are not a Yes/No field in a table, then what you want to do will not work.

Am I missing something here? What is the purpose of the checkboxes?

[CeVaEs_64]

Why do you keep wanting to do this checkbox thing? You don't have to - since you are filtering on "Pending", that is all you need. Checking all all the checkboxes in the records you see is completely redundant, is it not? A where clause like "Status = 'Pending'" will give you exactly the same record set as setting the checkboxes (by hand or automatically) and then using the checkbox field will.

You have not said whether or not the checkboxes are part of the form's record source, if they are NOT, i.e. if the checkboxes are not a Yes/No field in a table, then what you want to do will not work.

Am I missing something here? What is the purpose of the checkboxes?

Aint the checkboxes how the query knows what fields to update in the table? or will it know when i filter it to only the pending ones?
Edit: And Yes the checkbox field is part of the record source with a yes/no Data type

[John_G]

Hi -

No, a query can use any fields (as many as necessary) to determine which records are to be updated (or selected).

I don't use macros, but in VBA and using SQL it's easy.

Assuming the "Status" field is in the table you want to update, and that the field is called "Status", then you would use something like this:

currentdb.execute "Update yourtable set field1 = expression1, field2 = expression2 where status = 'Pending'"

replacing the italics with the actual names of your table, fields and expressions.

I think that could be converted into a macro without much difficulty.

[CeVaEs_64]

Hi -

No, a query can use any fields (as many as necessary) to determine which records are to be updated (or selected).

I don't use macros, but in VBA and using SQL it's easy.

Assuming the "Status" field is in the table you want to update, and that the field is called "Status", then you would use something like this:

currentdb.execute "Update yourtable set field1 = expression1, field2 = expression2 where status = 'Pending'"

replacing the italics with the actual names of your table, fields and expressions.

I think that could be converted into a macro without much difficulty.

Many Thanks, im new to all this, and im having to work on stuff that i didnt create nor understand the logic of what is suposed to do, they used macros for everything, Im converting everyting to VBA, at my own pace, im reading a lot and lurking here, Sorry for the noobies questions but thats how I learn, I will give that a try tomorrow morning and Many Many Thanks!

[CeVaEs_64]

Soo i feel so stupid now and i want to say sorry, its just been too much in little time, All i had to do was to change the update query values, instead of going from the checkbox field i changed it to the status field where Pending, if pending, then update to: and that was it =~(

Sorry And Thansk guys, is a slow process but i will get there soon

[John_G]

Glad we helped you find a solution. Hang in there - Access does has a steep learning curve at times.