Multi Select Listbox 1

[jeffshex]

Duane,

I've been trying to get that listbox code you showed me to work. Maybe you might know where I'm goofing.
I pasted the code in a module and named it modMulitSelect, starting with the BuildIn line and ending with the End Function. My form has 2 listboxes and a start date and end date box as well. I named the listboxes lboTTeam and lboTLocation.
I put this as the commond Ok click:

Private Sub cmdOK_Click()

Dim strWhere As String
strWhere = " 1=1 "
strWhere = strWhere & BuildIn(Me.lboTTeam)
strWhere = strWhere & BuildIn(Me.lboTLocation)
DoCmd.OpenReport "rptStatusReports", acViewPreview, , strWhere
Me.Visible = False
End Sub

*I threw the me.visible in there to close the open form*

When I click ok on the form, it closes and brings up another dialog box asking for a parameter for Team. I looked and made sure there were no queries with that prompt. If I click ok on that box, it brings every team in the date range regardless of what team you selected or location.
Any clue as to what is going on?

Thanks - Jeff

 

[dhookom]

A saved query is a query that you can see in the query tab in your database window. I would create another simpler query to use as your subreport's record source
qselStatusSubRpt with a SQL view of:

SELECT * FROM [Line Item Hours for rptStatusReports]

Your code would then be something like

Dim strWhere As String
strWhere = " 1=1 "
strWhere = strWhere & BuildIn(Me.lboTSubDepartment)
Debug.Print strWhere
Currentdb.QueryDefs("qselStatusSubRpt").SQL ="SELECT * " & _
    "FROM [Line Item Hours for rptStatusReports] WHERE " & _
     strWhere
DoCmd.OpenReport "rptStatusReports", acViewPreview, , strWhere
Me.Visible = False

You may need to make some changes but this should give you the main idea to the solution.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jeffshex]

Any idea as to the runtime error "3265 Item not found in this collection?

 

[dhookom]

Did you create a query named qselStatusSubrpt? What is its sql view? Did you have a query named [Line tiem Hours for RptStatusReports]?

Which line causes the error?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jeffshex]

It highlights the whole line...and I'm guessing that its maybe because of the "qselStatusSubrpt". I don't have anything named that, might of got copied from the sample code you listed.

The main report "rptStatusReports" comes from a query called: "Source Query for Project Status"

I actually have 2 sub reports under the main:
"Line Item Hours for rptStatusReports" and
"Missing for rptStatusReports"
-----------------------------------------------------
If I put the query name for the report, "Source Query for Project Status", in the spot where qselStatusSubrpt is it comes up with a circular reference error.

What is supposed to go in the spot of qselStatusSubrpt?

 

[dhookom]

In an earlier post, I suggested you create a query qselStatusSubrpt. Apparently my suggestion wasn't strong enough language. Please re-read my posting at 21 Jul 05 16:38.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jeffshex]

Ok, so I created a query named qselStatusSubRpt and set it all up.
Now nothing is working and it's saying that there is a circular reference error. Saying the query can't depend on itself for information.

 

[dhookom]

What is the SQL view of qselStatusSubRpt?
What is the current code that you are using?


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jeffshex]

The SQL view for qselStatusSubRpt is what you told me to use:

SELECT * FROM [Line Item Hours for rptStatusReports]

The code for the command button was from that same post from you:

Currentdb.QueryDefs("qselStatusSubRpt").SQL ="SELECT * " & _
    "FROM [Line Item Hours for rptStatusReports] WHERE " & _
     strWhere

 

[dhookom]

After running this code, your SQL of qselStatusSubRpt should be changed. Find the query and view the sql and view the datasheet. Do you see what you expected? What happens if you run the subreport by itself?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jeffshex]

Ok, I ran it with 2 selections (Montreal and Houston).
The subreport just showed Houston.
The SQL view for qselStatusSubRpt did change like it was supposed to, and if I ran that by itself it did run both selections correctly.
Should I open up the sub report and change its record source under properties to get from the qselStatusSubRpt query?

(I did not get a circular reference error this time.)

 

[jeffshex]

I think I got it. I changed the record source of the Line Item subreport to look at the qselStatusSubRpt as it's source query.
I did have to take off the child/master relationships for it to all display correctly.

Now if I wanted to do this code for my other subreport, would i just do the same steps?
Make a special query, add another Currentdb line to my code, and point the subreports record source to the new saved query?

 

[dhookom]

I think you have a good understanding of how to implement this for other subreports. The code changes the sql of the subreport's record source prior to opening the report. This provides a great deal of flexibility in the filterinf of the subreport records.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jeffshex]

I set up the 2nd one, just like the first. I called it MisSelStatusSubRpt. The SQL view looks fine on it...but when I run just the query itself, it prompts for the start and end date like it is supposed to, but it then brings up a parameter box asking for SubDepartment even though they are allready in the SQL view.

SELECT *
FROM [Missing for rptStatusReports]
WHERE 1=1  AND SubDepartment In ('Network - Houston', 'Network - Montreal', 'Network - Omaha', 'Network - Stamford');

I'm assuming this is how I would insert it:

Private Sub cmdOK_Click()

Dim strWhere As String
strWhere = " 1=1 "
strWhere = strWhere & BuildIn(Me.lboTSubDepartment)
Debug.Print strWhere
CurrentDb.QueryDefs("qselStatusSubRpt").SQL = "SELECT * " & _
    "FROM [Line Item Hours for rptStatusReports] WHERE " & _
     strWhere
CurrentDb.QueryDefs("MisSelStatusSubRpt").SQL = "SELECT * " & _
    "FROM [Missing for rptStatusReports] WHERE " & _
     strWhere
DoCmd.OpenReport "rptStatusReports", acViewPreview, , strWhere
Me.Visible = False

End Sub

--------------------
I have no queries with a parameter box..which is why I have no idea where this is coming from.
This is an unmatched query though. Could that be why?

 

[dhookom]

Check your query "Missing for rptStatusReports" to see if there is a subdepartment field in it.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jeffshex]

I think I got it. The field name that the code generated and was looking for was called SubDepartment. The table it was coming from was named sub dept. So whenever I ran the report, it was forcing the the field [SubDepartment] and prompting it since there was no "SubDepartment" in there. I went in and changed the column name in that table to SubDepartment and it looks like we are in business.
I wish I knew just half of what you do!
Thank you again!!!!!!!

 

[dhookom]

Glad to hear you got this working.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jeffshex]

Duane,

I'm working on another form and am wondering if that same listbox code can do this:

The form has a text box and listbox.
The listbox is 2 columns (ProjectNumber and Description)

As they select listbox items, can it populate the textbox with the ProjectNumbers?
The query will just grab that text box for all the criteria.

**The only reason I'm using a textbox is because some people want to type in their project numbers**

Thanks again D!
-Jeff

 

[dhookom]

You should be able to take the concatenated project numbers and place the values into the text box. You should then be able to use the text box values to update the SQL property of a saved query.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jeffshex]

Where would I put that code in then so the user can see the textbox values before they click OK.

Or should I put in another button thats only function is to place the concatenated string in the textbox?

Your thoughts?

 

[dhookom]

Use the after update event of the list box to set the value of the text box.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]