Search forms

[thewrightdesign]

I'm not sure what category this even falls under so putting it in general.

Some background:

I maintain a database of student records. Currently this is a Lotus Approach database but we are converting to Access 2003. (I work for the state, this is as far as we've gotten so far, sucks I know)

What I'm having trouble figuring out how to do is this: Right now when we do a search in Lotus, we do a ctrl+F and it brings up the full form that our database shows but devoid of data. We can then enter criteria in any number of empty input boxes and it will sort based on those criteria and return the results just in our normal form but with only the records showing that meet all those criteria. For example, if I want to know which students are currently going to a certain school or program, I can bring up the find, and enter the school name, the location if I need to, and the current status and it will bring back a report of all the students currently enrolled at that school, but it is in the same form that first shows up in the database. It also has the number or records it found down in the status bar. Plus we can click a tab at the top that says worksheet and get a spreadsheet type form with the information for just those records

I'm trying to find a way to do that in Access. So far all I can find is a single input box search that is in the search and replace dialogue box. Well this only lets me input one criteria, and it doesn't tell me how many records it found and I can't view directly in that form without closing out the search dialogue box. To see more records I have to click on next in the find dialogue box and if I close that box I've lost the search results entirely.

I hope this makes sense and that someone can help us find this answer so that we can continue to do our work efficiently. We are trying to eliminate the many steps it takes to filter down our searches and have it be able to report it to a worksheet view like it does in Lotus.

Thanks in advance for any help,
from a very frustrated state worker LOL

 

[boblarson]

The thing you're looking for is called FILTER BY FORM and you can just use this code in the bound form you want to use

    DoCmd.RunCommand acCmdFilterByForm

 

[John Big Booty]

This sample may give you something to think about.

 

[boblarson]

This sample may give you something to think about.

Quick question P - Have you ever used the Filter By Form? I hadn't up until recently and now I'm like kicking myself because it is a very simple and yet powerful search tool. It handles all of the criteria for different fields. Just wondered.

 

[John Big Booty]

Quick question P - Have you ever used the Filter By Form? I hadn't up until recently and now I'm like kicking myself because it is a very simple and yet powerful search tool. It handles all of the criteria for different fields. Just wondered.

No I've not, however that would seems to be a glowing recommendation, and I shall certainly investigate it at my soonest opportunity.

I am quiet enamoured with the dynamic search that I linked to and have implemented it in a couple of DB's, one of which I constantly use here at work. I like the way you can search on any part of multiple fields and watch as the results focus as you refine the criteria.

 

[John Big Booty]

Bob having used;

DoCmd.RunCommand acCmdFilterByForm

Do you then utilise the native menu bar that that command initiates, or are you using your own custom set of buttons or menu?

I can certainly see this being very much what the OP is looking for.

 

[boblarson]

Bob having used;

DoCmd.RunCommand acCmdFilterByForm

Do you then utilise the native menu bar that that command initiates, or are you using your own custom set of buttons or menu?

I can certainly see this being very much what the OP is looking for.

I normally use the native menus but you can create your own.

 

[John Big Booty]

Bob I've been having a bit of a play with the "Filter by form command", and come up with a strange anomaly.

Form1 is linked to a table that has seventeen odd thousand records, now when I fire the filter by form command the options it present are Is Null or Is Not Null. However with Form2 which is linked to a table will just under a thousand records the command presents a drop down list with all the available records.

Am I correct in assuming that the behaviour exhibited by Form1 is due to the number of records in it's Record Source? If so what is the break point for this change in behaviour?

 

[thewrightdesign]

The thing you're looking for is called FILTER BY FORM and you can just use this code in the bound form you want to use

Code:
DoCmd.RunCommand acCmdFilterByForm

__________________

Since you posted this I've been researching the filter by form (when time allows which is why my reply took so long) I'm not altogether sure where I would put the code you have here.

Is this going to get me a blank form that I can enter data into any of the input fields and it will sort using that data I've entered? Thanks again in advance for your help! This is a major function for us since we use this type of search many times a day so we'd really like to code that function into Access before we switch databases.

I played with the filter by form and the problem we have with it is that while I can enter multiple criteria, our main screen has two unbound boxes on it pulling information in from other forms. We need to be able to include these in that filter by form search like we do in Lotus Approach and it does not let us. Clearly I have to code something on the unbound boxes which contain sub forms in order to have it included correct? What would that be?

 

[boblarson]

I played with the filter by form and the problem we have with it is that while I can enter multiple criteria, our main screen has two unbound boxes on it pulling information in from other forms.

If you have that, you can't use filter by form and would have to create your own search functionality. The filter by form only works with bound controls.

 

[boblarson]

Bob I've been having a bit of a play with the "Filter by form command", and come up with a strange anomaly.

Form1 is linked to a table that has seventeen odd thousand records, now when I fire the filter by form command the options it present are Is Null or Is Not Null. However with Form2 which is linked to a table will just under a thousand records the command presents a drop down list with all the available records.

Am I correct in assuming that the behaviour exhibited by Form1 is due to the number of records in it's Record Source? If so what is the break point for this change in behaviour?

I'm sorry I didn't answer this back when it was posted. I must have overlooked that I had a response here. But since I haven't used it all that much, I can't tell you.

 

[thewrightdesign]

If you have that, you can't use filter by form and would have to create your own search functionality. The filter by form only works with bound controls.

Can you give me an idea then of how I would do that? I don't mind creating my own but need to know how to do it so that we can do a search on any of the three areas. Generally it won't be all at once, but would like to do a search using multiple criteria from the main screen on any of the forms/subforms like we can do in approach.

 

[boblarson]

Can you give me an idea then of how I would do that? I don't mind creating my own but need to know how to do it so that we can do a search on any of the three areas. Generally it won't be all at once, but would like to do a search using multiple criteria from the main screen on any of the forms/subforms like we can do in approach.

Check out a couple of samples from my website.


http://downloads.btabdevelopment.com/Samples/reports/Sample-GenericReport.zip

http://downloads.btabdevelopment.com/Samples/combos/FormSampleFromMultipleCriteria.zip

and while the samples focus on reports you can open forms the same way.

 

[Wiz47]

This sample may give you something to think about.

I use something very similar in a contacts form. I love the way it will narrow a search based on *any* criteria in the fields, dynamically. And like yours, a double click on the highlighted bar will bring up another form with the full information in that particular record.

 

[thewrightdesign]

Check out a couple of samples from my website.


and while the samples focus on reports you can open forms the same way.

Thank you for the samples. So if I'm understanding this correctly, I would be creating another form entirely in order to run my search off of any or all of the forms/subforms I want to search rather than actually searching within my existing form/subforms?

 

[boblarson]

Yes, that is correct.

 

[thewrightdesign]

So it will take twice the work to do in Access than it does in Approach. Seems a lot less efficient somehow. Thanks for your replies. It's starting to look as though there is not going to be a way to get Access to do what we do in Approach without taking a lot more steps not just in coding but from a user standpoint. That is really disappointing. You'd think as technology/programs improve, user experience should get easier not harder.

Thanks for your help.