Eliminating duplicate check box entries

[Jim Ory]

Using WinXP and Access2K2

I have a database consisting of CLIENTS table and ADDRESSES table. Some of
these clients have more than one address and I’ve placed a check box on the
address sub form and it denotes that an address is PRIMARY. This is done so
only the primary addresses will get an address mailing label. CLIENTS=1 to
many=ADDRESSES.

Data entry depends on how meticulous the data entry person is and I’ve found
that several client’s addresses have all their primary addresses' check boxes
checked.

The ADDRESSES form is a sub form of CLIENTS.

I’d like to be able to write code where only one primary address check box
can be checked per CLIENT.

So far, all I can come up with is a combination of queries to find clients
with more than one address and then find if more than one check box has been
checked. This is used to create an Error Report that appears when the main
CLIENT form is closed, if the queries find more than one check box cheked.
Closing the error report opens the main Client form for correction. Seems
clumsy to me and I believe there must be a better, or more efficient way to
have the same effect.

Any ideas are welcome. Thanks.

 

[Jeff Boyce]

Jim

I don't know if my situation matches yours, so 'grain of salt time'...

I have folks who have more than one address and need to note which one is
their "primary".

But I also have one address that is "shared" by multiple folks (think office
workers, all at the same address).

To resolve this many-to-many situation, I have a People table, an Address
table and a PeopleAtAddress table. Now, some folks can have more than one
record in that PeopleAtAddress table, but only one of them is designated
"default/primary". I added code to the form in which people are connected
to addresses that checks to make sure that no other addresses for the person
are already checked. If any are found, the code pops up a window asking if
the default address should be changed.

This way, just because a particular address is the "default/primary" for one
person, that address doesn't have to be the default for everyone using it.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Arvin Meyer [MVP]]

Use an Update Query in the AfterUpdate Event of the checkbox. Something like
this (untested):

Sub chkPrimary_AfterUpdate
If Me.chlPrimary = True Then

CurrentDb.Execute "Update MyTable Set Primary = False Where ClientID
=" & Me.txtClientID " And AddressID <>" & Me.txtAddressID

Else
MsgBox "You need to select a Primary Address
End Sub

 

[MikeJohnB]

One other solution, use the on click event of the check box to unset any of
the others that have been set so that only one check box can be ticked.

Private Sub Ckbox1_Click()
If me.ckbox1 = -1 then
me.ckbox2 = 0
me.ckbox3 = 0
End If

End Sub

Private Sub Ckbox2_Click()
If me.ckbox2 = -1 then
me.ckbox1 = 0
me.ckbox3 = 0
End If

End Sub
etc


I used this on selection check boxes to display Euro, GBP or Dollars, allow
entry in only one value and therefore report only one value.

 

[Allen Browne]

You've already got several answers, but here's another to consider.

Instead of choose one address as the primary one, it might be more
meaningful to use a Priority field (type Number.) The users can the specify
the client's number 1 address, number 2, and so on. IMHO, this gives more
useful results than just saying one of them is the preferred address.

It also passes responsibility to the user to sort out whether there is a
preferred address. If they want to enter 2 address of equal priority, I
don't stop them; it's just that the preferred one is undefined. (In
practice, I choose the lower autonumber of they have 2 of equal priority.)