Front end help

[hrenee]

I have a query and it works great, and I can create a report from it. BUT it isn't user friendly. At this point, I go in change the query to the terms I want (there are different areas or Departments I want to run the report on) then run a report off it, make the report pretty and turn it in. The problem is I'm a temp, and I have no clue if the next person will know how to do that and what I don't know how to do is make it so it's an easy report.



Here's the code for the query:

SELECT tFMV.DEPT, tFMV.MainID, tFMV.AssetID, tFMV.DESCRIPTION, tFMV.Pics, Nz(tL.[Asset Description],"") AS [Asset Description]
FROM tblFixedAssetFMV AS tFMV LEFT JOIN tblFixedAssetListing AS tL ON tFMV.AssetID = tL.AssetID
WHERE (((tFMV.AREA)="CAN" Or (tFMV.AREA)="CAWH")) OR (((tFMV.DEPT)="NCK") AND ((tFMV.AREA)="PEA"))
ORDER BY tFMV.DEPT, tFMV.DESCRIPTION;


In this case I was looking for the area Candy or Candy Warehouse, but you may just as well want to look at bakery or many other things.

What I want is there to be a box that comes up and says "Which area do you want?" then a list box of the area, and then I'd like it to ask which Department you want from that area, with the ability to check as many or as few as you'd like. There are two tables, one for Area and one for Department and they have a relationship.

I know how to make a list box, but I don't know how to execute what I'm trying to do. . .

You don't need to tell me how to do it, I'd like a link to some sort of class, or some idiots guide, so I can do it myself, but the help I've found online so far just doesn't go to this level, or is so far past me that I'm clueless.

Thank you.

[ItsMe]

Anything "User Friendly" is going to involve a form and VBA. You mentioned a listbox. This is probably the best way for the user to select multiple items and add the selection to a query's where clause.

The problem is being able to translate the user input from the listbox. You will need to create a variant that stores the multiselection from the listbox. This variant holds a string array. Then you need to do something like a "For Each" statement and loop through the array, concatenating each selection to a single string variable. Then you need to incorporate the single string variable into an SQL SELECT query.

With your new SQL you can use a docmd to print, view, or whatever the report. Easy stuff.

[hrenee]

yeah that's what my husband said. . . I don't know VBA at all, so I guess I need to find a class online to take. I was hoping I'd missed something somewhere.

I did find this link in another thread: http://www.baldyweb.com/multiselect.htm

Once I have the front end built, that should work for my list box right?

[ItsMe]

I didn't scrutinize each letter of code in the example, but yeah. That is the idea.

Ultimately, you need to create a where clause. This where clause could contain one or it could contain many conditions. Because you need to have a plan to contend with many conditions, you need to concatenate these conditions into a single string.

If you could always be at the keyboard and have the user dictate to you what the conditions are, you could type the conditions directly into the string. You could type across, in a single line, in a way the where clause can understand it.

If you are not there, you need something like a listbox for the user input. The list box is going to create an array. It will list out the conditions downward, in many rows, in an array. The where clause does not know how to handle this. Simply placing a listbox there will not suffice. You need to add code to take the array and "type" it our "across".

If you practice with a listbox and the help files in VBA editor, you can debug a multiselection. It should display the results in the VBA immediate window in a list. If you copy and paste this result into your where clause, you will see that it does not fit. It creates multiple lines of code. You need to concatenate the variant (listbox result) that is the string array into a single string.

[hrenee]

Ahhh okay. I get it (I think). At this point I am always the one who runs the report so it's no issue. But when I'm done compiling all the data, I'm out of a job, and I'm pretty sure there isn't anyone here who can actually pull the reports. Short of writing each one up individually ahead of time so they just have to run them, this was my best solution.

[Dal Jeanis]

I really like your professionalism. It takes real ethics to intentionally make yourself dispensible.

I'm sure I've posted this kind of VBA on the forum before, so I'll find it and post a link to it here.

Let me get out me spyglass and scan the horizons for the bloody beast...

Aaargh... Thar she blows. https://www.accessforums.net/queries...ria-36143.html

Starting at post 6 or so.

[hrenee]

Dal - Thank you so much! That's perfect. Now to teach myself some VBA.