Originally Posted by
June7
If you want to allow multiple checkboxes to be selected then each must be bound to a separate field and not within an OptionGroup and no code. However, this appears not to be a normalized data structure and will likely cause you frustrations in the future.
I was able to get the Qualifications combobox to allow a Null response to return ALL possible choices, in addition to the ability for it to simply pass the single value selected by the combobox instead. I got the idea from a post involving Ken Sheridan and someone named Ed...
HTML Code:
http://msgroups.net/microsoft.public.access.gettingstarted/pass-field-value-selec/99189
Here is the QualificationsID final IIf statement which allows for a selection from the combobox And will also return all records if the combobox value is Null:
Code:
Like IIf([Forms]![f_Choose_Regions]![cboQualifications] Is Null,"*",[Forms]![f_Choose_Regions]![cboQualifications]) Or Is Null
So all is not lost as the user can choose any or all regions, it just means more clicking for them. Here is the link to the database if anyone wishes to look at it:
HTML Code:
http://1drv.ms/Yvjmkg
.
Here is the SQL from the query based on the parameter form f_Choose_Regions:
Code:
SELECT lt_Region.RegionNumber, lt_Status.StatusName, t_FSESO.QualificationsID, t_FSESO.InstructorID, t_FSESO.RegionID, t_FSESO.FName, t_FSESO.LName, t_FSESO.StatusID, lt_Qualifications.QualificationsDescFROM lt_Status INNER JOIN (lt_Region INNER JOIN (lt_Qualifications INNER JOIN t_FSESO ON lt_Qualifications.QualificationsID = t_FSESO.QualificationsID) ON lt_Region.RegionID = t_FSESO.RegionID) ON lt_Status.StatusID = t_FSESO.StatusID
WHERE (((lt_Region.RegionNumber) Like IIf([Forms]![f_Choose_Regions]![Reg1]=-1,1,"") Or (lt_Region.RegionNumber) Like IIf([Forms]![f_Choose_Regions]![Reg2]=-1,2,"") Or (lt_Region.RegionNumber) Like IIf([Forms]![f_Choose_Regions]![Reg3]=-1,3,"") Or (lt_Region.RegionNumber) Like IIf([Forms]![f_Choose_Regions]![Reg4]=-1,4,"") Or (lt_Region.RegionNumber) Like IIf([Forms]![f_Choose_Regions]![Reg5]=-1,5,"") Or (lt_Region.RegionNumber) Like IIf([Forms]![f_Choose_Regions]![Reg6]=-1,6,"") Or (lt_Region.RegionNumber) Like IIf([Forms]![f_Choose_Regions]![Reg7]=-1,7,"") Or (lt_Region.RegionNumber) Like IIf([Forms]![f_Choose_Regions]![Reg8]=-1,8,"")) AND ((lt_Status.StatusName) Like IIf([Forms]![f_Choose_Regions]![ACTIVE]=-1,"ACTIVE","") Or (lt_Status.StatusName) Like IIf([Forms]![f_Choose_Regions]![INTERIM]=-1,"INTERIM","") Or (lt_Status.StatusName) Like IIf([Forms]![f_Choose_Regions]![PROBATION]=-1,"PROBATION","") Or (lt_Status.StatusName) Like IIf([Forms]![f_Choose_Regions]![ON LEAVE]=-1,"ON LEAVE","") Or (lt_Status.StatusName) Like IIf([Forms]![f_Choose_Regions]![SUSPENDED]=-1,"SUSPENDED","") Or (lt_Status.StatusName) Like IIf([Forms]![f_Choose_Regions]![RESIGNED]=-1,"RESIGNED","") Or (lt_Status.StatusName) Like IIf([Forms]![f_Choose_Regions]![DE-CERTIFIED]=-1,"DE-CERTIFIED","") Or (lt_Status.StatusName) Like IIf([Forms]![f_Choose_Regions]![DECEASED]=-1,"DECEASED","")) AND ((t_FSESO.QualificationsID) Like IIf([Forms]![f_Choose_Regions]![cboQualifications] Is Null,"*",[Forms]![f_Choose_Regions]![cboQualifications]) Or (t_FSESO.QualificationsID) Is Null));
Hope this helps someone as I have been weeks working on this...so thanks so much for steering me in the right direction!
Bill