Advice for custom find/filter

[Toxalot]

It's been a while since I worked on this project or with vba so I'm
feeling rusty and am having trouble knowing where to start.

This project has no bound forms. It's all done with vba. I want a
popup find/filter form with the following features.

- a list of all company names (5000+) and this should look like a list
box not a combo box
- a text box where user can start typing a company name and it will
scroll the list to match
- a search box that will allow the user to search specific fields for
keywords which will limit the "list box" to only those companies that
match the criteria
- a filter feature that will allow the user to select specific fields
to filter on specific values which will limit the "list box" to only
those companies that match the criteria
- the ability to return to the main form, that shows all company
details, and filter it based on the same criteria as the popup form

I guess my first decision is what to use for the 5000+ list of company
names. We want it to look like a scrollable list not a combo box, but
I'm wondering if maybe I should use a subform?

I will add the find/filter options after. First, I want to get the
text box to scroll the list a highlight the first match.

Any suggestions/pointers?

Thanks
Jennifer

 

[Tom Wickerath]

Hi Jennifer,

I'm thinking you might want to try a subform instead of a listbox. I have
two fairly simple samples available here, that you are welcome to look at:

http://home.comcast.net/~tutorme2/samples/elements.zip
http://home.comcast.net/~tutorme2/samples/Chap08QBF.zip

The first one uses a multi-select listbox, to allow the user to select one
or more elements (either continuous selection, with the <Shift> key, or
discontinuous selections, using the <Ctrl> [control] key). The results are
displayed in a subform.

The second example is a modified version of the QBF (Query by Form) example
from Scott Barker's Access 2000 Power Programming book. This includes a
listbox control similar to the elements sample, check boxes (not so useful,
IMHO), and a text box to enter a date. My modifications include allowing the
user to double-click a record in the subform to open it for editing, adding
Access MVP Allen Browne's calendar control to assist in picking a date, and
adding the Export to Excel functionality.

I have more extensive examples that I'd be willing to send to you as well.
If you are interested, send me a private e-mail message with a valid
reply-to address. Please use the same subject as this posting. My e-mail
address is available at the bottom of the contributor's page indicated
below. Please do not post your e-mail address (or mine) to a newsgroup
reply. Doing so will only attract the unwanted attention of spammers.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html

_________________________________________


It's been a while since I worked on this project or with vba so I'm
feeling rusty and am having trouble knowing where to start.

This project has no bound forms. It's all done with vba. I want a
popup find/filter form with the following features.

- a list of all company names (5000+) and this should look like a list
box not a combo box
- a text box where user can start typing a company name and it will
scroll the list to match
- a search box that will allow the user to search specific fields for
keywords which will limit the "list box" to only those companies that
match the criteria
- a filter feature that will allow the user to select specific fields
to filter on specific values which will limit the "list box" to only
those companies that match the criteria
- the ability to return to the main form, that shows all company
details, and filter it based on the same criteria as the popup form

I guess my first decision is what to use for the 5000+ list of company
names. We want it to look like a scrollable list not a combo box, but
I'm wondering if maybe I should use a subform?

I will add the find/filter options after. First, I want to get the
text box to scroll the list a highlight the first match.

Any suggestions/pointers?

Thanks
Jennifer

 

[Toxalot]

This giving me ideas and getting my brain thinking on the right track.
Thanks.

One question at the moment (I'm sure I'll have more later)... Is there
a way to highlight a whole record in datasheet view? I can move to the
record I want and the first field is highlighted, but it would be nice
if the whole row was highlighted.

Jennifer

Hi Jennifer,

I'm thinking you might want to try a subform instead of a listbox. I have
two fairly simple samples available here, that you are welcome to look at:

http://home.comcast.net/~tutorme2/samples/elements.zip
http://home.comcast.net/~tutorme2/samples/Chap08QBF.zip

The first one uses a multi-select listbox, to allow the user to select one
or more elements (either continuous selection, with the <Shift> key, or
discontinuous selections, using the <Ctrl> [control] key). The results are
displayed in a subform.

The second example is a modified version of the QBF (Query by Form) example
from Scott Barker's Access 2000 Power Programming book. This includes a
listbox control similar to the elements sample, check boxes (not so useful,
IMHO), and a text box to enter a date. My modifications include allowing the
user to double-click a record in the subform to open it for editing, adding
Access MVP Allen Browne's calendar control to assist in picking a date, and
adding the Export to Excel functionality.

I have more extensive examples that I'd be willing to send to you as well.
If you are interested, send me a private e-mail message with a valid
reply-to address. Please use the same subject as this posting. My e-mail
address is available at the bottom of the contributor's page indicated
below. Please do not post your e-mail address (or mine) to a newsgroup
reply. Doing so will only attract the unwanted attention of spammers.

Tom Wickerath
Microsoft Access MVPhttps://mvp.support.microsoft.com/profile/Tomhttp://www.access.qbuilt.com/html/expert_contributors.html

_________________________________________



It's been a while since I worked on this project or with vba so I'm
feeling rusty and am having trouble knowing where to start.

This project has no bound forms. It's all done with vba. I want a
popup find/filter form with the following features.

- a list of all company names (5000+) and this should look like a list
box not a combo box
- a text box where user can start typing a company name and it will
scroll the list to match
- a search box that will allow the user to search specific fields for
keywords which will limit the "list box" to only those companies that
match the criteria
- a filter feature that will allow the user to select specific fields
to filter on specific values which will limit the "list box" to only
those companies that match the criteria
- the ability to return to the main form, that shows all company
details, and filter it based on the same criteria as the popup form

I guess my first decision is what to use for the 5000+ list of company
names. We want it to look like a scrollable list not a combo box, but
I'm wondering if maybe I should use a subform?

I will add the find/filter options after. First, I want to get the
text box to scroll the list a highlight the first match.

Any suggestions/pointers?

Thanks
Jennifer

 

[Tom Wickerath]

Hi Jennifer,

I've never tried doing this, but you might try changing the datasheet to a
continuous form. You can make a continuous form look pretty much like a
datasheet. Then try something similar to the ColorCon.zip (from Kevin Gray)
sample, found here: http://www.mvps.org/access/forms/frm0024.htm

I would appreciate any feedback, to see if you can get this working okay.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Toxalot]

This is pretty cool. I like the sorting ability as well. I will be
playing with this for a while.

Thanks.

Jennifer