Change AllowEdits based on y/n field value?

[Aikistan]

[Solved] Change AllowEdits based on y/n field value?

I know this is possible...I've done it before but I just can't remember how anymore since my mini stroke. I have two related questions about the following database.

I have Form A and it has a subform B. A enters information about a sponsor organization and B is for entering discrete documents the organization sponsors. In some cases, document responsibility can be passed to a second organization, who can also be a sponsor of their own documents.

Form A enters information into tblSponsors. There are lookup tables for States (lutblStates) and Sponsors (lutblSponsors) and I have combo boxes for those (and they work!). I have a Yes/No on Form B to check if the Form A Sponsor is the responsible party for that particular document. If they are different, I want to enable entry of the responsible party from the table of Sponsors in a combo box using the same lutblSponsors.

My questions:

1. How and where do I add Event code to enable edits in the SecondPartySponsor combo box based on whether the IsThereSecondParty field is true? Both controls are on the same form.

2. The Sponsors entered on Form A and B are limited to values in lutblSponsors. This lookup table contains fields SponsorID, State, and Name. I want to have the combo list sorted by State and display State, SponsorID, and Name. (Some users know the numbers and some only know the names. Also, some names are duplicated in various States.)
​

The second question is particularly frustrating for me. I know it's easy but the method just isn't in my head anymore. :banghead:

Preemptive thanks!

Stan

 

[pbaldy]

1: http://www.baldyweb.com/ConditionalVisibility.htm

2: I'm not really clear on the question. Using a query/SQL for the row source will let you apply any sorting you want.

 

[isladogs]

2. The Sponsors entered on Form A and B are limited to values in lutblSponsors. This lookup table contains fields SponsorID, State, and Name. I want to have the combo list sorted by State and display State, SponsorID, and Name. (Some users know the numbers and some only know the names. Also, some names are duplicated in various States.)

Not sure whether you're asking how to get the row source for the combobox or how to display all 3 fields. So I'll answer both points. Apologies if you already know some / all of this.

In the property sheet for the combobox, click row source then click the ellipsis button (...) and enter the following:

SELECT DISTINCT lutblSponsors.State, lutblSponsors.SponsorID, lutblSponsors.Name
FROM lutblSponsors
ORDER BY lutblSponsors.State;

Using DISTINCT will ensure duplicates aren't displayed.
Omit if you want to see duplicates listed

Next set Column Count = 3 and set the column widths to e.g. 1cm, 1cm, 1cm (adjust as necessary)

HTH