Form with Datasheet Subform - Select Columns? 2

[misscrf]

I have a nifty Summaries and Report Menu for an application I developed. It has a subform that is tied to a query, and is invisible until they click the Run Summary button on the main form. The subform shows as a datasheet.

This is very useful to the end users, because they like to just take the grid and throw it in excel and email it.

The problem is that the report menu lets them choose from a bunch of different options to filter on, and based on that, they may not want certain fields in the results. The query is giving them all the fields, and in certain cases, that will repeat the records because of sub records. In those cases, we don't want those extra fields.

How would I go about making a listbox of the fields that are available (with nice names) and letting them choose 1 or more of those for their output? I would need the query to only run on those columns so that records would appropriately group based on the columns chosen at that time.

Any thoughts?

Thanks!

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[misscrf]

Thank you. I will try that. I have seen that before, but I always have trouble getting the immediate window to actually spit out the sql so I can see it.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

I doubt the window "spits". You can open and view it by pressing Ctrl+G.

Duane
Hook'D on Access
MS Access MVP

 

[misscrf]

haha. In my experience SQL always spits at me. I got it to work now. My issue at this point is that I need to trap where a user doesn't select any fields for output. In that instance, I need to make the

strFields = strFields & "[" & lbo.ItemData(itm) & "], "
 Next
 strFields = Left(strFields, Len(strFields) - 2)
 Else

see everything as selected. I had gotten it to select all fields, but it actually selects them in the listbox for what the end user sees and I would like the selection of all those to be transparent.

This is what I am at right now.

Private Sub cmdSummary_Click()
 Dim Mysql As String
 Dim strCriteria As String
 Dim strFields As String
 Dim strQuery As String
 Dim lbo As ListBox
 Set lbo = Me.lstFields
[COLOR=red yellow] If Me.lstFields.ListIndex = -1 Then
 For Each itm In lbo....(every item in the list, not every item selected bc none are selected)
 strFields = strFields & "[" & lbo.ItemData(itm) & "], "
 Next
 strFields = Left(strFields, Len(strFields) - 2)
 Else
 For Each itm In lbo.ItemsSelected
 strFields = strFields & "[" & lbo.ItemData(itm) & "], "
 Next
 strFields = Left(strFields, Len(strFields) - 2)
 End If [/color] 
 strCriteria = "1=1 "
 strCriteria = strCriteria & _
 BuildIn(Me.LstClient, "[Client]", "'")
 strCriteria = strCriteria & _
 BuildIn(Me.lstCarManufacturer, "[Car Manufacturer]", "'")
 strCriteria = strCriteria & _
 BuildIn(Me.lstCarModel, "[Car Model]", "'")
 Mysql = "SELECT  " & strFields & " FROM  frmSubqryClientCarsQry Where " & strCriteria & " Group By " & strFields
 Me![frmSubqryClientCarsQry].Form.RecordSource = Mysql
 Me.frmSubqryClientCarsQry.Visible = True
 End Sub

The part that I highlighted is the area I am still working out. What I would like to do is if the listindex is -1 or there are no fields selected for output, I need to have the strfields build all the fields. I tried making it a "*" but that won't work for the group by. Realized that one quick!

All in all, your solutions work except for this new issue, and I wish I could give you both bunches of stars for all your help!!

Thank you!!!!

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[misscrf]

nevermind. If you can even believe it, I found the answer!!!

Private Sub cmdSummary_Click()
 Dim Mysql As String
 Dim strCriteria As String
 Dim strFields As String
 Dim strQuery As String
 Dim lbo As ListBox
 Dim i As Long
 Set lbo = Me.lstFields
 If Me.lstFields.ListIndex = -1 Then
 For i = 0 To Me.lstFields.ListCount - 1
 Me.lstFields.Selected(i) = True
 Next
 For Each itm In lbo.ItemsSelected
 strFields = strFields & "[" & lbo.ItemData(itm) & "], "
 Next
 strFields = Left(strFields, Len(strFields) - 2)
 Else
 For Each itm In lbo.ItemsSelected
 strFields = strFields & "[" & lbo.ItemData(itm) & "], "
 Next
 strFields = Left(strFields, Len(strFields) - 2)
 End If 
 strCriteria = "1=1 "
 strCriteria = strCriteria & _
 BuildIn(Me.LstClient, "[Client]", "'")
 strCriteria = strCriteria & _
 BuildIn(Me.lstCarManufacturer, "[Car Manufacturer]", "'")
 strCriteria = strCriteria & _
 BuildIn(Me.lstCarModel, "[Car Model]", "'")
 Mysql = "SELECT  " & strFields & " FROM  frmSubqryClientCarsQry Where " & strCriteria & " Group By " & strFields
 Me![frmSubqryClientCarsQry].Form.RecordSource = Mysql
 Me.frmSubqryClientCarsQry.Visible = True
 End Sub

My next issue will be that when I select only some fields, I need to make those controls on the subform invisible. Or to say it another way, I need to set visibility of the subform controls based on if the field is chosen in that query.

I will put that in a new post if I should. Please let me know your thoughts on that. Again, thanks, thanks, thanks!!!!

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

If you used my previous code from 29 Mar 10 17:55, with the query as the subform, you would only display the columns that were selected.

Duane
Hook'D on Access
MS Access MVP

 

[misscrf]

Thank you, Duane. I am assuming you are referring to this:

strQuery = "MyQuery" ' any saved query
CurrentDb.QueryDefs(strQuery).SQL = strSQL
Me.sfrmResults.SourceObject = "QUERY." & strQuery

When I put that into my code, I end up with this:

Private Sub cmdSummary_Click()
 Dim Mysql As String
 Dim strCriteria As String
 Dim strFields As String
 Dim strQuery As String
 Dim lbo As ListBox
 Dim i As Long
 strQuery = "qryClientCarsQry" 
 Set lbo = Me.lstFields
 If Me.lstFields.ListIndex = -1 Then
 For i = 0 To Me.lstFields.ListCount - 1
 Me.lstFields.Selected(i) = True
 Next
 For Each itm In lbo.ItemsSelected
 strFields = strFields & "[" & lbo.ItemData(itm) & "], "
 Next
 strFields = Left(strFields, Len(strFields) - 2)
 Else
 For Each itm In lbo.ItemsSelected
 strFields = strFields & "[" & lbo.ItemData(itm) & "], "
 Next
 strFields = Left(strFields, Len(strFields) - 2)
 End If 
 strCriteria = "1=1 "
 strCriteria = strCriteria & _
 BuildIn(Me.LstClient, "[Client]", "'")
 strCriteria = strCriteria & _
 BuildIn(Me.lstCarManufacturer, "[Car Manufacturer]", "'")
 strCriteria = strCriteria & _
 BuildIn(Me.lstCarModel, "[Car Model]", "'")
 Mysql = "SELECT  " & strFields & " FROM  qryClientCarsQry Where " & strCriteria & " Group By " & strFields
 CurrentDb.QueryDefs(strQuery).SQL = mySQL
 Me.frmSubqryClientCarsQry.SourceObject = "QUERY." & strQuery
 Me.frmSubqryClientCarsQry.Visible = True
 End Sub

When I do this, Access crashes. Then when I open back up my query is now saved as Select (criteria chosen) from (itself) and it errors saying it is a circular reference. I've backed up the query, and I keep needing to replace it because of this.

When I do it this way:

....
 Mysql = "SELECT  " & strFields & " FROM  qryClientCarsQry Where " & strCriteria & " Group By " & strFields
 Me.frmSubqryClientCarsQry.SourceObject = "frmClientCarsQry"
 Me![frmSubStatusVenueQry].Form.RecordSource = Mysql
 Me.frmSubqryClientCarsQry.Visible = True
 End Sub

It doesn't crash and I do get results. The only problem is that I see all the columns and only the columns that I chose show data. The rest show up with #Name for all their values. I need to make those invisible.

I tried setting the record source of the form to the query with no controls on the query, but then no fields show up. With all the controls on the form, they all show up, even though the query is only pulling some of them.

I think I need a string to say if the field is selected in the listbox, then the corresponding control on the subform is visible. Then I can have them invisible on default.

Sound right?

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

You can't use

   '...
   strQuery = "[b][red]qryClientCarsQry[/red][/b]" 
   '...
   Mysql = "SELECT  " & strFields & " FROM  [b][red]qryClientCarsQry[/red][/b] Where " & strCriteria & " Group By " & strFields
   '...

These must be two different queries the second query is what I would consider my "base" query and the strQuery would be the fields and records selected from the base. So there needs to be at least two queries.

Your base query is the one that provides the field list to select from. A user might select only half of the fields available in the base query. The strQuery should be based on the "base" query but contain only the selected fields.

Duane
Hook'D on Access
MS Access MVP

 

[misscrf]

Thank you!!!!! That was my bad. lol. I didn't understand that needed to be 2 queries. You rock!!!!!!

[highlight]

!!!!!!!*****STAR*****!!!!!!!!!​

[/highlight]

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[misscrf]

ok, so I took this to another report summary form, and things got a bit more complicated. I don't have enough room for the subform on the main form, so I ended up just running the query and opening it for them to see. It will look like a pop-up form in DS view, and it works.

My issue is this - I have a Clear button on the main summary form that resets all listboxes and all fields to be empty, so the users can run a query and start over and do another.

I use this code in the clear onclick:

 With Me.lstFields
    For Each i In .ItemsSelected
         .Selected(i) = False
    Next i
End With

I have also used this:

 With Me.lstFields

        For Each varItm In .ItemsSelected
            .Selected(varItm) = False
        Next varItm

    End With

The code is on the onopen of the form and the onclick of the Clear button.

If I click some of the values in lstFields, the query runs as should. If I then click Clear and try to run with none clicked, I get an error Run-time error '5': Invalid procedure call or argument.

It goes to this line red line in the code below, on debug:

 Set lbo = Me.lstFields
 If Me.lstFields.ListIndex = -1 Then
 For i = 0 To Me.lstFields.ListCount - 1
 Me.lstFields.Selected(i) = True
 Next
 For Each itm In lbo.ItemsSelected
 strFields = strFields & "[" & lbo.ItemData(itm) & "], "
 Next
 strFields = Left(strFields, Len(strFields) - 2)
 Else
 For Each itm In lbo.ItemsSelected
 strFields = strFields & "[" & lbo.ItemData(itm) & "], "
 Next
 [COLOR=red]strFields = Left(strFields, Len(strFields) - 2)[/color]
 End If

I can see that this means it doesn't see the listIndex as -1, and it doesn't. If I close the form, open it and run the summary with no values selected in lstFields the summary brings all the fields like it should.

So how come the clear command code is not properly setting the lstFields listbox back to an index of -1 like it should? Nothing is selected?

Got anything for this one? ;-)


misscrf

It is never too late to become what you could have been ~ George Eliot

 

[misscrf]

Never mind. I found that if I use Me.lstFields.ItemsSelected.Count = 0
instead of
Me.lstFields.ListIndex = -1

the rest of my code works.

misscrf

It is never too late to become what you could have been ~ George Eliot