Form Setup for an Idiot

[mediaman812]

I'm attempting to do something specific, but I'm mentally challenged.

Here's my objective: I have a database with several fields, some of which are value lists and some of which are open text fields.

In a perfect world, I would like the user to be able navigate to a form, where she would select values from combo boxes and add keywords into the open text fields. Once she'd selected all the criteria desired to select the appropriate records, she would click a command button that would run a report based on that criteria.

Now I realize that, with my tiny, little brain, creating some mythical boolean keyword search would be nigh-impossible. However, the combo box selection sounds like something I might actually be able to tackle.

Would someone be able to suggest something simple enough for me to actually understand?

The value list fields I would want to search on would be the following:

[tblUserType].[txtUserType]
Value List:
Statistician;Data Miner;Light User;Professional;Report Reader;Buyer;IT
[tblProductFamily].[txtProductType]


Additionally, it'd be great to be able to select a record from another table (tblProduct), but I'd need to show the user two of the three columns (the first column is the primary key).

 

[John Big Booty]

Have a look at this search tool, it might help.

 

[suepowell]

HI,

I've done this several times and would be happy to post an example form. What version of access are you using? and i'll put something together.

Sue

 

[suepowell]

Hi I've assume you are using 2002 or later so here is the example.

The button to produce the reports does not work as I have not included all the tables and queries, but the form does all you seem to want so have a look at the code the see how the report is called.

This also handles leaving some of the boxes blank which can be confusing for new users, it certainly confused me for quite a while!

Hope this helps

Sue

 

[mediaman812]

Hi I've assume you are using 2002 or later so here is the example.

The button to produce the reports does not work as I have not included all the tables and queries, but the form does all you seem to want so have a look at the code the see how the report is called.

This also handles leaving some of the boxes blank which can be confusing for new users, it certainly confused me for quite a while!

Hope this helps

Sue

Thanks!

Wow. That's very nice.

There are a couple of items that don't quite fit, and leave me scratching my sore head.

1. Would I be able to use a Value List as a data source instead of a Table/Query in the form? I'd like to be able to filter by a list of values that mirrors the list in the ultimate table.

2. How can I filter based on this form? It doesn't look like an easy fit.

3. Is it possible to do this without getting into the code? I've never done VB coding.

 

[mediaman812]

Oh, and sorry. I'm using Access 2003.

 

[mediaman812]

Have a look at this search tool, it might help.

Thanks, JBB. It is, indeed a cool search tool.

Unfortunately, though, I wouldn't have the slightest idea how to copy that code into my db. It looks a little daunting to this monkey boy.

Would there be an easy way of copying it into a form without having to edit the VB code?

 

[mediaman812]

Anyone have any more info?

 

[John Big Booty]

The only thing I can suggest is that you pull it apart piece by piece until you have worked out what's going on in the form. Then reconstruct it in your DB using the original as a template.

 

[John Big Booty]

I've just had another look at the Cool Search tool.

It should be pretty easy to adapt it to your own DB.

The easiest way would be to simply copy and past the Form Search in to your DB.

Then create a new Query called Query1, this query should collect all the data that you wish to search on along with the data you wish to show. In the criteria for each filed you wish to search put;

Like "*" & [Forms]![Search]![Search2] & "*"

this will need to be stepped down one line for each field so that it using an "Or" search, if they are all on the same line it will be an "And" search and the form probably won't work.

Next delete all the Fileds below the List Box QuickSearch, and drag in the ones that relate to your Query1. That should do it.

The code behind the form is really quite straight forward. There is an On Change event on the text box Search which simply passes the value in text box Search to the hidden text box Search2. Search2 is used as the criteria in your Query1.

There is an AfterUpdate event on the list Box QuickSearch, that moves the focus to the top of the list or gives a message box if there is no match for the search.

Then there is some code behind the Reset button to clear the text box Search, and requery the list box QuickSearch. However if you follows the steps I laid out above you shouldn't need to look at the code.

 

[suepowell]

Hi, sorry for the long time in replying'

In answer to your questions,

1.Yes you can use a value list instead of a table as a source, but if you want
to have the same list as an eventual table it seems to make sense to use that table rather than type a list again.

2. I do use this form to filter. If you look at the code on the button you will see there is a line to open a report (this could also be a form) with just the records you want included. Instead of setting a filter for a form or report you can actually modify the record source by using the values in the select form. This involves writing some sql. If I know what fields I want I used to create a query using the query creater, and then view and copy the sql into the code where I want it. Makes it easier to get it right when you are starting out.

3 No I don't think you can really do this without code, but it doesn't need to be too complicated.

I have had a look at the test database you posted.

The main problems seem to be to do with the record sources of the froms you are opening with the buttons on the main form.

I have corrected these as far as I can and the buttons all now work and select the right records for the other forms.

Have a look at the code and compare it to what you had, and I think this might help put you on the right road, you were very close with what you had, but those last bits are always the hardest.

One of the forms wouldn't open at all because of datasource errors on the form and the subform, so this is always a clue and needs to be fixed before you can go on to select the right records.

I hope this helps

Good luck.

Sue

 

[mediaman812]

Sorry for the long time replying on my end.

I've been trying to work my way through this and wrapping my head in knots. In case you're unaware of the phenomena...it hurts.

I opened the sample db you posted, Sue, but it didn't work. No report existed. So I created a report and it still didn't seem to work. I attempted to dissect the VB code and replace the associated fields, but I can't seem to get it to work. Either I get all records (i.e., no filtration occurred), or a report with no data. I've been trying to parse the coding out, but have been unsuccessful.

Not sure what next step to take.

 

[suepowell]

HI,

I just made a few tweeks to your posted database to get it to open the forms off the buttons correctly, there wasn't a report so I didn't create one.

If you post your version with the report I'll have a quick look and see if I can spot the problem.

Sue

 

[mediaman812]

HI,

I just made a few tweeks to your posted database to get it to open the forms off the buttons correctly, there wasn't a report so I didn't create one.

If you post your version with the report I'll have a quick look and see if I can spot the problem.

Sue

Okay. Here's a test version. The report is more for functionality's sake than anything else. Thanks!