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]

I would try add this line prior to the OpenReport line:

   Debug.Print strWhere

This allows you to view the where clause. Do you have text fields named Team and Location in the report's record source?


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]

Does the debug.print go in the code for the command ok or in the actual report code for the on open event?
I have it like this and nothing happened when I ran the report:

Private Sub cmdOK_Click()

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

Record Source rptStatusReports is a query:
Source Query for Project Status.
The only criteria in that query is the one that grabs the StartDate and EndDate from the form.
This code should handle 2 listboxes on 1 form right?
Thanks again!

 

[dhookom]

The code should handle more than one list box. Press Ctrl+G to view the results of the Debug.Print. See if this matches your expectation. Maybe reply back with the results of the debug.print.

You didn't answer my questions about the fields in the report.

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]

In the actual report, there are textboxes called SubDepartment(which is the "team") and there is a Location field as well.
The source query for the report contains the same 2 fields as well, SubDepartment and Location.
Something looks funny in this debug.print statement.
These are the last few different trys I did:

 1=1  AND Team In ('Network')  AND Location In ('Montreal') 
 1=1  AND Team In ('Project Management')  AND Location In ('Omaha') 
 1=1  AND Team In ('Network')  AND Location In ('Houston')  1=1  AND Team In ('DCO')  AND Location In ('Omaha')

That look right to you?
My last selection was just DCO and in Omaha, don't know why all the other stuff is connected behind it.

 

[dhookom]

Apparently you don't have a [Team] field in your report's record source. You might need to change the listbox name to:
lboTsubDepartment

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 just did that and it looks like its working, but the sub report isn't following suit. The sub report just wants to display every SubDepartment and not just the ones based off of the query results.

 

[jeffshex]

Is there any way for the subreports query to grab the information of which SubDepartments to show for the criteria field?
Is it as easy as the [Forms]![formname].... way?
Or since it is done with code do I have to do something else?

 

[dhookom]

It looks like you have only selected a single subdepartment so you could possibly use a reference to the control on the form in the subreport's record source. Another option is to set the link master/child properties to the field.

I sometimes change the SQL property of the saved query that is the subreport's record source.

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]

Rock on Duane!
I set the master child to
SubDepartment = SubDepartment
Location = Location

I think it all works now!
Keep the fingers crossed it works in all ways I need it to.

-Jeff

 

[jeffshex]

Am I able to call those strings so they show up on the report header? I had it so it worked with the drop down list box selection, but this is a different method using code for a multiselect listbox.
Just so they know what SubDepartment(s) and what Location(s).

 

[dhookom]

Try add a text box with a control source of:
=Trim(Mid([Filter],9))

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]

says it's an invalid expression

 

[jeffshex]

I think that the Master/Child for a subreport is not working correctly with the listboxes. For example lets say that that I set the Master/Child to SubDepartment. When I run the report I select just "network" and 1 location. The subreport is giving me all 4 locations.
If I add to the Master/Child field to do SubDepartment and Location it will show me "network" and my 1 location selection, but if I select "network" and multiple locations, it only shows results for 1 location.
Should I try a different method other than the Master/Child?

 

[dhookom]

I sometimes change the SQL property of the saved query that is the subreport's record source. This is done prior to opening any report and involves DAO code like:

CurrentDb.QueryDefs("qselForSubReport").SQL = "SELECT..."

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]

Let me make sure I understand you. Are you changing that every time before you run the report? If so, chances are I'm not going to be the one running the report and the people who are will not know SQL if they have to change it.
Do you think I should look into a way a listbox sends the string to a hidden textbox and use that as the criteria for the query?
I'm stumped now.

 

[dhookom]

Nobody has to open any object to change design. This is done with code like I suggested.

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 where would I go to insert that beginning code?
Open the subreport and then under the forms properties?
Open up the subreports query and change the SQL itself?

Just need a little help as to exatcly where and how to get to where the SQL property is.

Thanks!

 

[dhookom]

Assuming the subrpeport has a saved query as its record source, you would use code like I mentioned prior to opening the report. I assume you have a form with a command button control that would open the report. The code could be added to the button code prior to the DoCmd.OpenReport line.

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]

Yes I do have a command button.
Under the sub reports properties under record source is this:
Line Item Hours for rptStatusReports
Is that considered a "saved query"?

If so how would that be worked in to:

CurrentDb.QueryDefs("qselForSubReport").SQL = "SELECT..."

Would I have to go into the query under SQL view and copy that over into the code so it looks like this:

CurrentDb.QueryDefs("qselForSubReport").SQL = SELECT DISTINCTROW [Source Query for Project Status].SubDepartment, [Source Query for Project Status].Name, [Source Query for Project Status].Date, Sum([Source Query for Project Status].SickHours) AS [Sum Of SickHours], Sum([Source Query for Project Status].VacationHours) AS [Sum Of VacationHours], Sum([Source Query for Project Status].HolidayHours) AS [Sum Of HolidayHours], Sum([Source Query for Project Status].ProjectHours) AS [Sum Of ProjectHours], Sum([Source Query for Project Status].TechHours) AS [Sum Of TechHours], Sum([Source Query for Project Status].AdminHours) AS [Sum Of AdminHours], Sum([Source Query for Project Status].AfterHoursMF) AS [Sum Of AfterHoursMF], Sum([Source Query for Project Status].AfterHoursSat) AS [Sum Of AfterHoursSat], Sum([Source Query for Project Status].AfterHoursSun) AS [Sum Of AfterHoursSun]
FROM [Source Query for Project Status]
GROUP BY [Source Query for Project Status].SubDepartment, [Source Query for Project Status].Name, [Source Query for Project Status].Date;

That just seems odd to be inserted into the on click:

Private Sub cmdOK_Click()

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