I have two Yes/No fields in a table (ContractorInstall, and InHouseInstall)
in [Getting Started]
|
Prev: I have two Yes/No fields in a table (ContractorInstall, andInHouseInstall)
Next: Latest occurrence for same person
From: Jeanette Cunningham on 27 Jan 2010 03:10 Setting up Yes/No fields for this is better avoided. Create a table for InstallType. tblInstallType InstallTypeID - autonumber- Primary Key InstallTypeName - description of the type In the main table, put InstallTypeID as one of the fields. In the relationships window with both the main table and the install type table, select InstallTypeID from tblInstallType and drag it onto the matching field in the main table. In the data entry screen you will have a field for the InstallTypeID. Make this a combo. The combo's row source will be a query based on tblInstallType. So on the data entry screen, in the after update of the combo you can go If Me.NameOfCombo = 1 Then 'contractor, code to display the contractor survey Else 'code to display the in house survey End If The above assumes that 1 is the InstallTypeID for a contractor install. You will use the value from your InstallType table. You can also change the above slightly and make the InstallTypeID in the table a text field and make it the primary key. Doing it this way means you only need one field in the table and the values for InstallTypeID would be Contractor, and the next row would be Inhouse. It's up to you which way you do it. Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia <Tara Metzger> wrote in message news:201012712951tara(a)tanber.com... >I have two Yes/No fields in a table (ContractorInstall and InHouseInstall) >What I would like to happen in the form is as follows: Since one of the >boxes must be checked (yes), the checked box will display either the >contractor or the in-house survey depending upon which one is "yes". If >it's a contractor install, the contractor survey needs to be displayed. >The inhouse survey is to be hidden either from view or seen but blocked >from data entry, and vice versa. My question is this. Am I better trying >to set this up in the form itself or should I use queries to help? > Thank you, > Tara > > > Submitted via EggHeadCafe - Software Developer Portal of Choice > Silverlight, WCF, Security And Things You Might Not Know > http://www.eggheadcafe.com/tutorials/aspnet/eb009559-8467-4d49-a882-f6ac833492d5/silverlight-wcf-securit.aspx
From: John W. Vinson on 27 Jan 2010 12:33 On Tue, 26 Jan 2010 22:29:52 -0800, Tara Metzger wrote: >I have two Yes/No fields in a table (ContractorInstall and InHouseInstall) That's not an ideal design: I presume that if one is checked the other should not be? You wouldn't have a record where neither was checked, or both were checked, would you? If that is the case, I'd suggest using *one* field, Installer; it would be convenient to have this a Number/Integer field, and use an Option Group control on the form. This would let you store a 1 for Contractor, 2 for InHouse (and perhaps in the future 3 for Preinstalled and 4 for Customer Installed, if those options should come to pass); the Option Group would have two (or more) radio buttons with labels. -- John W. Vinson [MVP]
From: KenSheridan via AccessMonster.com on 27 Jan 2010 14:04
Tara: I'll come back to the question of having a single 'InstallType' column later, but firstly I'd like to examine just how the survey data is stored. I'm assuming the form is based on a 'Jobs' table or similar, so lets assume it has a primary key JobID. What I'd envisage for the surveys is a Surveys table which includes a foreign key JobID column to relate it to the Jobs table. This table would have a primary key SurveyID and columns for those attributes which are common to both types of survey, such as SurveyDate. As the two survey types presumably each have attributes which are distinct for each I'd then envisage two further tables, ContractorInstallSurvey and InHouseInstallSurvey say, each of which will again have a primary key SurveyID. The important point here is that the SurveyID columns in ContractorInstallSurvey and InHouseInstallSurvey are also foreign keys referencing the primary key of Surveys, i.e. the relationship type is one-to- one. Note that the SurveyID columns in ContractorInstallSurvey and InHouseInstallSurvey cannot be autonumbers, but that in Surveys can be. For data entry I'd envisage a form based on the Jobs table and within it two subforms, one for each survey type. The ContractorInstall survey subform would be based on a query which joins Surveys and ContractorInstallSurvey on SurveyID, the InHouseInstall survey subform on a query which joins Surveys and InHouseInstallSurvey on SurveyID. Both would be linked to the parent form on JobID. You could either superimpose the two subforms and show the relevant one. With your current two fields you'd put the following code in the AfterUpdate event procedures of both check boxes bound to these fields, and in the form's Current event procedure: Me.sfcContractorSurvey.Visible = Nz(Me.ContractorInstall,False) Me.sfcInHouseInstall.Visible = Nz(Me.InHouseInstall,False) where sfcContractorSurvey and sfcInHouseInstall are the names of the subform controls in the main form, i.e the controls which house the subforms. The reason for the Nz functions is that when you navigate to a new record a bound check box is Null until you start to enter data. The other option would be to have both subform's visible side by side or one above the other, or on separate pages of a tab control and enable/disable them as appropriate. In this case the code in the event procedures would be: Me.sfcContractorSurvey.Enabled = Nz(Me.ContractorInstall,False) Me.sfcInHouseInstall.Enabled = Nz(Me.InHouseInstall,False) Coming back to the issue of a single 'InstallType' field rather than your two Boolean (Yes/No) fields I'd agree completely with Jeanette and John on this. What you are doing at present is what's known as 'encoding data as column headings. A fundamental principle of the database relational model (the Information Principle) is that data is stored as values at column positions in rows in tables and in no other way.' What I've said above about the survey tables and subforms would still apply, however. You'd just amend the code along these lines: Me.sfcContractorsurvey.Enabled = (Nz(Me.InstallType,"") = "Contractor") Me.sfcInHouseInstall.Enabled = (Nz(Me.InstallType,"") = "InHouse") Ken Sheridan Stafford, England Tara Metzger wrote: >I have two Yes/No fields in a table (ContractorInstall and InHouseInstall) What I would like to happen in the form is as follows: Since one of the boxes must be checked (yes), the checked box will display either the contractor or the in-house survey depending upon which one is "yes". If it's a contractor install, the contractor survey needs to be displayed. The inhouse survey is to be hidden either from view or seen but blocked from data entry, and vice versa. My question is this. Am I better trying to set this up in the form itself or should I use queries to help? >Thank you, >Tara > >Submitted via EggHeadCafe - Software Developer Portal of Choice >Silverlight, WCF, Security And Things You Might Not Know >http://www.eggheadcafe.com/tutorials/aspnet/eb009559-8467-4d49-a882-f6ac833492d5/silverlight-wcf-securit.aspx -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/201001/1 |