Hello

I have 3 check boxes to tell the program which address the form is to be mailed to...the Client...the Primary Owner or to the Accountant.

Is there a way that I let only 1 of the 3 check boxes be checked for that person or business?

The primary is ClientID and the check box names are...MailToClient...MailToOwner...MailToAccountant

Thanks
Larry

Hi Larry,

Consider using an Option Group.

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)

<picky>
What you're trying to do contradicts standard Windows usage. In Windows, check boxes mean one or more of the options are available. You use radio buttons when only one option is allowed.
</picky>

If you add an Option Group, you can use check boxes in the manner in which you describe. Another option would be to associate code with the AfterUpdate event of each of the check boxes:

Hopefully, you only have 1 field in your table (maybe called "MailToWhom")
You can use an option group control on your form which will automatically handle the "only check one box" part.
Then in the after-update event of the option group control, you can stuff the correct value into the ONE field "MailToWhom".

THis could be a 1,2, or 3 (which you could decode via a lookup table)
or you could stuff the string "Client", "Owner", "Accountant" into the field.

Rob

Thanks for all the replies

Below is the SQL for the report that is mailed.

SELECT qryClients.ClientID, qryClients.SSANEN, qryClients.IsActive, qryClients.MailToClient, qryClients.MailToOwner, qryClients.MailToAccountant, qryClients.AccountName, [ClientMailingAddress] & (", "+[ClientStreetAddress]) AS AccountAddress, [ClientCity] & (", "+[ClientState]) & (" "+[ClientPostalCode]) AS AccountCity, qryClients.PrimaryOwner, [OwnerMailingAddress] & (", "+[OwnerStreetAddress]) AS PrimaryOwnerAddress, [OwnerCity] & (", "+[OwnerState]) & (" "+[OwnerPostalCode]) AS PrimaryOwnerCity, qryClients.AccountantFirm, [MailingAddressCPA] & (", "+[StreetAddressCPA]) AS AccountantAddress, [CityCPA] & (", "+[StateCPA]) & (" "+[PostalCodeCPA]) AS AccountantCity, IIf([MailToClient]=True,[AccountName],IIf([MailToOwner]=True,[PrimaryOwner],IIf(
[MailToAccountant]=True,[AccountantFirm],""))) AS MailToName, IIf([MailToClient]=True,[AccountAddress],IIf([MailToOwner]=True,[PrimaryOwnerAdd
ress],IIf([MailToAccountant]=True,[AccountantAddress],""))) AS MailToAddress, IIf([MailToClient]=True,[AccountCity],IIf([MailToOwner]=True,[PrimaryOwnerCity],
IIf([MailToAccountant]=True,[AccountantCity],""))) AS MailToPostal
FROM qryClients
WHERE (((qryClients.IsActive)=True));

Maybe it will give you a better idea as to what I am trying to do.

Thanks
Larry

All that code shows me is that you are using 3 fields to store "who" it is to go to.
Structurally, I'd consider changing to 1 field, (as long as it can only go to 1 recipient). If it can (someday) go to multiple
recipients, then that's another story.

However, as is, imagine the difficulty of adding a 4th option if some day you could also send it to someone else.

I'd go with an option group or possibly use code similar to what Doug supplied to turn off the other boxes each time
you check a box.

If you stored 1, 2, 3 for MailToAccountant, MailToClient or MailToOwner respectively (as Rob suggested), that could be simplified to

SELECT qryClients.ClientID, qryClients.SSANEN, qryClients.IsActive, qryClients.MailToClient, qryClients.MailToOwner, qryClients.MailToAccountant, qryClients.AccountName, [ClientMailingAddress] & (", "+[ClientStreetAddress]) AS AccountAddress, [ClientCity] & (", "+[ClientState]) & (" "+[ClientPostalCode]) AS AccountCity, qryClients.PrimaryOwner, [OwnerMailingAddress] & (", "+[OwnerStreetAddress]) AS PrimaryOwnerAddress, [OwnerCity] & (", "+[OwnerState]) & (" "+[OwnerPostalCode]) AS PrimaryOwnerCity, qryClients.AccountantFirm, [MailingAddressCPA] & (", "+[StreetAddressCPA]) AS AccountantAddress, [CityCPA] & (", "+[StateCPA]) & (" "+[PostalCodeCPA]) AS AccountantCity, Choose([MailTo], [AccountantFirm], [AccountName],[PrimaryOwner]) AS MailToName, Choose([MailTo],[AccountantAddress], [AccountAddress], [PrimaryOwnerAddress]) AS MailToAddress, Choose([MailTo], [AccountantCity], [AccountCity], [PrimaryOwnerCity]) AS MailToPostal
FROM qryClients
WHERE (((qryClients.IsActive)=True));

Thanks for the replies

I used Doug's Code in the AfterUpdate and it worked like it should...only one is checked.

When I tried the SQL that you gave me Doug...it could not find the MailTo and it errored.

Thanks
Larry

You need to change your table so that you have a MailTo field that contains the values 1, 2 or 3 instead of the three check boxes for that SQL to work.

Thanks again everyone... (IMG:style_emoticons/default/notworthy.gif)

I used Doug's first code and it seems to be working correctly.

You people are wonderful...wish I could have found this place many years ago.

Larry

Hi Larry,

Glad to hear you got it working with Doug's help. Good luck with your project.

Hello

Hope you didn't think I was only thanking Doug...I said thanks to everyone...just wanted to let you all know what I used.

Sorry for any mistake.

Thanks
Larry

Hi Larry,

No, I got it and on behalf of everyone...

(IMG:style_emoticons/default/yw.gif)

Cheers (IMG:style_emoticons/default/cheers.gif)

Hello once again

Am here to try and pick somebodies brain so I can get this to work because it looks so much better.

I have been trying to get the Option Group Work instead of using the check boxes ... (IMG:style_emoticons/default/iconfused.gif)

Can someone please tell me what I need to change or how to get this to work correctly?

Thanks
Larry

as Rob and Doug both said, you'd need to have one field in your table for MailTo, not three fields. unless the email should go to more than one recipient, now or in the future.

so first you need to answer the question: will the email always go to one recipient? if no, that requires further discussion. if yes, then did you replace the three fields in your table with one field MailTo? if yes again, then pls post the code you're using (if any) with the option group.

hth
tina

Thanks Tina

I put the MailToWhom in the table but I can't figure out how to use the prior code in the Option Group.

Really this won't be used for Email ... it is to tell the report which address to use on a form that will be snail mailed to the Client ... Primary Owner or the Accountant.

I would like to be able to put a email option on maybe later once I get this figured out.

Thanks
Larry

Howdy

FYI ... the way the buttons are on the Option Group are as follows if anybody can help me.

Mail to Client ....................... Option3
Mail to Primary Owner .......... Option5
Mail to Accountant................. Option7

Just thought I would try and get a jump start ... (IMG:style_emoticons/default/thumbup.gif)

Thanks
Larry

okay. i'd suggest a table listing the mailto options, as

tblRecipientTypes
typeID (primary key)
typeName

example records:



(the above is not code; i used a code box just to keep the columns separated and lined up.) the above table setup gives you infinite expandability for recipient types.

now let's stop and backtrack a minute. back in February, you posted the SQL statement for the report. that query is based on qryClients. pls post the SQL statement of qryClients.

hth
tina

Thanks Tina

Here is the SQL ... but for some reason it not updateable .... (IMG:style_emoticons/default/pullhair.gif)

SELECT tblClients.ClientID, tblClients.SSANEN, tblClients.SSN, IIf([SSANEN] Is Not Null,[SSANEN],IIf([SSN] Is Not Null,[SSN],Null)) AS FederalNo, tblClients.IsHuman, tblClients.Human, tblClients.IsActive, tblClients.FilingTypeIdFk, tblFilingTypes.CodeType, tblFilingTypes.FilingType, tblClients.MainName, tblClients.FirstName, tblClients.MiddleName, tblClients.Suffix, tblClients.SpouseName, [MainName] & (", "+[FirstName]) & (" "+[MiddleName]) & (", "+[Suffix]) & (" & "+[SpouseName]) AS ClientsName, tblClients.MailingAddress, tblClients.StreetAddress, [tblClients].[MailingAddress] & (", "+[tblClients].[StreetAddress]) AS StreetFileAs, tblClients.Occupation, tblClients.Plus4, [ClientPostalCode] & ("-"+[tblClients].[Plus4]) AS FullPostalCode, tblClients.ClientCity, tblClients.ClientState, tblClients.ClientStateAbbrev, tblClients.PostalIdFk, tblClients.ClientPostalCode, tblClients.ClientCounty, [ClientCity] & (", "+[ClientState]) & (" "+[FullPostalCode]) AS CityStateZip, qryCities.AreaCode, qryCities.Latitude, qryCities.Longitude, qryCities.CountryName, tblClients.TelephoneNumber, tblClients.MobileNumber, tblClients.FaxNumber, tblClients.statCode, tblClients.AccNo, tblClients.Closed, tblClients.MailTime, tblClients.MailToClient, tblClients.MailToOwner, tblClients.MailToAccountant, tblClients.DateCreated, tblClients.DateModified, tblClients.AddedBy, IIf([IsActive]=True,"O",IIf([IsActive]=False,"C")) AS Status, IIf([IsActive]=True,"Open",IIf([IsActive]=False,"Closed")) AS Status1, IIf([IsHuman]=True,"H",IIf([IsHuman]=False,"O")) AS PersonType, IIf([IsHuman]=True,"Human",IIf([IsHuman]=False,"Business")) AS PersonType1, IIf(IsNull([ClientsIdFk]),"NO",IIf(Not IsNull([ClientsIdFk]),"YES")) AS OwnerStatus, IIf(IsNull([NotesComments]),"NO",IIf(Not IsNull([NotesComments]),"YES")) AS NotesStatus
FROM (((tblClients LEFT JOIN qryCities ON tblClients.PostalIdFk = qryCities.AddID) LEFT JOIN tblFilingTypes ON tblClients.FilingTypeIdFk = tblFilingTypes.TypeID) LEFT JOIN qryClientsOwners ON tblClients.ClientID = qryClientsOwners.ClientsIdFk) LEFT JOIN sqryNotesStatus ON tblClients.ClientID = sqryNotesStatus.ClientIdFk
ORDER BY [MainName] & (", "+[FirstName]) & (" "+[MiddleName]) & (", "+[Suffix]) & (" & "+[SpouseName]);

I have another thread posted on it.

Thanks
Larry

the above is the SQL statement of qryClients? the first SQL statement you posted, with qryClients as the source, references fields that are not in the SQL statement above. i've bolded some, but not all, of those fields below. where are those fields coming from? or am i missing something?

SELECT qryClients.ClientID, qryClients.SSANEN, qryClients.IsActive, qryClients.MailToClient, qryClients.MailToOwner, qryClients.MailToAccountant, qryClients.AccountName, [ClientMailingAddress] & (", "+[ClientStreetAddress]) AS AccountAddress, [ClientCity] & (", "+[ClientState]) & (" "+[ClientPostalCode]) AS AccountCity, qryClients.PrimaryOwner, [OwnerMailingAddress] & (", "+[OwnerStreetAddress]) AS PrimaryOwnerAddress, [OwnerCity] & (", "+[OwnerState]) & (" "+[OwnerPostalCode]) AS PrimaryOwnerCity, qryClients.AccountantFirm, [MailingAddressCPA] & (", "+[StreetAddressCPA]) AS AccountantAddress, [CityCPA] & (", "+[StateCPA]) & (" "+[PostalCodeCPA]) AS AccountantCity, IIf([MailToClient]=True,[AccountName],IIf([MailToOwner]=True,[PrimaryOwner],IIf(
[MailToAccountant]=True,[AccountantFirm],""))) AS MailToName, IIf([MailToClient]=True,[AccountAddress],IIf([MailToOwner]=True,[PrimaryOwnerAdd
ress],IIf([MailToAccountant]=True,[AccountantAddress],""))) AS MailToAddress, IIf([MailToClient]=True,[AccountCity],IIf([MailToOwner]=True,[PrimaryOwnerCity],
IIf([MailToAccountant]=True,[AccountantCity],""))) AS MailToPostal
FROM qryClients
WHERE (((qryClients.IsActive)=True));

and btw, in the first SQL statement above, suggest you change the following expressions

from
IIf([IsActive]=True,"O",IIf([IsActive]=False,"C"))
to
IIf([IsActive]=True,"O","C")

from
IIf([IsActive]=True,"Open",IIf([IsActive]=False,"Closed"))
to
IIf([Isactive]=True,"Open","Closed")

from
IIf([IsHuman]=True,"H",IIf([IsHuman]=False,"O"))
to
IIf([IsHuman]=True,"H","O")

from
IIf([IsHuman]=True,"Human",IIf([IsHuman]=False,"Business"))
to
IIf([IsHuman]=True,"Human","Business")

from
IIf(IsNull([ClientsIdFk]),"NO",IIf(Not IsNull([ClientsIdFk]),"YES"))
to
IIf([ClientsIdFk] Is Null,"NO","YES")

from
IIf(IsNull([NotesComments]),"NO",IIf(Not IsNull([NotesComments]),"YES"))
to
IIf([NotesComments] Is Null,"NO","YES")

there is no need to use the IsNull() function in SQL, just use Is Null as the examples above show.

hth
tina