filter basics

[cyberbob999]

For the life of me I cannot figure out how to filter a recordset dynamically based on a combobox value...

I have a form that uses a table (surveillances) as a source. My combobox is cboFilName. The field that I want to filter by is [performer]. If someone could please explain to me how to do this, I would be eternally grateful!

If I may be greedy, however, what I would ultimately like to do is have several comboboxes, each one that correlates to a field. And then, be able to narrow down my recordset by selecting multiple values to filter by (for example, [performer]=forms![edit_surv][cboFilName] and [date] = forms![edit_surv][cboFilDate] and etc...) where each combobox will have the approriate names as well as an "Any" option (*)...

ANY assistance would be appreciated...Thanks!


Oh, one more very basic question that I can't seem to figure out... I don't know what the "me" does in such items as me.filter or me.whatever. If you could briefly exlain that to me, I'd appreciate it!

 

[hwkranger]

me is the current form - instead of writing out the entire things.

you could build a filter in the background and apply it each time you cange your combo boxes. You'll have to do it entirely with VBA.

I would use a private function to accomplish this.

I don't have time to write this out but i''ll steer you in the right direction.


use a string that is built when you change the combo
pass the string, and edit it based upon what you select in your combo boxes
use the .filter property , change it.





Randall Vollen
National City Bank Corp.

Just because you have an answer - doesn't mean it's the best answer.

 

[cyberbob999]

I think I figured out what I was doing wrong...but now I have another fundamentals question...

I have comboBox (cboName) which looks up values in a table. Let's say I select "Smith" from the list (cboName) and then MsgBox cboName, I get a number returned (the primary key assoicated with Smith in the table). How do I specify that I want the name returned from cboName?

Otherwise, when I pass the string to the filter, it is filtering with the IDnumber, not the actual name...

Thanks

 

[markgrizzle]

You'll probably be better off using the id value as the filter, while displaying the string.

Having said that, to get the name associated with the id,

private function GetName() as string

dim db as database
dim rs as recordset
dim sql as string

sql = "Select Name From table Where ID = " & me.cboName

set db = currentdb()
set rs = db.openrecordset(sql)

if not rs.eof then
GetName = rs("Name&quot
else
GetName = "No Name Found"
end if

rs.close
set rs = nothing

db.close
set db = nothing

Exit function

end function

I've left out error handling, etc., and doublecheck the syntax on the openrecordset, my dao is rusty.