Set Check box to Yes

Experts, how do I set a YN check box to Yes?  I have a button on the form and the form is filtered for certain items and I need to set the check box to yes after clicking the button but I get an error of "cant assign a value to this object".  

Let me know what I am doing wrong.

thanks

checkboxerror
pdvsaProject financeAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

bcnagelCommented:
Hi pdvsa.

Can you set the CheckBox.Value property to True? As here:

https://msdn.microsoft.com/en-us/library/office/ff820832.aspx
0
pdvsaProject financeAuthor Commented:
that was it.  thanks but I need to ask a follow up on how to select all
0
bcnagelCommented:
No problem, pdvsa. Are you thinking of something like this loop through checkboxes:

http://bytes.com/topic/access/answers/619874-loop-through-checkboxes-using-ado-access-2003-a#post2449139
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

pdvsaProject financeAuthor Commented:
I think so but that looks pretty complicated.  I would think that if I simply wanted to check mark all the check boxes on the form then it would be much simpler.  ???  I will wait your response.
0
Rey Obrero (Capricorn1)Commented:
try

private sub Button_click()
dim ctl as control
for each ctl in me.controls
    if ctl.controltype =accheckbox then
         ctl=true
    end if
next
end sub
0
mbizupCommented:
How many check boxes are you talking about?

You can set them each explicitly like this:

Me.chkSomeCheckbox = true
Me.chkSomeOtherCheckbox = true
Me.chkYetAnotherCheckbox = true

Or more generically, like this:

Dim ctl as control
for each ctl in me.Controls
if ctl.ControlType = acCheckbox then ctl = true
Next

Open in new window


Both of these assume that you are talking about separate checkboxes in Single Form View.

If you are talking about a continuous form, however, you would have to use a recordset based method:

Dim rs as dao.recordset
set rs = me.recordsetclone
do until rs.eof
   rs("MycheckboxFieldName") = true
rs.movenext
set rs = nothing
me.requery

Open in new window

0
pdvsaProject financeAuthor Commented:
Hi Mbizup, thank you for the response.  It is only one chkbox name.  The form is in split setup.  I assume split is not seen to be some sort of continuous form.  Will test when at computer but the first.  I think the loop is what I need.
0
pdvsaProject financeAuthor Commented:
Oh hey Rey, I didn't see that you responded.  So sorry. ...
0
mbizupCommented:
-->>> The form is in split setup.

That's comparable to a continuous form.  So basically you would have to loop through the records in the form's recordset, setting the y/n field to True for all records... and then requery the form.

Using the recordsetclone versus the recordset updates the records behind the scenes, hidden to the user.  The requery then  makes what the user sees displayed on the form current with the underlying records.

You can also achieve the same thing by looping through a recordset based on the table or query behind the form, assuming the form is based on an updateable query.
0
pdvsaProject financeAuthor Commented:
Ok back at computer.


Rey:  When I run, it gives me an error of "cant assign value to control".  It is the same error I had in the initial post and I possibly I need to add .value somewhere in the code.  However, now knowing it is a split form, the code might need to revert to a record select as Miriam stated.

Miriam:  in the rs code, The MycheckboxFieldName is chkRemoveFromReport and I replace as so but when I run it says cant find field name.  The name is correct though.  I also had a "Do" without a Loop error and I added "Loop" at the end of the code and I assume that is what I needed to do to eliminate that error.

here is how I have it:

Private Sub cdmCheck_Click()
        Dim rs As dao.Recordset
        Set rs = Me.RecordsetClone
        Do Until rs.EOF
           rs("chkRemoveFromReport") = True
        rs.MoveNext
        Set rs = Nothing
        Me.Requery
     Loop  '===>I added this but not sure if placement is correct
     
End Sub

thank you
0
pdvsaProject financeAuthor Commented:
<when I run it says cant find field name.  The name is correct though.
correction:  the error says "Item not found in collection"
0
Rey Obrero (Capricorn1)Commented:
change this

rs("chkRemoveFromReport") = True

with

rs("Name of the field the checkbox is bound") = True
0
mbizupCommented:
Make sure you are using the name of the *field* as seen in the table or query behind the form... Not the name of the checkbox.
0
Rey Obrero (Capricorn1)Commented:
test this code

Private Sub cdmCheck_Click()
         Dim rs As dao.Recordset
         Set rs = Me.Recordset
         Do Until rs.EOF
            rs("Name of the field the checkbox is bound") = True
         rs.MoveNext
        Loop
        rs.close      
 End Sub
0
mbizupCommented:
Also... The Loop should go immediately after the

rs.movenext


(Your correct that it needs a loop statement, but it is not placed quite right)
0
mbizupCommented:
Looks like Rey has you covered modifying the code in my earlier post.

I'll check in tomorrow morning.
0
pdvsaProject financeAuthor Commented:
Rey, well darnit I get a runtime 3020:  "Update or Cancel Update without Addnew Or Edit"
          RemoveFromReport is the control source and name of chkRemoveFromReport

here is how I have it:
Private Sub cdmCheck_Click()

        Dim rs As dao.Recordset
        Set rs = Me.Recordset
          Do Until rs.EOF
             rs("RemoveFromReport") = True
          rs.MoveNext
         Loop
         rs.Close

End Sub
0
pdvsaProject financeAuthor Commented:
fyi:  the form is filtered.  Not sure if that changes anything.
0
Rey Obrero (Capricorn1)Commented:
Private Sub cdmCheck_Click()

         Dim rs As dao.Recordset
         Set rs = Me.Recordset
           Do Until rs.EOF
              rs.edit
             rs("RemoveFromReport") = True
              rs.update
          rs.MoveNext
          Loop
          rs.Close

 End Sub
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pdvsaProject financeAuthor Commented:
Ok that was it.  

One last question, can I keep it from refreshing?  I only want to check the check boxes and be able to see all the records it checked as True instead of them disappearing/refreshing
0
pdvsaProject financeAuthor Commented:
thank you for the help.  Really appreciate you hanging in.
0
mbizupCommented:
Tony,

>>> instead of them disappearing/refreshing

(I think this is the issue you are describing here...)


Take another look at my comment at http:#a40739317 and let me know if it makes sense..

In general you do NOT want to use the form's recordset directly for looping through records like this, because that physically changes the record the user has selected on the form.  You will visibly see the cursor moving through the records on the form, which can make for a disturbing/flashing UI if you are dealing with a lot of records.

The recordsetCLONE is an indirect way of achieving the same thing... you can update the data in the background, without affecting where the user is on the form


This is the recordsetClone code I had originally suggested, with the syntactical corrections (You'll have to modify it to use your own field names)

Try this - the cursor will remain in place with this method...
    Dim rs As DAO.Recordset
    Set rs = Me.RecordsetClone
    rs.MoveFirst
    Do Until rs.EOF
        rs.Edit
        ' Use your own field name here
        rs("YourYNField") = True
        rs.Update
        rs.MoveNext
    Loop
    set rs = nothing
    '***** You can omit this to avoid refreshing the screen
   ' Me.Requery

Open in new window



Another indirect method is to use the table or query behind the form instead of the recordsetclone.  You'll have to requery the form at the end to display the updated data.  You won't see the cursor stepping through the records with this method, but the cursor will move (Once) to the first record when you requery the form:

    Dim rs As DAO.Recordset
    ' Use the table/query that the form is based on
    Set rs = CurrentDB.Openrecordset("YourTableOrQueryName", dbOpenDynaset)
    rs.MoveFirst
    Do Until rs.EOF
        rs.Edit
        ' Use your own field name here
        rs("YourYNField") = True
        rs.Update
        rs.MoveNext
    Loop
    rs.Close
    set rs = nothing
    '  Requery the form to show the updated records
    Me.Requery

Open in new window

0
mbizupCommented:
Also... note that I added a "MoveFirst" to the code...

The recordsetclone (or recordset) code will execute from wherever the cursor happens to be.  The MoveFirst assures that ALL records are updated, not just the records that appear after the 'current record'.
0
pdvsaProject financeAuthor Commented:
Miriam, you have described precisely the issue: I do have many records updating and the flash you described is what I see after clicking the button.  

I will test this when I get back to the computer.  

Thank you for the detailed explanations.  I do appreciate it.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.