Filter Form with Option Group problem

[DomFino]

Hi everyone,
I am trying to do something similar to thread702-940780, but keep getting an error.

I have a form that has an option group with 6 options. What I want the form to do on open is show all records. If one of the 6 options is selected, then filter the records for that specific Representative. I suspect I need another option 7 for ALL so that a user can have all records displayed. Not sure at this point. I also would need a way to pop up a message if NO records were found on any specific option (1 through 6).

Here is the code I have in a Module.

 Option Compare Database

Public Function OptMask()
   Dim frm As Form, opt As OptionGroup
   
   Set frm = Forms![frmRequisitionDetail]
   Set opt = frm!OptRep
   
   If opt.Value = 1 Then
      OptMask = "ALD"
   ElseIf opt.Value = 2 Then
      OptMask = "AM"
   ElseIf opt.Value = 3 Then
      OptMask = "GMK"
   ElseIf opt.Value = 4 Then
      OptMask = "DR"
   ElseIf opt.Value = 5 Then
      OptMask = "JLE"
   ElseIf opt.Value = 6 Then
      OptMask = "MDL"
   Else
      OptMask = "*"
   End If
 
   Set opt = Nothing
   Set frm = Nothing

Here is the SQL I have as the form datasource.

 SELECT tblVacancyMaster.MasterID, tblVacancyMaster.PriorityID, tblVacancyMaster.Salary, tblVacancyMaster.SubNumber, 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 (((afrequis.o_recruit) [b]Like OptMask()[/b]) AND ((tblVacancyMaster.StatusID)<>2))
ORDER BY afrequis.code;

When I open the database, I keep getting Error 2427, You entered an expression that has no value.

Can anyone tell me where I am going wrong with this code?
Thanks,
Dom

 

[NorthNone]

Do you need a wildcard character?
OptMask = "GMK*"
instead of
OptMask = "GMK"
etc...

---------------------------------------
The customer may not always be right, but the customer is ALWAYS the customer.

 

[DomFino]

NorthNone,
Thanks for the reply. I will give that a try and post back.
Dom

 

[DomFino]

NorthNone,
I tried your suggestion but still get the same error. This has been driving me nuts. The odd thing is I can run the query without the Like OptMask() in the criteria and it works fine. If I put in "GMK" in the criteria, it too works correctly by only retrieving the records for GMK.
Dom

 

[NorthNone]

Here's a couple things to try. Forgive me if you've been down these paths before. Let me know what you find out. I'm curious now...
---
Add
Option Explicit
up with
Option Compare Database
and recompile the code. See if anything pops out from that.
---
Do a debug.print after form datasource. If you don't see anything wrong with it by looking at it, copy it from the immediate window and plop it into a blank query in SQL mode. Try to run it from there. Sometimes you will get an 'improved' error message that will tell you more.
---


---------------------------------------
The customer may not always be right, but the customer is ALWAYS the customer.

 

[PHV]

I think the problem is that the RecordSource of the form is evaluated when the form opens (ie not yet loaded) and thus the function can't evaluate OptRep.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[DomFino]

NorthNone & PH
I tried adding the Option Explicit, recompiled but nothing changed.

I just read PH's response and that makes sense to me. Then what do I know, I am still stuck. Is there a way to have the function delayed until after the form is open?
Dom

 

[rubbernilly]

Also, you might check out this link:

http://www.databasejournal.com/features/msaccess/article.php/10895_3444891_2

Look specifically at the example where he populates a Listbox control with a set of options, with the last option being an "ALL ITEMS" entry. That might give you more ideas.

 

[PHV]

Just a note, I don't see the End Function instruction in your original post ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[DomFino]

PH,
Sorry, I must of fat fingered the cut and past. The end funtion is there. Here is the actual code for the funtion.

Public Function OptMask()
   Dim frm As Form, opt As OptionGroup
   
   Set frm = Forms![frmRequisitionDetail]
   Set opt = frm!OptRep
   
   If opt.Value = 1 Then
      OptMask = "ALD"
   ElseIf opt.Value = 2 Then
      OptMask = "AM"
   ElseIf opt.Value = 3 Then
      OptMask = "GMK"
   ElseIf opt.Value = 4 Then
      OptMask = "DR"
   ElseIf opt.Value = 5 Then
      OptMask = "JLE"
   ElseIf opt.Value = 6 Then
      OptMask = "MDL"
   Else
      OptMask = "*"
   End If
 
   Set opt = Nothing
   Set frm = Nothing

End Function

 

[TheAceMan1]

How are ya DomFino . . .

I've had similar problems with list/combo boxes and have to agree with [blue]PHV[/blue]:

[blue]I think the problem is that the RecordSource of the form is evaluated when the form opens (ie not yet loaded) and thus the function can't evaluate OptRep.[/blue]

This is further supported by my simulation of your problem in 2K and obtaining the following error:

[blue]Error 2427: You entered an expression that has [purple]no value[/purple][/blue]

Surely the query is executed before the option group is set.

Checking over my solutions for this type of problem, I find in 93% of the cases what I did was [blue]load the RecordSource of the form with the SQL, via the forms Open event[/blue] (Don't forget to save the form with an empty recordsource first).

If you'd like to give it a shot, here's the code for the [blue]Open[/blue] event:

[blue]   Dim SQL As String

   SQL = "SELECT tblVacancyMaster.MasterID, tblVacancyMaster.PriorityID, tblVacancyMaster.Salary, " & _
                "tblVacancyMaster.SubNumber, 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 (((afrequis.o_recruit) Like OptMask()) And ((tblVacancyMaster.StatusID) <> 2))" & _
         "ORDER BY afrequis.code;"
   Me.RecordSource = SQL[/blue]

As for your [blue]OptMask[/blue] function:

[blue]Public Function OptMask()
   Dim Opt As OptionGroup, idx As Integer
   
   Set Opt = Forms!frmRequisitionDetail!OptRep
   idx = Nz(Opt.Value, 7)
   OptMask = Choose(idx, "ALd ", "AM", "GMK", "DR", "JLE", "MDL", "*")

End Function[/blue]

I think you'll find you need to add [blue]All[/blue] to the group (once a selection is made there's no way to get back to all).

See Ya! . . . . . .

 

[DomFino]

Hi AceMan,
Thanks so much for the reply and code suggestions. I did exactly as you suggested and here is what I get.

Good News:
I added option 7 to the option group for ALL
The form opens fine with all records available.

Bad News:
When I select one of the other available options the form show calculating in the bottom right of the screen and then the same (ALL) records are available. In other words no filtering is performed.

I suspect the function is working properly since I do not receive any errors. However, in the former function there was specific code that said

If opt.Value = 1 Then
      OptMask = "ALD"

etc. In the new function, I do not see how the OptMask knows what value was selected in the option group on the form OptRep.

I think you have managed to get me 99% there with your expert suggestions. Now I need your help to get that last 1%, which is always the one that causes me to get gray hair. Any suggestions?

Thanks again for your help,
Dom

 

[PHV]

Replace this:
Public Function OptMask()
with this:
Public Function OptMask(dummy)

And this:
Like OptMask()
By this:
Like OptMask(tblVacancyMaster.VacancyID)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[DomFino]

PHV,
Thanks for the tips. I applied them but still do not get filtered records when I select an option.

My Funciont code is now:

Option Compare Database
Option Explicit
Public Function OptMask(dummy)
   
   Dim Opt As OptionGroup, idx As Integer
   
   Set Opt = Forms!frmRequisitionDetail!OptRep
   idx = Nz(Opt.Value, 7)
   OptMask = Choose(idx, "ALD", "AM", "GMK", "DR", "JLE", "MDL", "*")

End Function

My on open SQL is as follows:

Private Sub Form_Open(Cancel As Integer)
Dim SQL As String

   SQL = "SELECT tblVacancyMaster.MasterID, tblVacancyMaster.PriorityID, tblVacancyMaster.Salary, " & _
                "tblVacancyMaster.SubNumber, 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 (((afrequis.o_recruit) Like OptMask(tblVacancyMaster.VacancyID)) And ((tblVacancyMaster.StatusID) <> 2))" & _
         "ORDER BY afrequis.code;"
   Me.RecordSource = SQL


End Sub

 

[PHV]

What is the code of the AfterUpdate event procedure of OptRep ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[DomFino]

Ahhh! I don't have any code there!

 

[DomFino]

All,
I put Me.Requery in the Afterupdate of the OptRep properties and it all works!
I am an idot!
Thanks for all your help.
Dom

 

[PHV]

You may try this:
Dim SQL As String
SQL = Me.RecordSource
Me.RecordSource = ""
Me.RecordSource = SQL

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[DomFino]

PHV,
I used your suggested code and had to modify one option in the function ("AM") changed to ("AM*") and it works like a dream. You are one heck of a programmer/guru!
Have a great day,
Dom