toggle checkbox in continuous form

[Raveen1609]

My form is a continuous form, data source from a union query. I have added a check box bound from a table. Now the user need to click ON the checkbox after examining the record. when one check box is clicked, all the check boxes are checked. I want only the particular record's check box should be On. Kindly assist how to solve this issue. Many thanks

[moke123]

Is the checkbox bound to a field?
A union query is not updateable.

What is your ultimate goal?
What happens after a record is selected?

[Micron]

This is a non-starter? A control has to be bound in order for all instances of it to behave independently on a continuous form. If bound to the query to solve that issue, it becomes unselectable because the recordsource is not updatable. I always suspect that there is an underlying table design issue when Union queries are involved. Not saying it's always true, just often.

You might solve this by creating an action query (preferably an append I think) based on your union query to populate a table that you use for the form recordsource. This might solve the checkbox issue, but as moke123 asks, what needs to happen then? My suggestion might mean that you now have data in multiple tables, which is not good. If this additional table will be emptied when you close this form then perhaps that is ok. Hard to say without knowing your situation.

[CJ_London]

one way assuming each record has a unique identifier, at least within the context of the records you are looking at. Since this is a union query you may need an additioanl field to identify which table the record has come from

add a textbox to the form (doesn't matter where because when you are sure everything is working as required it can be hidden). Call the textbox txtSelected

for your checkbox, set the control source to =instr("," & [ID] & ",",[txtSelected] & ",")>0

and in the click event for the checkbox

if instr("," & [ID] & ",",[txtSelected] & ",")=0 then 

     'not currently selected, so add to the list
    txtSelected="," & Me.[ID] & txtSelected

else

    'is currently selected to remove from the list
    txtSelected=replace(txtSelected  &",","," & Me.[ID] & ",","")

end if

Don't know what you want to do with this information, but if it is to update a table, you can use txtSelected in a query

dim sql as string

    sql="Update some table set somefieid=true where ID in (" & mid(txtSelected,2) & ")"
    currentdb.execute sql

union queries have their uses but are often an indication of poor design - for example having separate tables for good in and goods out - they should be in one transactions table.

[Raveen1609]

I want to send SMS, after click the send sms button, i want any indication to the user in the same record, by way of tick mark or a text box, to indicate that SMS has been sent to this client. And the same should be stored in the source table. In this form, the checkbox field is from the source table.

After CJ_London VBA code, when I click the checkbox, all the records tick boxes get ON as seen in the image.

Hope this will give some idea, how to solve this issue.

Many thanks for the support

[moke123]

What is the reason for the union query? What tables are involved?

You should address the issues with your tables before trying to find any work arounds.

Personally I would use a date/time field to indicate exactly when the sms was sent. You can still display the fact it was sent on your form in many different ways.

[Raveen1609]

Thank you all. finally I solved by using a SQL update statement in the SMS Command button, to update the Checkbox in the table and requery the form. Thanks

[CJ_London]

After CJ_London VBA code, when I click the checkbox, all the records tick boxes get ON as seen in the image.

surprised, it is a technique I have demonstrated on numerous occasions and have used myself many time. So I suspect you did not do quite what I provided or you have something in your setup causing the issue.

It doesn't matter, you have a solution