Solved: Option Group to select records on Form

[DomFino]

Hi everyone,
I am at a loss with regard to the following:

I have a form (frmRequisitionDetail) that has a query as the record source. On the form I have an Option Group with 7 selections. Selection 1 through 6 are for individuals and Selection 7 is for ALL.

What I am trying to do is have the form open with option 7 (show ALL records) as the default. Then a user can select their individual option (1 through 6) to see just their records displayed on the form.

Here is the SQL for my form query:
SELECT tblVacancyMaster.MasterID, tblVacancyMaster.PriorityID, tblVacancyMaster.Salary,
tblVacancyMaster.SubNumber, tblVacancyMaster.StatusID, tblVacancyMaster.VacancyID,
afrequis.o_recruit, afrequis.o_company, tblPriorityLKU.PriorityDesc, tblStatusLKU.StatusDesc,
tblVacancyLKU.VacancyDesc, afrequis.code, afrequis.desc, tblVacancyMaster.NumberVacancies,
tblVacancyMaster.NumberSubmittals, afrequis.o_opendte, tblVacancyMaster.DateNeeded,
(Date()-[o_opendte]) AS [Days Open], tblVacancyMaster.SourcerCode, tblVacancyMaster.EmailSent
FROM (tblVacancyLKU RIGHT JOIN (tblStatusLKU RIGHT JOIN (tblPriorityLKU
RIGHT JOIN tblVacancyMaster ON tblPriorityLKU.PriorityID = tblVacancyMaster.PriorityID)
ON tblStatusLKU.StatusID = tblVacancyMaster.StatusID) ON tblVacancyLKU.VacancyID =
tblVacancyMaster.VacancyID) LEFT JOIN afrequis ON tblVacancyMaster.SourcerCode =
afrequis.code
WHERE (((tblVacancyMaster.StatusID) <> 2))
ORDER BY afrequis.code;



Here is the code behind the Option Group:
Private Sub Select_Record_AfterUpdate()
If Me.Select_Record.Value = 7 Then
DoCmd.RunCommand acCmdRemoveFilterSort
End If
If Me.Select_Record.Value = 6 Then
DoCmd.ApplyFilter , "[MDL] = " & Me.Select_Record.Value
End If
If Me.Select_Record.Value = 5 Then
DoCmd.ApplyFilter , "[JLE] = " & Me.Select_Record.Value
End If
If Me.Select_Record.Value = 4 Then
DoCmd.ApplyFilter , "[DR] = " & Me.Select_Record.Value
End If
If Me.Select_Record.Value = 3 Then
DoCmd.ApplyFilter , "[GMK] = " & Me.Select_Record.Value
End If
If Me.Select_Record.Value = 2 Then
DoCmd.ApplyFilter , "[AM] = " & Me.Select_Record.Value
End If
If Me.Select_Record.Value = 1 Then
DoCmd.ApplyFilter , "[ALD] = " & Me.Select_Record.Value
Else
DoCmd.ApplyFilter , "[All] = " & Me.Select_Record.Value
End If

End Sub


My gut tells me I am going about this all wrong so any guidance you can offer will be much appreciated.
Thanks,
Dom

[geekgirlau]

Hi Dom,

I've edited your post to put some line breaks in the SQL just to make it easier to read.

Something like this might work:

Private Sub Select_Record_AfterUpdate()
If Me.Select_Record.Value <> 7 Then
Select Case Me.Select_Record.Value
Case 1
Me.Filter = "[ALD] = " & Me.Select_Record.Value
Case 2
Me.Filter = "[AM] = " & Me.Select_Record.Value
Case 3
Me.Filter = "[GMK] = " & Me.Select_Record.Value
Case 4
Me.Filter = "[DR] = " & Me.Select_Record.Value
Case 5
Me.Filter = "[JLE] = " & Me.Select_Record.Value
Case 6
Me.Filter = "[MDL] = " & Me.Select_Record.Value
End Select

Me.FilterOn = True

Else
Me.FilterOn = False
End If
End Sub

My only query here is that I can't see these fields ("ALD", "GMK" etc.) in your record source - are these the field names, or the values in a field that you want to filter?

[DomFino]

Hello geekgirlau,
Thanks for your reply. I like the idea of the case statement. As you said it sure makes it easier to read!

The field o_recruit (Recruiter) contains the identifier or values for each recruiter. The values are "ALD", "AM", ect.

Hope that helps.
Dom

[Norie]

You seem to be using the ALD, AM etc as fields rather than values in your code.



Me.Filter = "[o_Recruiter]='" & "ALD" & "'"



Me.Filter = "[o_Recruiter] = '" & Me.Select_Record.Value &"'"

[DomFino]

Hi everyone,
I took an entirely different approach to solve this problem I created a function that is called by the option group selection. It now works like a charm.
I wish to thank all of your for your suggestions and help.
Dom :friends: