Solved Filter a List Box (1 Viewer)

wmix

Registered User.
Local time
Today, 11:16
Joined
Mar 16, 2017
Messages
31
Hello Everyone,
I have an unbound form that pulls up a list of vending machines. The user scrolls through the list of vending machines, or searches using a search box (search as you type), finds the machine(s) they want and then clicks on the button for Run Report. It took a while but I finally have this form working properly.

Because a user cannot look at a machine name and determine if a machine is active or inactive, I want to filter the vending machines in the list box. My thought was that on form load only active vending machines would populate in the list box. Then, if they user needs to see the entire list of machines, they would request to see "all machines" (active and inactive).

Within the query that is populating the form's list box, qryProductSearch, I have a field called InactiveDate. When I manually add "Is Null" to the Criteria only active vending machines are show and when I leave the criteria empty it shows all the machines.

I have tried to use a check box and pass "Is Null" on form load to the query and that's not working. Then I tried to figure out a way to use a combo box with Active and Inactive status and that was a disaster. I have searched Google and the forum and I'm just missing something. I cannot wrap my head around the best way to complete the task at hand. Any help would greatly appreciated because at this point in time I'm drawing a complete blank. Thank you!
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 12:16
Joined
May 21, 2018
Messages
8,463
Make the two queries or three queries. All, Active, and maybe Inactive. Then I would have an option group to pick a choice. On the after update of the option group set the rowsource of the listbox to the correct query.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:16
Joined
May 7, 2009
Messages
19,169
instead of using a Listbox, you can use a SubForm (in datasheet view) of your table.
therefore you can filter, search, and see which machines are active/inactive.
 

wmix

Registered User.
Local time
Today, 11:16
Joined
Mar 16, 2017
Messages
31
instead of using a Listbox, you can use a SubForm (in datasheet view) of your table.
therefore you can filter, search, and see which machines are active/inactive.

Thank you, I'll have to look into that. I've not thought about it. Will it allow me to make selections of machines to run the report I need is the question. Sometimes I'm choosing one machine, but often it's several.
 

wmix

Registered User.
Local time
Today, 11:16
Joined
Mar 16, 2017
Messages
31
Make the two queries or three queries. All, Active, and maybe Inactive. Then I would have an option group to pick a choice. On the after update of the option group set the rowsource of the listbox to the correct query.

Thanks for your reply. I'm trying to normalize my database as much as possible and avoid redundancies, so I was hoping to just filter the one query I have. I appreciate the feedback and will look into this option. I suppose it wouldn't be too difficult to rewrite the code I have to work this in.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 12:16
Joined
May 21, 2018
Messages
8,463
Normalization has nothing to do with stored queries. Normalization has to do with design of tables. FYI and stored queries take up no room.

Assume you had an option group or some way to pick the filter type.
Code:
Private sub OpFilter_AfterUpdate()
  dim strSql as string
  dim strWhere as string
  strSql = "Select * from someQueryOrTable"
  select case OpFilter
    case 1  ' Active
        strWhere = " WHERE inactiveDate is Null"
   case 2 ' inactive
       strWhere = " WHERE not inactiveDate is Null"
   case 3 ' all
end select
StrSql = strSql & strWhere
me.somelistbox.rowsource = strSql
end sub
 

bastanu

AWF VIP
Local time
Today, 09:16
Joined
Apr 13, 2010
Messages
1,401
In your original query (listbox rowsource) use something like this in the InactiveDate criteria row: IIF(Forms!YourForm!CheckBox=True,Like "*" Or Is Null, Null). It assumes your checkbox label says "Show All".

Cheers,
Vlad
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:16
Joined
May 7, 2009
Messages
19,169
sample using subform to select machines to be included in report.
 

Attachments

  • sampleMachine.zip
    36.6 KB · Views: 135

Users who are viewing this thread

Top Bottom