[X]My Assistant

[Alexander]

Hoping for some help understanding how to control a Tick Box (Yes/No field type) on a Membership Form.

I only have two options ... I show 2 tick boxes ...one for Division 1 and one for Division 2.

My question ... how to ensure at least one is ticked (to indicate which Division they are in) and how to ensure both boxes don't get ticked as a member can only be in one Division.

Thanks

[RJD]

Hi Alexander: Have you considered using a single field in the table indicating the Division (1 or 2) rather than 2 yes/no fields? That way you could default the field to 1 and use a combobox on the membership form to select 2 if that is the correct Division.

Or you could eliminate the default for the combobox and make the new Division field required.

This also gives you flexibility in case there are ever more divisions.

Yes, there are ways to ensure that at least one yes/no field is filled, but that just seems to complicates matters.

HTH
Joe

[cheekybuddha]

You have a couple of choices:

1. Use code in the AfterUpdate event of checkbox to set the other checkbox dependent on how the current checkbox is checked:

CODE

Private Sub chk1_AfterUpdate()

  Me.chk2 = Not me.chk1

End Sub

Private Sub chk2_AfterUpdate()

  Me.chk1 = Not Me.chk2

End Sub

2. Use an Option Group with checkboxes instead of radio buttons (or even just use radio buttons. There is a wizard to help you set it up. This is probably a better solution for what you describe.

3. Just have one checkbox - Checked means one option, unchecked means the other.

hth,

d

[cheekybuddha]

I didn't see Joe's response before I posted - but I definitely agree.

In terms of storage, your table should just have a single division field, preferably linked to a table listing the divisions.

You have 2 divisions now, but if you don't plan for it now, you can guarantee that you will have more in the future.

Display-wise to the user, you can still go for an option group or checkboxes if that makes more visual sense, or comboboxes, or listboxes etc.

hth,

d

[Alexander]

Good to hear from you Joe

As so often is the case I am working backwards and already have these two fields and included in various queries reports etc.

I can see from David's reply that the After Update event would work for me ensuring that any one time only one of the two boxes can be ticked.

If no box ticked I can flag up a message saying "No Division selected fro this member" when entering the members form.

Thanks for the comments.

[Alexander]

Thank you David ...

I am going to go with your After Update Event as that would work for me and I can understand it.

I have planned for the future as I have 4 fields for Div 1, 2 3 and 4 but only 2 at present.

I will work on a message if no division ticked for a member to complete the form and make it bulletproof !!!!!

Thanks

[Alexander]

Sorry David and Joe

I will also default Div 1 to YES and Div 2 to NO so the client needs to change but it means one or other must always be ticked ensuring they are correct (if input of course s correct).

[cheekybuddha]

>> I have planned for the future as I have 4 fields for Div 1, 2 3 and 4 but only 2 at present. <<

Aaaargh!

On your own head be it! You have been warned!!



d

[Alexander]

Yes David but like lots of things in Access there are so many ways to do each job.

I think I have it and thanks for the after update code which will work just fine.

Thanks again.

[RJD]

Hi Alexander: Actually, it is possible for you to have this both ways. You can leave the yes/no fields in the table (as we said, a bad idea in our opinions) and default the first yes/no field to yes in the table, but then use a combobox to enter the Division on the form. The code behind the combobox (after update) would look at the selection and set the Division yes/no fields in the record accordingly.

You might have to display the current selection in the form using some logic, but this would preclude the multiple checkbox situation and the associated code.

But, it might be better to "bite-the-bullet" now and revise the table (to a single Division field) and associated objects at this stage rather than face more complicated revisions of the table and associated objects later.

Up to you, of course, as always. These are just some thoughts ...

Good to hear from you again, too, Alexander. Good luck with your project.

Regards,
Joe

[Alexander]

Many thanks Joe ..

I do take the advice on UA and understand what you are saying.

The Combo Box idea will work and worth having a go with that option.

As always I am a little too far into queries and reports to change but filed in my "Access Snipetts" folder a little trick I learned from a good friend Mike60Smart.

[RJD]

I understand, Alexander. These were just some thoughts to store away for future consideration as you progress with your db.

I recently overhauled one of my client databases (that I had built years ago, and modified a couple times along the way) to clean up approaches and naming conventions. It ended up taking only a couple or three hours to do, with impacts rippling through tables, queries, forms, reports and procedures. But the db is easier to follow now, and much easier to support as we change it in the future. So the effort was worthwhile, and did not take as long as I had first estimated.

Again, just some thoughts.

Good luck and best wishes.

Joe

[Alexander]

Thanks Joe and yes .. it is worth the effort.

I tend to do individual reports and queries, so have lots of them. Don't know if I do this because I like to do so or if I just like working in Access but it works for me and that's the key. I try to reduce my clients questions to a one button click in my Reports Menu and find this works well.

As I get stuck (which is most days) I search around and then fire a question or two to UA and always get great help.