control a primary checkbox on a continuous subform

[misscrf]

Ok I have 2 issues.

1) I have a main candidate form with 2 subforms on a tab control:
http://www.geocities.com/misscrf/ima...uscheckbox.jpg
I have been encouraged to add these checkboxes, so that when there is
more than one phone or address for a candidate, I can track which is
the primary.

My issue is that I have to control that only one address, for example,
can be a primary for one candidate. Can anyone help me in how to go
about controlling that?

My thought is something along these lines, but I could use help with
the specifics, ie code to do it. I was thinking that per candidate, I
have code in the on change or click of the yes/no checkbox. It
evaluates if there is a 1 ( yes) for any address records for the
candidate. If so, then a message " This candidate has a primary
address. Would you like to change it?" Response if yes, then make all
checkmarks for addresses for that candidate null, so that they can
make one checked. If no, then leave as is. If there is no address
with a checkmark, then the intial check can either just check, or pop a
message (ok) that says, " This address will be made the primary address
for the candidate"

Can anyone help with that?

I think I want to post my other question, but maybe I will wait and do
one at a time.

Thanks!

[ABG]

to change it
on the before update event for the checkbox:
where persID is the PK

docmd.runsql " update contactInfo set PrimaryContact = 0 where persID =
' " & me.persid.value & " ' "

it should run this to clear any PrimaryContacts for that person to 0
before your record is saved.

[misscrf]

Thanks. I will remember that. I jotted it down. Now I need to work
out the intial check of one record, and the evaluation of the check
instance if it is a 2nd check etc.

[jv]

I have a custom lookup function that I always use, but you can use the
builtin DLookup function to do the checking:

if isnull(DLookup( "isPrimary","tb lAddresses","Ap plicantID=" &
me!ApplicantID & " isPrimary=True" )) then
' did not find any primary address, write code to make this one the
primary
else
if vbYes=msgbox("T his candidate has a primary address. Would you
like to change it?",,vbquestio n+vbYesNo,"Chan ge Primary Address") then
'clear the existing check box
DoCmdRunSQL"Upd ate tblAddresses SET isPrimary=false WHERE
ApplicantID=" & me!ApplicantID & " isPrimary=True"
'write code to make this one the primary
end if

[misscrf]

I will look at your code to see if it will work better than what I have
started. This is what I have now:

I put this code in the afterupdate of the primary checkbox:

Private Sub Primary_AfterUp date()
On Error GoTo Err_Primary_Aft erUpdate
If Not DCount("[Primary]", "tblAddress ", "[CandID] = " & Me.anCandID &
" AND Primary = True") Then
Me.Primary = True
ElseIf Me.Primary = True Then
DoCmd.RunSQL "Update tblAddress Set Primary = False Where CandID = "
& Me.anCandID
Dim Msg, Style, Title
Msg = "You must specify which address is the primary address.
Please do that now. Thank you."
Style = vbOK
Title = "Candidates with multiple addresses must have a primary
address."
Response = MsgBox(Msg, Style, Title)
DoCmd.GoToContr ol [Primary]
End If

Exit_Primary_Af terUpdate:
Exit Sub

Err_Primary_Aft erUpdate:
MsgBox Err.Description
Resume Exit_Primary_Af terUpdate

End Sub

----------------------

This is the error I get when I click on the checkbox:

Syntax error (missing operator) in query expression 'CandID = AND
Primary = True'.

I think they truncated the query criteria of the code there.

Anyway, after you click ok to the error, it checks the box anyway and
lets you move on.
I am not sure where to go from here...