Filtered listbox with selected item to open record.

%3CLINGO-SUB%20id%3D%22lingo-sub-1873068%22%20slang%3D%22en-US%22%3EFiltered%20listbox%20with%20selected%20item%20to%20open%20record.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1873068%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20listbox%20I%20have%20doing%20two%20things.%26nbsp%3B%20It%20filters%20criteria%20based%20on%20a%20Textbox.Text%20with%20an%20OnChange%20event.%26nbsp%3B%20Code%20works%20beautifully%20individually!%26nbsp%3B%20Plenty%20of%20tutorials%20using%20a%20WHERE%20in%20an%20SQL%20statement%20for%20the%20listbox%20source.%3CBR%20%2F%3E%3CBR%20%2F%3EIn%20this%20same%20Listbox%20I%20use%20a%20OnDblClick%20event%20to%20open%20a%20form%20and%20filter%20for%20the%20selected%20item%20using%20the%20Listbox.Column()%20property%20to%20forward%20the%20value%20to%20WHERE%20criteria%20of%20the%20opened%20form.%26nbsp%3B%20Code%20also%20works%20beautifully%20individually.%3CBR%20%2F%3E%3CBR%20%2F%3EHere%20is%20my%20problem%2C%20say%20I%20do%20a%20search%20in%20the%20textbox%20and%20this%20filters%20the%20records%20of%20the%20listbox.%26nbsp%3B%20For%20some%20odd%20reason%20I%20can%20no%20longer%20grab%20a%20value%20of%20the%20selected%20item%20in%20the%20listbox.%26nbsp%3B%20Further%2C%20if%20I%20right-click%20to%20copy%20the%20field%20it%20gives%20me%20a%20error%20telling%20me%20%22there%20isn't%20enough%20memory%22%20which%20is%20absurd%20thinking%20the%20data%20is%20a%20few%20bytes%20on%20a%2016GB%20RAM%20memory%20system.%3CBR%20%2F%3E%3CBR%20%2F%3EI'm%20scratching%20my%20head%20on%20this%20one%20for%20a%20couple%20of%20days.%26nbsp%3B%20I%20tried%20Listbox.Value%20even.....doesn't%20matter!%26nbsp%3B%20Again%2C%20the%20code%20works%20as%20desired%20individually%2C%20but%20I%20have%20no%20way%20of%20pulling%20data%20from%20the%20listbox%20once%20I%20apply%20a%20filter%20to%20its%20contents%20based%20on%20a%20form's%20textbox.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1873068%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAccess%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1873192%22%20slang%3D%22en-US%22%3ERe%3A%20Filtered%20listbox%20with%20selected%20item%20to%20open%20record.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1873192%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F432063%22%20target%3D%22_blank%22%3E%40AmedMesa%3C%2FA%3E%26nbsp%3BHello%2C%20Amed%2C%20just%20a%20quick%20thought%20on%20the%20memory%20message.%26nbsp%3B%20This%20may%20be%20obvious%2C%20but%20have%20you%20restarted%20your%20computer%3F%26nbsp%3B%20I%20have%20found%20in%20previous%20versions%20of%20Access%20and%20on%20O%2FS%20before%20Win%2010%20(which%20I%20am%20only%20starting%20to%20use%20these%20past%20couple%20of%20weeks)%20that%20sometimes%20my%20computer%20would%20get%20%22tired%22%20and%20require%20a%20reboot.%26nbsp%3B%20I've%20frequently%20encountered%20situations%20where%20MS%20Access%20would%20not%20even%20%3CEM%3Estart%3C%2FEM%3E%2C%20citing%20%22not%20enough%20memory.%26nbsp%3B%20Restarting%20the%20computer%20has%20always%20made%20such%20memory%20error%20messages%20disappear.%3C%2FP%3E%3CP%3E--%3C%2FP%3E%3CP%3ETim%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1873213%22%20slang%3D%22en-US%22%3ERe%3A%20Filtered%20listbox%20with%20selected%20item%20to%20open%20record.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1873213%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F864080%22%20target%3D%22_blank%22%3E%40Tim_Marshall%3C%2FA%3E%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EAppreciate%20the%20response.%26nbsp%3B%20I%20don't%20think%20it%20is%20my%20computer%20given%20I%20have%20done%20this%20on%20two%20entirely%20different%20PCs.%26nbsp%3B%20I%20have%20repeated%20this%20error%20using%20the%20same%20coding%20but%20written%20in%20separate%20db%20files%20individually.%26nbsp%3B%20Negates%20a%20file%20corruption%20question.%20%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EWhen%20I%20close%20the%20form%20I%20can%20use%20the%26nbsp%3B%3CSPAN%3EOnDblClick%20event%3C%2FSPAN%3E%20on%20the%20listbox%20as%20intended%20to%20pass%20through%20the%20selected%20item%20to%20the%20WHERE%20clause%20of%20the%20SQL%20statement%20in%20the%20other%20form.%20%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EI%20get%20this%20problem%20when%20I%20apply%20a%20filter%20to%20the%20listbox%20based%20on%20the%20TextBox.Text%20on%20the%20OnChange%20event.%26nbsp%3B%20Again%2C%20it%20filters%20the%20items%20perfectly.%26nbsp%3B%20But%2C%20once%20I%20filter%20it%20I%20cannot%20copy%20the%20contents%20of%20what%20I%20select%2C%20though%20I%20can%20select%20any%20item%20on%20that%20list%20as%20normal.%26nbsp%3B%20It%20just%20does%20not%20update%20the%20object%20properties%20it%20seems.%26nbsp%3B%20Its%20blank.%26nbsp%3B%20the%20ListBox.Value%20or%20.Column()%20is%20empty%20no-matter%20what.%20%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I have a listbox I have doing two things.  It filters criteria based on a Textbox.Text with an OnChange event.  Code works beautifully individually!  Plenty of tutorials using a WHERE in an SQL statement for the listbox source.

In this same Listbox I use a OnDblClick event to open a form and filter for the selected item using the Listbox.Column() property to forward the value to WHERE criteria of the opened form.  Code also works beautifully individually.

Here is my problem, say I do a search in the textbox and this filters the records of the listbox.  For some odd reason I can no longer grab a value of the selected item in the listbox.  Further, if I right-click to copy the field it gives me a error telling me "there isn't enough memory" which is absurd thinking the data is a few bytes on a 16GB RAM memory system.

I'm scratching my head on this one for a couple of days.  I tried Listbox.Value even.....doesn't matter!  Again, the code works as desired individually, but I have no way of pulling data from the listbox once I apply a filter to its contents based on a form's textbox.

2 Replies

@AmedMesa Hello, Amed, just a quick thought on the memory message.  This may be obvious, but have you restarted your computer?  I have found in previous versions of Access and on O/S before Win 10 (which I am only starting to use these past couple of weeks) that sometimes my computer would get "tired" and require a reboot.  I've frequently encountered situations where MS Access would not even start, citing "not enough memory".  Restarting the computer has always made such memory error messages disappear.

--

Tim

@Tim_Marshall 

Appreciate the response.  I don't think it is my computer given I have done this on two entirely different PCs.  I have repeated this error using the same coding but written in separate db files individually.  Negates a file corruption question.  

When I close the form I can use the OnDblClick event on the listbox as intended to pass through the selected item to the WHERE clause of the SQL statement in the other form.  

I get this problem when I apply a filter to the listbox based on the TextBox.Text on the OnChange event.  Again, it filters the items perfectly.  But, once I filter it I cannot copy the contents of what I select, though I can select any item on that list as normal.  It just does not update the object properties it seems.  Its blank.  the ListBox.Value or .Column() is empty no-matter what.