filter assistance requested

[cyberbob999]

I'm trying to filter my recordset using a combobox. This seems like it should be an easy task to me, but I can't make it work. I've followed various examples in this forum, but I can't get any of them to work. Can someone please point out what I'm doing wrong?

This is what I've tried:
[filPerformer] is the combo box
I just have the "msgbox filname" in there as a debugging tool to make sure filname is being assigned the right name, and it is.


Private Sub FilPerformer_AfterUpdate()

filname = DLookup("[performer]", "List of Surveillance Performers", "[IDnumber] = Forms![edit_surv]! _[FilPerformer]&quot
MsgBox filname
Me.Filter = "[performer] like" & filname
Edit_Surv.FilterOn = True

End Sub

When I run it shown above, I get : "You can't assign a value to this object"

I also tried:

a few variations of this:

Private Sub FilPerformer_AfterUpdate()

filname = DLookup("[performer]", "List of Surveillance_ Performers", "[IDnumber] = Forms![edit_surv]![FilPerformer]&quot
MsgBox filname
Me.Filter = "[performer] = filname"
Me.FilterOn = True

End Sub

Again, filname is being assigned the right value, but when I update the combo box I am prompted with an "Enter paramet value window" for "filname." I don't understand why the function is not recognizing the variable I already have established???

also, the reason I am doing the DLookup is because when I change the combobox FilPerformer, FilPerformer returns the IDnumber associated with the name I selected.

I am tired of banging my head against the wall--please help :*(

Oh, and I've tried to access Bill Power's site to download his filterforms example, but I haven't been able to connect to it for the past 3 days (I have connected in the past, so I don't know what the deal is!)

 

[KUZZ]

Me.Filter = "[performer] = filname"

try

Me.Filter = "[performer] = " & filname

or

Me.Filter = "[performer] =" & "'" & filname & "'"


the second option should deffinitely work.



Good luck,
Kuzz

"Time spent debating the impossible subtracts from the time during which you
can try to accomplish it."

 

[Hasu]

Hi cyberbob,

try this:

Private Sub FilPerformer_AfterUpdate()

filname = DLookup("[performer]", "List of Surveillance_ Performers", "[IDnumber] = " & [FilPerformer])
MsgBox filname
Me.Filter = "[performer] = '" & filname & "'"
Me.FilterOn = True

End Sub


Hope this helps...
Hasu
(Trust on someone, someone will trust you!)

 

[lamarw]

Is this code in a module? If so then you will have to be explicit witht he form reference rather than using me.filter. If not then please provide a little more

 

[cyberbob999]

Hasu you're a genius! You have NO IDEA the amount of time I spent trying to get that right! I thought I had tried every combination of brackets, single quotes and double quotes possible!

To be honest, right now I don't really care why it works, just that it works ...but, I know I will run in to this problem again if I don't understand why mine didn't work. If you have the time (and it's not too lengthy/difficult) could you please explain why your code worked and mine doesn't?

A million thanks!!!

 

[cyberbob999]

I hate to be greedy, but I have one more question: How do I pass the same variable over to a query? For example if my record source was a query and I wanted the filter criteria to be "[performer] like filPerformer"...

thanks

 

[Hasu]

Hi cyberbob,

sorry for delay!

Que. 1 : could you please explain why your code worked and mine doesn't?
Answer 1:

in your code,
option-1:
Me.Filter = "[performer] like" & filname
that should be Me.Filter = "[performer] like '" & filname & "'"
text type always require quotation marks.

option-2:
Me.Filter = "[performer] = filname" you specified 'filname' variable inside filter that means that 'filname' will treated as parameter value that will prompt while it's trying to filter records. You should pass contents of the varible to filter records. that you can do with only conjuction of string with varible (i.e. Me.Filter = "[performer] = " & filname this will treat as me.filter="[performer] = testfile&quot

Que. 2 : How do I pass the same variable over to a query?
Answer 2:
If you are using stored query or defined query as recordset then after setting query.

for example,
your query look like follows:
query1= "Select fname,lname,grade from student where lname like [lastname]"

where [lastname] is the parameter that will prompt if not specified.

Set qdfQuery = db.QueryDefs("query1&quot

you can pass parameter as below:
qdfQuery.Parameters(0) = strLastname
OR
qdfQuery.Parameters("lastname&quot = strLastname

that will filter records, without prompting.




Hope this helps...
Hasu
(Trust on someone, someone will trust you!)