Full Version: Filtering My Form With Combobox
UtterAccess Forums > Microsoft® Access > Access Forms
ipisors
This should be so easy but it's not - I reviewed various web articles plus a post I found on UA, (sorry I didn't copy the link) with some great advice, but I can't get it to work.
ither it's not filtering, or it's updating a record - like as if I want to UPDATE the value in the combobox in the table itself, which I definitely don't want to do.
All I want to do is simple: Filter my form by the value in the field "USER" (query that supports form) which is also "CREATEDBY" (actual name of field in table).
THere is recordsource of form:
CODE
SELECT CTTLOG.APPID AS [App Id], CTTLOG.LOANNUMBER AS [Loan#], CTTLOG.CUSTOMERNAME AS Name, CTTLOG.LASTSIXVIN AS Vin, CTTLOG.CREATEDBY AS [User], CTTLOG.CREATEDONDATE AS [Create Date], CTTLOG.REFIYESNO AS [Refi?], CTTLOG.REFIALB AS Alb, CTTLOG.REFIWFDS AS WFDS, CTTLOG.REFILOANNUMBER AS [Refi Loan#], CTTLOG.TITLESAPPSYESNO AS [Titles?], CTTLOG.POAYESNO AS [POA?],

Here is RowSource of Combo54:
CODE
SELECT [CTTLOG].[CREATEDBY] FROM [CTTLOG];

Here is ControlSource of Combo54:
User
Apparently I have it all wrong. All the users want to be able to do is Filter the form based on the person who created the data entry record. In the QUERY that underlies the form, that is called "User", but in the table itself, that is called CREATEDBY.
Any help greatly appreciated.
Jeff B.
I can't tell if you are looking for a way to use a combobox to limit the record(s) displayed in your form to those 'belonging to' {selected-from-combobox}, or
.. using a form to "filter" a recordset.
If the latter, search on-line for MS Access and filter for lots of examples. Probably even some here at UA.
If the former, here's a generic outline of how I do that:
1. create a form that displays the fields I want to see
2. add an unbound combobox in the header, fed by a query that returns the selection criterion I want to use
3. create a query that returns the fields I want to see - be sure to include the field that holds whatever field matches up with the combobox (UNBOUND!)
4. in the query's Selection Criterion row, under the 'key' field (see #3), put something like: Forms!YourForm!cboYourCombobox -- use your form's name and combobox's name.
5. in the form's combobox's AfterUpdate event, put something like: Me.Requery
Is that what you're looking to do?
ipisors
when you say this:
a way to use a combobox to limit the record(s) displayed in your form to those 'belonging to' {selected-from-combobox}
oes it mean the functionality where, you click a selection in a drop-down, and the form "goes" to that particular record?
If that's what you mean, then no, I do not want that. (Sorry I'm still getting used to a lot of these terms and their corresponding concepts).
I want to filter any/all records in the form, to be only those where, it's a certain field value.
Does that make sense? In my ignorance, the two things you said at the beginning seem like the same thing kind of - or I am just confused I guess.
Anyway I followed your exact instructions, but nothing happens. I click on the combobox drop-down and see the selections I want. I click on one of them, and nothing happens. The form is still not filtered. ?
I did find example on UA to filter a form by combobox, but they contained various assumptions and specifications which I couldn't tell if I met or not. I just want to filter the darn records by a particular value.
So if the form is based on a query that shows 500 data entry records, I want to filter it by only one user who created them. And that user is one of the values in each data entry record. (as well as other places.
ipisors
wait, I think I see something in your instructions that maybe i misunderstood -
The instructions in step #4, I would do that for the query that underlies the form
ipisors
It's still not working.
ombobox after update event is me.requery. The form's recordsource is a query which, as the criteria for CREATEDBY, [Forms]![cttlogqc]![Combo58]
but when I select something in the combobox, nothing happens.
ALaRiva
Two things I noticed. Keep in mind I don't use Access anymore, so I might miss a thing or two, but i think I see a few problems.
) You say " The form's recordsource is a query which, as the criteria for CREATEDBY, [Forms]![cttlogqc]![Combo58]"
--- Isn't it supposed to be Combo54 as your posts above that indicate?
2) If this combo box is to be used for filtering, then you need to remove the Control Source. That is why it is updating the record.
3) Keep the requery.
Give that a shot and see where you are with things.
hth
ipisors
the combo58 vs. combo54 is because by then I was using a new combobox since I had tried to remove/add it back on so many times.
OK, somehow it seems to be working now. I am going to keep testing it , with more variety of values in that CREATEDBY field, as I'm not 100% sure but it does seem to be working. Thanks for all your
ALaRiva
Glad it's working.
Good Luck with the rest of your project.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.