Checkboxes used to show summary info

[Manning]

I have a form where I display the client's information and a series of checkboxes showing which services have been registered to them (ex. telephone, internet, cell phone (all info is held in separate tables with client id as the foreign key...).

For instance: I have a working query that pulls the client's id from the form to search the Cell Phone Table to see whether the client has a cell phone registered to them or not. How can I get that to translate into the checkbox on a form?

It seems so simple, yet the solution isn't presenting itself.

Any help is appreciated.

 

[amerfeld]

Your field in your table should be yes/no datatypes. Drag your field onto your form. When the record is pulled up, if the criteria is "yes" the checkbox will have a check. If "no", there won't be a check.

 

[Manning]

Thanks, but that's not quite it...

This checkbox isn't based on a field in the client table, it's based on whether or not the query returns a result or not on a different table (ex. Cell Phone Table).

I have been asked to provide a form that shows you the client info, followed by a series of checkboxes showing which services are held by that client.(Beneath that, I have tabular subforms with all the details of those services)

Would is just be easier to put Yes/No fields in the client table that are based on the queries? Is that possible? Then I could do what you suggested but it doesn't seem right...

 

[RuralGuy]

I haven't tried this but I believe if you LEFT JOIN the Client's table to the other tables and include a numeric field from each of the other tables then the Nz([FieldName],0) should give you a zero (False) for tables that do not have Client records and a NonZero (True) where one exists. That should populate a check box properly. At least you get the idea.

 

[amerfeld]

I think you have two options:

to have a yes/no field for each of the optional services in your main client table.

Or

you could have a record added for each client in the detail tables whether or not they have the service. You would add a field named CellPhone (or whatever), in the cellphone table, with a datatype of yes/no. So, if client #1 doesn't have a cell phone, they would have a record in the related cell phone table that says "no". All other fields would be empty.

Hope this makes sense. Either way you will have to have a yes/no field for a checkbox unless you want to do some coding.

 

[Manning]

I have the query that RuralGuy suggested in place, I have the appropriate field as the control source for the check box. But I get nothing, the check box is still grayed out, somthing is not working.

Is there something fundamental I am missing about using a query to populate a checkbox? Is there an event I need to take care of?

 

[RuralGuy]

Post the SQL so we can see what you are doing.

 

[RuralGuy]

Post the SQL so we may see what you are doing.

 

[RuralGuy]

Post the SQL so we may see what you are doing.

 

[Manning]

SELECT Nz([ctID],0) Or Not (Nz([ctID],1)) AS Expr1
FROM ClientRecords LEFT JOIN CellRecords
ON ClientRecords.ctID = CellRecords.clID
WHERE (((ClientRecords.ctID)=[Forms]![frmMasterForm]! [txtID]) AND ((CellRecords.clCellExpiryDate)>Date()));


Makes sense to me. I am joining two tables (Client and Cell Phone Tables through the Primary Key of ID)

I am pulling the ID number to look up from my Master Form and ensuring the record is current (> Date())

When I open the form and then run the query, Access shows me that the result is correct (-1). I changed the Control Source for the checkbox to the Expr1 field from the query. Nada, the box is grayed out. What am I missing?

I hope this makes sense.

 

[RuralGuy]

How about something like:

SELECT IIf(IsNull([ctID]),0,-1) AS HasCellPhone
FROM ClientRecords LEFT JOIN CellRecords 
ON ClientRecords.ctID = CellRecords.clID
WHERE (((ClientRecords.ctID)=[Forms]![frmMasterForm]! [txtID]) AND ((CellRecords.clCellExpiryDate)>Date()));

 

[Manning]

Thanks, a more elegant solution.

How do I get it to translate to the checkbox? Is it simply a matter of changing the Control Source in the checkbox properties? I tried that, it is doesn't seem to work....

The master form is bound to the Client Table. The checkboxes are currently not bound to anything (except the one we've been trying to convince to be) and the subform is bound to the Cell Records.

What am I doing wrong?

 

[RuralGuy]

Is this for display purposes only or are you going to be editing some of the data? I believe you will need to bind your form to a query that includes what we have done. That will almost certainly make a non-updateable query.

 

[RuralGuy]

Assuming the SubForm is linked by customer then you could also use the SubForm RecordCount to set the checkbox in the Current event of the form.

 

[Manning]

Thanks for your help, I have this sorted out finally. Each of the checkboxes is it's own tiny subform that is related to it's own query. Definitely not something I would have initially thought of.

It works, so I'm not complaining. Now on to the most time consuming task, making it look good.

 

[RuralGuy]

Good job! There are always several ways to skin a cat so-to-speak! You're very welcome and glad you got it working.