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

 

[MajP]

Add a multiselect list box. Have two columns. The first column is hidden and stores the field name. The other stores the fields caption. Make sure to put captions in your tables so you have user readable names.

Private Sub Form_Load()
  Call populateList
End Sub

Public Sub populateList()
  On Error GoTo errLbl
  Dim rs As DAO.Recordset
  Dim fld As DAO.Field
  Dim cap As String
  Dim strList As String
  Set rs = Me.subFrmProducts.Form.Recordset
  Me.lstChoose.RowSourceType = "value list"
  
  For Each fld In rs.Fields
    Me.lstChoose.AddItem (fld.Name & ";" & fld.Properties("Caption").Value)
  Next fld
  Exit Sub
errLbl:
  If Err.Number = 3270 Then
    Me.lstChoose.AddItem (fld.Name & ";" & fld.Name)
    Resume Next
  Else
    MsgBox Err.Number & "  " & Err.Description
  End If
  
End Sub

Private Sub lstChoose_AfterUpdate()
  Call hideColumns
End Sub

Public Sub hideColumns()
  Dim lst As Access.ListBox
  Dim frm As Access.Form
  Dim itm As Variant
  Dim i As Integer
  Set frm = Me.subFrmProducts.Form
  Set lst = Me.lstChoose
  Call showColumns
  For Each itm In lst.ItemsSelected
    frm.Controls(lst.ItemData(itm)).ColumnHidden = -1
  Next itm
End Sub
Public Sub showColumns()
  Dim lst As Access.ListBox
  Dim frm As Access.Form
  Dim itm As Variant
  Dim i As Integer
  Set frm = Me.subFrmProducts.Form
  Set lst = Me.lstChoose
  For i = 0 To lst.ListCount - 1
    frm.Controls(lst.ItemData(i)).ColumnHidden = 0
  Next i
End Sub

This is pretty reuseable. So you could stick different queries as the source object of your subform and reload the list.

 

[MajP]

Here is a demo.

http://www.4shared.com/file/249155616/2b56879e/DataSheetUtilities_25Mar10.html

I used actual subforms in datasheet view as the source objects, but suprisingly it works with tables or queries as source objects.

 

[misscrf]

Thank you for this response.

I am looking at the code and trying to break it down. It looks like one of the columns in the source of your list box is "Caption".

Do I have that right?

I guess I am trying to understand how the populate list, hide columns and shows columns is meant to integrate with the 2 forms that I am using.

I have 1 form that has a variety of list boxes, combo boxes and text boxes for filtering on 1 or more field.

Would I put this listbox for choosing the fields on here too?

I then have another form that is on the first 1 as a subform. It is invisible until the command button is pushed on the main form to run the filter and show the datasheet results.

How do I use the listbox that your code manages, to tie into the query that is the source of my subform?

Thanks again!

misscrf

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

 

[MajP]

Did you look at the Demo? I think it shows all of what you are asking.

1)It looks like one of the columns in the source of your list box is "Caption".

When you build a table you can give each field a Caption. So a field like
"dtmDueDate" could have a caption "Due Date", and that is what the user sees in table and data sheet view.. When I load the listbox the user sees
"Due Date" but the bound field is "dtmDueDate" the actual name of the field.

2) I have 1 form that has a variety of list boxes, combo boxes and text boxes for filtering on 1 or more field.
Would I put this listbox for choosing the fields on here too?
Look at the demo. You would put the list on the mainform and the datasheet is the subform. That is how you described it in your original post.

3) How do I use the listbox that your code manages, to tie into the query that is the source of my subform

Not sure what part of this you do not understand. Clearly the list populates based on the source of the query.
I am proposing doing this in the reverse order. Instead of picking the fields and then running the query, you run the query and then pick which fields to show or hide.

 

[misscrf]

Thank you. I couldn't go to the site right away because it was blocked in my domain. I got the demo. I guess I need to look closer to understand how the listbox is interacting with the fields on the subform, but that is just me needing to figure it out. Another question is that when a user hides a column, will the filter requery? What I mean is that if they hide 1 column, it may need to regroup the data. The reason we need to do this is because they can run many types of reports on this menu. One summary may be useful to show a certain date field, but in another summary may just show duplicate information for all those dates, when the date is not important, and we would rather group on the othe fields.

If I have a group by in the query, will this regroup info?

Thanks for taking the time to help me. you don't have to answer that if you don't want. I will keep looking at your demo and try to figure it out.

I am just not very advanced at VB for some of this, and I am not seeing where the listbox code is connected to the subform. I'll keep looking.

misscrf

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

 

[MajP]

No this code just hides/shows a column. It will not affect the grouping or filter.

In the demo I showed that you can change the query and the source object.

You could get very complicated and build your query dynamically. I would think the simpler approach is to have listbox, or combobox. From there you choose which query to load.
example:
Pick Grouping
No Grouping
Employee ID
Employee ID, Region
Employee ID, Region, Date
Region
Region, Date
Date

Then just load the selected query.
How many different groupings are possible? A handful or 100s? If it is a reasonable number this would be easier than trying to build a dynamic query. That can be done but you will need to provide a lot of details on you base query, and the different types of groups and filters.

 

[misscrf]

ok, so it sounds like this just got more complicated. See I open the summary main form and the subform is invisible. One of my pains is that it runs the query anyway, and the main form drags because it is running that query even before the user has chosen to run anything.

I know that is a separate issue, but thought I would mention it.

Anyway, once they choose any of the filters (or none of them) and click to get the summary, I need them to be able to choose which fields to show, and have it group on all of those. Right now the query with all fields (which at least 1 instance would require all of them) os 18 fields. The hard part here is that I am building something independent because they don't want to have to have me build out every scenario. They want it independent.

I don't know what all the logical scenarios are. I have to figure any combination is possible.

misscrf

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

 

[dhookom]

I haven't digested this entire thread but you might be making this way more difficult than necessary. Assuming you have a multiselect list box with field names. You have code that loops through the list box to build a "SELECT fieldA, FieldB, FieldC,..." and possibly other controls to create a where condition and Order By. You end up with a full SQL statement contained in a variable like

   strSQL = strSelect & strFrom & strWhere & strOrderBy

You can then set the SQL property of a saved query to the strSQL

    Currentdb.QueryDefs("MyQuery").SQL = strSQL

Then set the subform control's Source Object to the query name:

    Me.MySubfrmControl.SourceObject = "QUERY.MyQuery"

Duane
Hook'D on Access
MS Access MVP

 

[misscrf]

Duane,

Thanks for your response. I think you are definitely on the right track. The only thing is that the query I was using has 1 main table with 11 left joined tables off it.

It sounds like you are suggesting I make the query into code and not use the query. It will probably make things load faster.

So how do I break this down for each column of each table that I might be pulling?


I have 5 fields from the main table (tblA),
3 from another (tblB),
3 from another (tblC),
1 from another (tblD),
1 from another (tblE),
2 from another (tblF),
1 from another (tblG),
1 from another (tblH),
and 1 from another (tblI).

So right now I do not have columns in each of these tables for a "nice name" to present to the user. Would it be possible to just create this listbox with values and put the values that we can write in the code as matching up to each field?

i.e. if lst.value1 then tblA.field1 etc?

I know I am making it too simple, but just trying to throw out the gist of some of the syntax.

Am I getting on track?

Thanks Duane!!


misscrf

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

 

[MajP]

Then I think you will have to do what Duane is suggesting, but also have a Group by control.

I would envision this.
1)Multi select listbox to choose fields to include
2)Your current filter controls
"choose from a bunch of different options to filter on, and based on that, they may not want certain fields in the results."
3)A group by control consisting of another multiselect listbox

So it is like a wizard.
1) you pick your fields
This builds the strSelect
2) only show the filter controls that are in the select statement
This builds the strWhere
3)Select the fields to group on
This builds the strGroupBy
4)Build your sql string

 

[dhookom]

I create master data source queries that combine tables and alias the field names to make them more user friendly. I save the queries with names like:
[tt]<Employee Timesheets>[/tt]
and
[tt]<Invoice Details>[/tt]
I then have a combo box to allow selecting from these queries.
[tt][blue]
Name: cboDataSource
Row Source: "SELECT Name FROM msysobjects WHERE Name Like "<*" ORDER BY Name;
[/blue][/tt]
I then use the after update to set the Row Source of a multi-select list box:
[tt][blue]
Name: lboFields
Row Source Type: Field List
Row Source:
[/blue][/tt]
Code in the after update of the combo box to set the Row Source of the list box.

   Me.lboFields.RowSource = Me.cboDataSource

My complete solution that saves the complete query definitions with where clauses, order by, etc can be found at DH Query By Form. This might be an older version that doesn't use the

 Me.SourceObject = "QUERY.MyQuery"

If so, you should update the code to comment out the update subform code and use the one line instead.

Duane
Hook'D on Access
MS Access MVP

 

[misscrf]

Thank you both for the responses. Duane, I think your latest response is taking me in the direction of creating a series of queries that have a fixed list of fields and having the user choose the query to use as a source? I am wanting to let them choose specifically the columns. To come up with the permeutations for the 18 columns, I would not logically come up with all the right queries. Partly because no query would make sense with just 1 column, but What is useful to the end user, they are not really telling me. They just want info, and I need it to be able to be build dynamically.


I thought you were saying that I could use the listbox in a way that would let me do something like this...

Private Sub cmdSummary_Click()

Dim Mysql As String
Dim strCriteria As String
strCriteria = "1=1 "
strCriteria = strCriteria & _
BuildIn(Me.LstCarMan, "txtCarManufacturer", "'")
strCriteria = strCriteria & _
BuildIn(Me.lstCarName, "txtCarName", "'")
[COLOR=red yellow]Mysql = "SELECT " & me.lstFields & " FROM qryCarQuery Where "[/color]
Mysql = Mysql & strCriteria
Me![frmSubCarQry].Form.RecordSource = Mysql
Me.frmSubCarQry.Visible = True
End Sub

(Code is for the click of the summary button. It has 2 listboxes on the main form for car manufacturer and car model. The user can choose none or more filters on either and then run their summary. I have other reporting menus that have more listboxes and date fields and combos but this is a simple one to give an example).


I know this is nowhere near right, but I am just trying to convey what it is I was wanting to do. The highlighted part is what I threw in to suggest that I want to tell the vb to choose the fields based on what the user selects from the listbox on the form (listbox is called lstFields.)

I know this would need to be expanded because I was going to have values in the fields listbox (Value 1, Value 2, Value 3) and in the vb somewhere I would spell out what value = which table field. ("Value 1" = tblA.Field1)

Is this possible? Am I going off the deep end? Thanks!


misscrf

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

 

[MajP]

Imagine I have a listbox where the first column is the possible field names, the second column is the nice readable Caption. Hide the first column and make it bound. Such as the demo. Load the list like I demonstrated

Public Sub populateList()
  On Error GoTo errLbl
  Dim rs As DAO.Recordset
  Dim fld As DAO.Field
  Dim cap As String
  Dim strList As String
  
  Set rs = currentdb.openrecordset("somequeryOrtable"
  Me.lstChoose.RowSourceType = "value list"
  For Each fld In rs.Fields
    Me.lstChoose.addItem (fld.Name & ";" & fld.Properties("Caption").Value)
  Next fld
  Exit Sub
errLbl:
  If Err.Number = 3270 Then
    Me.lstChoose.addItem (fld.Name & ";" & fld.Name)
    Resume Next
  Else
    MsgBox Err.Number & "  " & Err.Description
  End If
  
End Sub

Now click on your field captons.

Public Function getStrSelect() As String
  Dim lst As Access.ListBox
  Dim varItm As Variant
  Set lst = Me.lstChoose
  getStrSelect = "SELECT "
  For Each varItm In lst.ItemsSelected
    '1st field is the field name, 2nd is caption and visible
     getStrSelect = getStrSelect & lst.ItemData(varItm) & ", "
  Next varItm
  If Not getStrSelect = "SELECT " Then
    getStrSelect = Left(getStrSelect, Len(getStrSelect) - 2)
  End If
End Function

now you have part one

dim strSelect as string
dim strWhere as string
dim strGroupBy as string
dim strOrderBy as string
dim strSql as string

strSelect = getStrSelect()
.....
now the other parts
....
strSql = strSelect & strwhere & strGroupby & strOrderBy

 

[dhookom]

I have a combo box named cboDataSource with a Row Source as noted previously. I have several "master" queries saved with name beginning with "<" so they show in the combo box.

I then have a list box named lboFields and a subform control named sfrmResults.

I added a command button named cmdBuildSQL on the form to use the selected fields from the list box and display them in the subform control.

Option Compare Database
Option Explicit

Private Sub cboDataSource_AfterUpdate()
    Me.lboFields.RowSource = Me.cboDataSource
End Sub

Private Sub cmdBuildSQL_Click()
    Dim strSQL As String
    Dim strFields As String
    Dim strFrom As String
    Dim strQuery As String
    Dim lbo As ListBox
    Dim itm
    strQuery = "MyQuery" ' any saved query
    Set lbo = Me.lboFields
    For Each itm In lbo.ItemsSelected
        strFields = strFields & "[" & lbo.ItemData(itm) & "], "
    Next
    strFields = Left(strFields, Len(strFields) - 2)
    strSQL = "SELECT " & strFields & " FROM [" & Me.cboDataSource & "] "
    CurrentDb.QueryDefs(strQuery).SQL = strSQL
    Me.sfrmResults.SourceObject = "QUERY." & strQuery
End Sub

There is nothing in here to filter the results. It just takes the selected fields and displays them in the subform.

Duane
Hook'D on Access
MS Access MVP

 

[misscrf]

Thank you MajP and Duane. MajP, I think you are just really advanced for me. I am sure I seem quite dumb to you. Duane, I think I actually get your code for the cmdBuildSQL. I am going to try it out tomorrow and see where I can get with it. I will post back once I can try some of that out.

Thank you both for working through this with me!!! There are always 100 solutions to the problem. For me it will be the one I can grasp first!!!

misscrf

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

 

[MajP]

MajP, I think you are just really advanced for me.

No, I probably just not very articulate. Duane's code and mine are fundamentally the same, just packaged a little differently.

 

[misscrf]

ok, I feel like I am so close, but I am getting some odd errors. I set up a listbox called lstFields. I set it's type to a Field List and set the Row Source to the name of the query qryClientCars.

Then I modified my code that creates the criteria from the other controls on the form, to include the code that will use this new listbox as the field list and group by list.

When I try to turn the form over, I get an error " Circular reference cause by 'qryClientCars'.

When I try to compile the code (below) I get the error " Sub or Function not defined". It highlights the first "BuildIn" in the code.

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 itm
 strCriteria = "1=1 "
 strCriteria = strCriteria & _
 BuildIn(Me.LstClient, "txtClient", "'")
 strCriteria = strCriteria & _
 BuildIn(Me.lstCarManufacturer, "txtCarManufacturer", "'")
 strCriteria = strCriteria & _
 BuildIn(Me.lstCarModel, "txtCarModel", "'")

strQuery = "qryClientCars" ' any saved query
Set lbo = Me.lstFields
For Each itm In lbo.ItemsSelected
    strFields = strFields & "[" & lbo.ItemData(itm) & "], "
Next
strFields = Left(strFields, Len(strFields) - 2)
Mysql = "SELECT " & strFields & " FROM [" & strQuery & "] " & " Where "
Mysql = Mysql & strCriteria & " Group By " & strFields
Me![frmSubqryClientCarsQry].Form.RecordSource = Mysql
Me.frmSubqryClientCarsQry.Visible = True
End Sub

I took the part out about the cboDataSource because I don't have a combo in play here. Any thoughts on how I fix these two issues? I feel like I am so close to getting this to work!!!

misscrf

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

 

[misscrf]

ok, I got the circular reference to go away. During one of my attempts, the form and code actually turned the query SQL into what had been in the code, making it Select (what I had chosen in the listbox) from (the query itself) blah blah blah.

I had to rebuild the query and now that is gone. The compile error on the buildin is still there though, and I can't figure out why, because that statement worked fine before all of this. I guess I have to start taking out the new stuff and see where it breaks!

If you have thoughts, I am all ears (think Robin Hood Men in Tights - Lend me your ears!!!)

misscrf

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

 

[dhookom]

You need to learn how to trouble-shoot if you want to program. Try a debug.print of the SQL so you can see it'

Mysql = Mysql & strCriteria & " Group By " & strFields
debug.Print MySql
Me![frmSubqryClientCarsQry].Form.RecordSource = Mysql

Duane
Hook'D on Access
MS Access MVP