Multi-select Listbox Filtering Report

D

DGS1979

Registered User.
Local time
Today, 03:49
Joined
Sep 15, 2011
Messages
14
Hi I am new to VBA so please bare with me..

I have a Form I am using for Filtering Records on a Report.

I am trying to figure out the code to make my Listbox a Multi-Select Listbox and have the code in the on click event of a command button to output to a report.

My Single-Column/String Listbox name is "lstStatus"

My Command Button Name is "cmdRunTaskReport"

My Report I am trying to Filter is "rptTaskByProject"

I have attached a simplified copy of my db

Any Help would be greatly appreciated.

Thanks,
Dave
 

Attachments

Here is a link that might help with the VBA code to accomplish what you want.
http://access.mvps.org/access/forms/frm0007.htm

The general concept is that you, of course, set the Multi Select property of your listbox to simple or extended (your choice). Then when you command button is clicked, your code must iterate through all of the items in the listbox, determining if the item is selected. If the item is selected, it must read the value of the selected item and create a string formatted in an appropriate manner that the string can be as part of the criteria of your query that produces the recordset you are needing.
 
I have updated your database with some code that might be of some help. It is attached.
 

Attachments

wow took me awhile but I finally found the "Thanks" button.
 
For some reason the code is stopping on the following line:

loqd.SQL = stSQL
 
If the code is just stopping and you are not getting any error message, make some change to the code then you can even undo your change, then from the Debug menu in the VBA code window, select the Compile Database option. Hopefully that will fix the problem.

If you are getting some error message, post the error message.
 
Opps my mistake. I had re-copied your db and forgot to save it so it was in "read only".. Ok I am back in action. Thanks again =)
 
There is a few Text-Boxes and Combo-Boxes that I have on this same filter form. I would like those controls to be optional so for instance if I do not put a value in the control it will simply return record sets based on the other criteria.

I hope I have explained this proper. Any suggestions for what my query criteria should look like?

Thanks,
Dave
 
This type of filtering would require that you add code to the area where the sql statement is being developed. You would need some "IF" statements in addition to the code that is already there. It is possible to have any number of possible filtering options but you will just have to code for each option and add the criteria to the "WHERE" part of the sql statement based on the filtering criteria provided.

Hope this helps.
 
First off please excuse my ignorance :confused:

Previously I had the optional criteria working in my db when I had the query built into my report. I have attached my old report with the built in query if you would like to view the sql statement.

I then cut the query sql statement out of the report and pasted it into a new stand-alone query (as we discussed). For some reason my option criteria no longer works this way when I try to filter the report with my form.

Any help would be greatly appreciated.
 

Attachments

The only thing I could find in your attachment was a report. I would help if you would provide the query, form and anything else that is relative to the issue.
 
yes the query is built into the reports "Record Source". If you click on the reports record source property, that is my old sql statement.
 
I understand, but first the tables must be available or the report and its record source will not be working. Also, if you are trying to use the criteria supplied from a form and you are using a query, then having the form and the query would help to trouble shoot the problems.
 
Ok here is my most current db. I have incorporated your added features into this one and broke the report query out of the report.

As you will see when you attemp run a report from the following Form: "frmCreateTaskReport", Access asks for any criteria not filled out on the form.

The previous db I posted here shows the way I had the report previously with built-in query sql that would not ask for missing criteria.

Also since I sent you my whole db this time please let me know if you have any other suggestions for me. This is my first db I have created so please bare with me haha.

Thanks,
Dave
 

Attachments

After looking at your database, I would first encourage you to revisit the design of the schema. Seeing fields like: TaskSub1, TaskSub2, TaskSub3, etc.or CheckerSub1, CheckerSub2, CheckerSub3, etc. or TaskSub1Desc, TaskSub2Desc, etc. and all of the other places where you have create this type of fields indicates that you do not have your database normalized. In all of these cases there should be at least one additional table if these values are only a one-to-man relationship. If it turns out that the relationship between the tasks and these other records are actually a many-to-many relationship, then there may be a need for an additional table to provide the this type of relationships. The same type of issue also exists in the "tblDrawingRegister" table.

I cannot say enough about how essential it is that you get the design/structure of the database correct before you attempt to create any of the other aspects of the database.

As for the report; First, the reason Access is stopping to ask for data is, you have managed to get controls linked to data fields that are not in the record source of your report (the query). If you want the data in the report, that data has to be in the record source.

Again, I strongly suggest you rethink your data structure.
 
Funny you should mention that because I was unsure how to deal with all those Task & Checker Subs.

Basically, I have a task that comes up, I will open the Form called: "frmCreateTask". Each task is assigned an Auto ID. I will give the task a Task Name, Description, Due Date etc. As part of this for I have Combo-Boxes where I can assign different employees to work on this task.

Example: One task may have a "Task Lead" and "Checker Lead" assigned where another task may have a "Task Lead" and "Checker Lead" as well as people assigned as "Task Subs" and Checker Subs".

Now at this point I agree my db is not normalized and I need to step away from my "Flat File" mentality. Would you suggest I create a separate table for "Task Lead", "Task Sub 1" and "Task Sub 2" etc. This would obviously result in about 15 additional tables.

Do you have any thoughts on this?

Thanks,
Dave
 
I really don't think you need to create a table for each of the Lead types, I think you would just create a table where you can identify the Lead Type and who the lead is. Think in terms of grouping your data by the types of data and not just one specific type of data. (hope that made sense). By defining your table in this manner, you can define many Leads (each with a different lead type) to each task.
 
Yesterday as discussed I broke the different leads out into a separate table called "tblTaskAssign". For the life of me I can not get this application to work properly now. I have attached the db

Now from "frmCreateTask" Towards the bottom of the form there is a sub-form where i would like to assign a Lead. I want to simply click the employee combo box. I would like to be able to choose an employee name and have it output the "EmployeeID" to table "tblTaskAssign". (This part seems simple enough)

Where it gets tricky is I would like it to only update [tblTaskAssign]![EmployeeID] for the record with the same [tskID] and where the [TaskDuty] = "Task Lead"

If there is not a record under [tblTaskAssign] with the current [tskID] and where the [TaskDuty] = "Task Lead" then I would like to create a record with these values when I assign an employee to Task Lead.

I can then paste this code into each of my other combo boxes and simply modify the "TaskDuty" criteria.

Any suggestions would be greatly appreciated.

Thanks,
Dave
 

Attachments

You must log in or register to reply here.

Similar threads

D
Solved Perils Referring To A Value In Listbox By Referring To Listbox Itself (Not .SelectedItems)?
Replies
6
Views
251
MajP
MajP
M
Refresh Listbox Query
Replies
6
Views
386
monika_V
M
A
Solved Call data to a list box depending on combo box list
Replies
10
Views
359
June7
June7
C
Majp Search and Filter
Replies
7
Views
281
MajP
MajP
G
Open Filter Dialog box
Replies
12
Views
632
LarryE
LarryE

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom