472,119 Members | 1,565 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,119 software developers and data experts.

control a primary checkbox on a continuous subform

Ok I have 2 issues.

1) I have a main candidate form with 2 subforms on a tab control:
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.


Nov 13 '05 #1
4 1761

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.

Nov 13 '05 #2
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.

Nov 13 '05 #3
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","tblAddresses","Applica ntID=" &
me!ApplicantID & " isPrimary=True")) then
' did not find any primary address, write code to make this one the
if vbYes=msgbox("This candidate has a primary address. Would you
like to change it?",,vbquestion+vbYesNo,"Change Primary Address") then
'clear the existing check box
DoCmdRunSQL"Update tblAddresses SET isPrimary=false WHERE
ApplicantID=" & me!ApplicantID & " isPrimary=True"
'write code to make this one the primary
end if

Nov 13 '05 #4
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_AfterUpdate()
On Error GoTo Err_Primary_AfterUpdate
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
Response = MsgBox(Msg, Style, Title)
DoCmd.GoToControl [Primary]
End If

Exit Sub

MsgBox Err.Description
Resume Exit_Primary_AfterUpdate

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...

Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by gavo | last post: by
2 posts views Thread by dBNovice | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.