Multiple multi select list boxes to open report filtered to chosen data

[moke123]

Between the Ukes and Guitars I'm surprised you have any spare time.

We gonna get a build thread like NG's uke?

[Micron]

I'd say I'm too far in to go to that level of detail. However, I can muster up something if you get enough votes but I don't know if this forum tolerates a lot of pics in a thread like AWF does.

[Gina Maylone]

Any fellow insomniacs out there? Referring to the parameter form and reports above, my user needs to "recall last settings". At first I was appending the selections to another table and changing the recordsource of the report to the "ReportParamTable" and grabbing the last row. In this new setup, I don't know how to get it done. Any thoughts from my heroes?
TIA!!

[Micron]

What does that mean? I change a control value or listbox selection then want to revert back to what it was? Or it means go back to what I ran yesterday?
Something else?

[kd2017]

The easiest option might be to just save the generated where clause in a separate table, maybe along with date, user name, brief description, etc. And give the user the option to run "Saved Reports"

[Gina Maylone]

Micron,

For example, he runs a report based on the info he wants and moves on for the day. The next time he uses it he wants the same report as earlier but can't remember exactly what he asked for. I THINK. Or he chooses a different report and wants to apply the same filter as earlier.

Thanks.

[kd2017]

The easiest option might be to just save the generated where clause in a separate table, maybe along with date, user name, brief description, etc. And give the user the option to run "Saved Reports"

And if you *really needed to* "load" previous selections into the form's controls you could use some pretty simple regex to parse the saved where clause.

-Or- you could save save all the selections individually in a table. The table could have a field for control name, and a field for the value, just loop through them all and set the controls.

Do you need to save multiple versions of your selections or just the previous one?

[Micron]

Micron,
For example, he runs a report based on the info he wants and moves on for the day. The next time he uses it he wants the same report as earlier but can't remember exactly what he asked for. I THINK. Or he chooses a different report and wants to apply the same filter as earlier.
Thanks.

Then I'd say you need to store the complete search criteria. If you don't use individual fields (as opposed to mushing it all into one) then you know someone will ask for that. At first glance, would you not add date and user ID fields in the ReportParamTable and if there is something that makes the report unique (e.g. project number) ensure the param table can be linked back to the appropriate table field? Then a user could pop up a modal form that allows them to search on report param table to filter the param list by what you've added to that table (or perhaps also already have in it). Then they could load your param form with those parameters. Not sure you were planning to keep the individual parameter textboxes (where you build the initial report criteria). If not, you'd need them in the modal retrieval form. Once chosen, a user is likely going to ask that they be allowed to tweak fields and save that as a new search so I'd say you're looking at using unbound controls in one way or another.

[Micron]

This might give you a clue, but it's a bit of a hack because I made as much use of what you had that I could. However, I had to modify the queries behind the process and then delete a bunch of reports to get the zip file down to size. Modifying the queries also made it necessary to alter form field control sources.
sampledb2.zip

Notes:
I added id, userid and date fields to parameters table so that you can link the subform fields to a person and date and these would need to be required in order to provide the functionality. I had to update them to some value but you will see that only one has a userid value other than 0, which matches the user specified on your login form (matches based on the arbitrary userID value that I gave it). However, I only went as far as manually inputting to the table and didn't worry about dates. You'll have to work on getting the real values into the params table and eliminate the date part if not desired.

- you seem to have a lot of similar objects (reports, forms, etc) and should try to re-use one whenever possible.
- your code will not compile
- you have two button click events for the login that are almost identical and one isn't being used. That and a ton of commented code makes it hard to follow what's going on
- your lack of use of PK fields in some tables will often make the task more difficult (e.g. Person table - what if you get 2 users named John Smith?) I added one there. Also, I'd never combine first and last name in one table field. It also made it necessary to use Max in your attempt to get what you're after from the report parameters.
- you depended on the Last function to retrieve data but no order is imposed on that set of records, making Last a dicey proposition. There is no guarantee that what is Last today will be Last tomorrow in an unordered set. Use of this function should be reserved for ordered sets.
- the lack of pk thing possibly made it necessary to use stacked queries to get what you're after. You shouldn't need 3 queries to get one record for someone's last search criteria.

Well that's if for now...

[Gina Maylone]

Thanks so much! I am adding the selected criteria to a table, individual fields and recalling it that way. The only issue I have is if someone Selects All, then the string is too big to pass. While I could tell him not to expect it to save if select all is chosen, that's not going to fly well. If I pass the entire sql string, the same issue will apply, and of course an error will pop and confuse. I know, custom error message :-)

[kd2017]

The only issue I have is if someone Selects All, then the string is too big to pass.

What about a long text data type field?

[Gina Maylone]

PERFECT!!! Thanks for the suggestion, I always forget about that option! Have a fantastic day kd2017!!

[Micron]

You might find that you experience truncated fields if you try using criteria with memo/long text fields in searches. You should be OK if you stick to retrieving records based on something else, such as the userID.

EDIT: for future consideration - if one picks ALL why bother putting criteria in the parameter field or in your coded search string?

[Gina Maylone]

You might find that you experience truncated fields if you try using criteria with memo/long text fields in searches. You should be OK if you stick to retrieving records based on something else, such as the userID.

EDIT: for future consideration - if one picks ALL why bother putting criteria in the parameter field or in your coded search string?

Because they won't always select all, I actually hate allowing that option BUT...you know. ;-)

[Micron]

if one picks ALL...

I guess your way will be easier for you, but if you include the field in SELECT but not in WHERE then you get all?