Full Version: Listbox Filter
UtterAccess Forums > Microsoft® Access > Access Forms
natashaepperson
I tried asking this question before but i think i wasn't asking it correctly.
On my form: frm_Traveler I have a list box with 4 fields- JL_PK, CoNum, FSS and Location Number
If you click on the list box you can type in the PK number and it will go to that number.
I want to make it so that i can type in say a number for CoNum and it will go to that number in the list or Location Number and it will do the same. Not sure if i want it to filter down to the selected list or not. But i would need it to show the record selected once it's selected if you re-open the form.
GroverParkGeorge
You'll need to do this with a separate, unbound, text box on the form through which you can enter values, e.g. the CoNum, and peform a search from that separate searching text
natashaepperson
I've got a version where that works. However when you close the form, it no longer shows the items that were selected in the list box and i need it to show the items that were on the list box when i got back on the form.
GroverParkGeorge
"I've got a version where that works. " What is it that works in your other version? My suggestion regarding an unbound text box? Or a list box where you can select items from different columns in the listbox? If you meant the latter, it might be helpful to show that in your upload. It's not something I've encountered previously, so I'd be interested in seeing how you were able to implement it.
On order to "save" the previously selected items in the list box, you need to Save them somehow. And that means a more detailed explanation of what you are saving.
But perhaps a brief overview of how forms work and tables work might also help us get on the same page.
All data is stored in tables.
No data is permantently stored in forms, nor in any control on a form. Therefore, any selection if ANYTHING in any form is valid only while that form is open with the selections made. As you've noted, closing the form releases and values selected in the form at the time.
Your stated goal "show the items that were on the list box when i got back on the form" means that you MUST save those selections to a table before closing the form with the selections.
To do that, of course, you can follow one or more approaches: One would be to create a temp table into which you can save selections before closing the form. Another would be to save a value in a field in the table from which the values in the list box are taken--that value would indicate which items on the list were selected
natashaepperson
Sorry for my terminology mistake. Yes the data is stored in the tables.... my reply post to you shows the "working" version that filters the list box by text box.
The issue is not that the data is not being stored. If you look at the database, when you filter and select the option it stores the data correctly into the tables as that list box is tied to a field in the table. The problem is when you open the form again and return to the record it will not filter to that selected option in the list box which is what I’m attempting to figure a way to do. So I need a way to filter to the specific selection on open, yet if I don’t have a record to allow for the text box to do the filtering but I’m not sure how to do that.
Hope that makes better sense.
GroverParkGeorge
The Form is merely a conduit through which you can manipulate records in tables. How you do that determines the results you see.
This sample has no filtering text box, at least not that I can identify. There are TWO forms, one called "frm_Traveler" and one called "Frm_Junction_Location". Of the two, only one has a list box on it: "frm_Traveler", so that is the form I assume you want to work with here.
That list box IS bound to the field "Junction_fk" in the table called "tbl_Traveler". And, yes, as you indicate, selecting a value in that list box does store the value in the underlying field. However, that list box is ADDITIONALLY filtered on the field called "coValueID" in the table called "tbl_CoValue": i.e. "WHERE (((tbl_CoValue.coValueID)=[forms]![frm_Traveler]![cmbENTITY_FK]))
coValueID is bound to the combo box called cmbENTITY_FK.
That means when the form opens, and when you select a particular traveler, the List box can not show the correct "Junction_fk" until after you select the correct "coValueID' in the combo box and requery the list box. That, in turn, means your requirement is met by selecting first, the traveler, then the "coValueID'. Your full requirement can only be met by somehow figuring out what value to select for the combo box from the value stored in tbl_traveler for each Traveler_Pk.
The current event in the form can be used to retrieve that value, set the combo box to that value, and then requerying the list box. Here's how you'd do that.
CODE
Private Sub Form_Current()
Dim intCoValueID As Integer
If Not Me.NewRecord Then
    intCoValueID = Nz(DLookup("Entity_fk", "tbl_Junction_Location", "tbl_Junction_Location.JL_Pk  =" & Me.Junction_fk), 0)
    If intCoValueID > 0 Then
        Me.cmbENTITY_FK = intCoValueID
        Me.list4_fk.Requery
    End If
    
End If
End Sub
natashaepperson
perfect! thanks, now to do more testing <
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.