Prevent checking multiple checkboxes in subform (1 Viewer)

[Graham T]

We have a subform that allows us to assign multiple Playing Positions to a Player, however the Player can only have one Prefered Position (shown with a checkbox)

The composite primary key consists of the Player and the Position.

How and where is it best to code to prevent us checking the prefered position more than once?

Thanks

 

[llkhoutx]

Either have all checkboxes in an OptionGroup or on the AfterUpdate of any checkbox, set all other checkbox value to False.

I think use of an OptionGroup is your best choice.

 

[coyote]

Hi Graham T
Looking at your attachment I think you should use the Before Update Event of the PositionNo txtBox
Try the following Code
Dim P as boolean
P = DLookup("[Prefered]", "tblname", "[PositionNo]=" & Me.PositionNo)
If P.Value = True Then
MsgBox "That position has already been preffered."
Cancel = True
Me!PositionNo.Undo
End If
In this sample am assuming Prefered is the name of the checkbox in the table
in "tblname" use the table that the subform is based on.
Try it if it doesnt work post the error.
NB I have not tried it yet.

 

[Graham T]

llkhoutx and coyote

Thanks for the replies.

llkhoutx, not sure how to apply an option group in this situation. Didn't realise it would be an option?

coyote, I tried the code, but can't seem to get it working.

I have attached the database in Acc 2K if it is any help for you to visualise.

Cheers

G

 

[llkhoutx]

I can't download here at work, PCs are locked down where I am.

Add an OptionGroup to your form, the icon to the right of the Textbox icon on the Toolbox menu. A Wizard popsup. Add a name for each possible position, Striker, Left Wing, Right Wing, etc. The wizard will automatically set the value associated with each chekcbox. You can then change those vales via each checkbox properties. One checkbox selected automatically turns all others off, i.e. they are mutually exclusive.

 

[coyote]

Try This

check this some alterations and reply

 

[Graham T]

coyote

Thanks for looking at this.

I have looked over what you have done, but unfortunately this is not quite the way the options should work.

Each Player can play in any position and many players can play in the same position.

When positions are recorded in the subform against a player, they may have three or four positions that they are happy to play in, although they will only have one preferred position.

I hope that makes sense, give me a shout if not!

G

 

[coyote]

I am abit confused what you are trying to achieve because using your members form even if I removed the code behind the subform I am getting an error that the primary key cannot contain dublicates. Can you be a bit clearer what you want your form to do.
Am looking at it now

 

[Graham T]

coyote, sorry its so confusing. Guess I'm not explaining it correctly, as it really shouldn't be!

IF, from the example, we choose Position No 4 for a Player and check prefered, this means player 'X' can play in Pos No. 4 and it is his prefered position.

This same player could also play in Pos No. 7 or Pos No. 8.

Therefore a PlayerID and a PositionID have to be unique to allow storing.

What the intention is, is to prevent a single player having more than one prefered position 'if' they can play in multiple positions....

Hope that is more clear!

 

[coyote]

Use this code in the Before Update event of chkPreferred.
Delete the code I had used in the Before Update of lngPositionID.
Surely this should now work for you

If Me.chkPreferred.Value = True Then
MsgBox "You have already selected a preferred Position.You cannot have More than one preferred position", vbInformation, "Preferred Position"
Cancel = True
Me.chkPreferred.Undo
End If

You can change the message to whatever suits you
Any problems post the error.

 

[Graham T]

I think I had already tried something similar.

Appears to work first time, but then try clicking selecting further players...

 

[llkhoutx]

I've been involved in US soccer for years. Yes, players do play multiple positions, plus they are better at some that others.

Multiple (not OptionGroup) checkboxes would work fine in your case. Bind then to your table/query and keep on truckin'.