Multilist checkbox for reports

[Stew]

I have been trying to find a good example for multilist checkbox to filter a report but have been unable to. I have a list of all the training events that a person is required to do. I want the user to get a multilist checkbox in a form. I want the report to show key fields from the tbl, such as last name, and show all the training events for that person that the user clicked. I hope I explained what I am trying to do. A good simple example of multilist checkbox to show a report I think will get me close enough I can do what I am looking for. Thanks alot.

[HansV]

I don't know what a multilist check box is. Do you mean a multi-select list box?

See the thread Multiple select listbox.

[Stew]

That was what I was looking for, but I'm not sure if that will work for what I am trying to do. What I have is one main table with all the information for a person. Such as name, address, training dates, etc... It is roughly 30 fields. I want a form to allow the user to select the fields they want to see, then show the information for all records with those fields showing. I think I'm missing some design concept here. My only other idea, is to use a form to allow them to select all fields that the user wants and procede to export to excel with the selected fields. Any advice would be helpful in this matter.

[HansV]

Do you mean that each training event is a separate field in a table? If so, that is a suboptimal design. It would be better to use a separate table with a record for each person - training event combination. This would also make it easier to design reports etc.

See the attached database (Access 2000 format, zipped). It shows how you can implement a similar setup - students and courses. The Students form displays students, and the courses taken by each student in a subform. The Courses form displays courses, and the students participating in each course in a subform.

Many2Many.zip

[teachesms]

The concept I gained from Stews phone call (for which I could not answer) is that Stew wants a report to output whatever fields the user decides they want in the report. Much like a parameter query allows the user to input the desired criteria and the report basically outputs only that criteria.

Although I have used the parameter queries for the basis of many reports, I could not tell him how you would allow the user to also select what fields they want to see when the report is run as well (allowing different fields to show up for different users needs). He caught my attention with this one. If there is a way to do it, I am sure you all would know, and will learn something new today if you know the answer.

ADDED later: This is all to be run from a simple macro button I assume

PS: Stew - Remember from class, the concept of each student listed in the main table once (Students), but that student may take several classes that show up in the secondary table (Schedule), and Classes is also a primary table...as many people may be taking the same class. This would create two parent tables and one child, (or two primary and one secondary in the middle). I hope you are grouping like data into different tables. Employee in one, Training in another, etc...linked thru a common field as we discussed.

[HansV]

One option would be to write code to design the report in runtime from the user's specifications. Although this is possible, it's not really easy.

Another option is to create a generic report with unbound text boxes, and to use code to set the control source of the text boxes in runtime. The attached sample database contains a (limited) example.

ReportDemo.zip

[Stew]

I was actually looking for excatly what that demo did. I should be able to tweak it to my specifications. Two questions. Would you possible have a database sample with the runtime user's specifications. I'm just curious on how that works. Access is pretty new to me, however I am a long time programmer and that looks like something that would be quite interesting to look at. The second question is this, when I export that report to excel it exports crazy. It doesn't have field names or header names, also it seems like it is fliped on the axises. Where the header should be is on the left, instead of the top. Thanks alot for your help Hans, you have been a life saver to me.

[HansV]

I don't have an example of designing a report in runtime on hand, and creating it for this thread would be a bit much.

When I export the report from the sample database to Excel, it has the rather meaningless column headers Text0, Text1 etc., but apart from that it looks OK:

rptCustomers.xls

As an alternative, you could create a query that returns the fields you want, and export that.

[Stew]

How would the query situation work?

[HansV]

In the attached version, I have created a select query qryCustomers. It doesn't really matter what it looks like, it will be modified by the code.

I added a command button on the form, with the following On Click event procedure:

Code: Select all

Private Sub cmdExportQuery_Click()
  Dim strSQL As String
  Dim itm As Variant

  For Each itm In Me.lbxFields.ItemsSelected
    strSQL = strSQL & ", [" & Me.lbxFields.ItemData(itm) & "]"
  Next itm
  If strSQL = "" Then
    Me.lbxFields.SetFocus
    MsgBox "Please select at least one or more fields in the list box.", vbExclamation
  Else
    strSQL = "SELECT" & Mid(strSQL, 2) & " FROM tblCustomers"
    ' Temporary message to see what's happening
    MsgBox strSQL, vbInformation
    CurrentDb.QueryDefs("qryCustomers").SQL = strSQL
    DoCmd.TransferSpreadsheet _
      TransferType:=acExport, _
      SpreadsheetType:=acSpreadsheetTypeExcel12Xml, _
      TableName:="qryCustomers", _
      FileName:="Customers.xlsx", _
      HasFieldNames:=True
  End If
End Sub

The code builds an SQL string from the fields selected by the user in the list box, changes the SQL of the query to this string, then uses DoCmd.TransferSpreadsheet to export the query to an Excel workbook.

ReportDemo.zip

[Stew]

Thank you for that example Hans, that will work perfectly for what I am trying to achieve.

[Stew]

Is there a way to put a line of code in that export command to open the spreadsheet on export?

[HansV]

You could add a line like this after the DoCmd.TransferSpreadsheet instruction:

Application.FollowHyperlink "C:\Test\qryCustomers.xls"

substituting the complete path and filename of the exported workbook.

[Stew]

I'm trying to build the query output (qryReportAll) from another query (qryReportFilter). I want the qryReportFilter to be selected by user. If they select BN show all, otherwise show the right company. When I run the export it is exporting all data not just what I click. Could you take a look at the code and see if I messed up the build string for the query. I can't find the error.

[HansV]

Your code changes the SQL of the query qryReportAll to

SELECT ... FROM qryReportAll

That is a circular definition. Don't you mean

SELECT ... FROM qryReportFilter

or even better

SELECT ... FROM tblMain

[HansV]

Here is the code you need:

Code: Select all

Private Sub btnExport_Click()
  Dim strSQL As String
  Dim itm As Variant

  If Me.List3.ListIndex = -1 Then
    Me.List3.SetFocus
    MsgBox "Please select a company in the list box.", vbExclamation
    Exit Sub
  End If
  For Each itm In Me.lstFieldList.ItemsSelected
    strSQL = strSQL & ", [" & Me.lstFieldList.ItemData(itm) & "]"
  Next itm
  If strSQL = "" Then
    Me.lstFieldList.SetFocus
    MsgBox "Please select at least one or more fields in the list box.", vbExclamation
    Exit Sub
  End If
  strSQL = "SELECT" & Mid(strSQL, 2) & " FROM tblMain"
  If Me.List3 <> "BN" Then
    strSQL = strSQL & " WHERE COMPANY='" & Me.List3 & "'"
  End If
  ' Temporary message to see what's happening
  MsgBox strSQL, vbInformation
  CurrentDb.QueryDefs("qryReportFilter").SQL = strSQL
  DoCmd.TransferSpreadsheet _
    TransferType:=acExport, _
    SpreadsheetType:=acSpreadsheetTypeExcel12Xml, _
    TableName:="qryReportFilter", _
    FileName:="Test Report.xlsx", _
    HasFieldNames:=True
End Sub

[Stew]

qryReportFilter has all the fields, but I am setting it up to take an input from the first select box. I want the dynamic export to take the filtered information so I want it FROM qryReportFIlter instead of tblMain

[Stew]

So I don't need two queries with that code, the code filters it with the IF statement. I'll give that a try instead.

[Stew]

That seems to be doing the trick, thanks for the help Hans. Just to get some clarification on why that works for my own record. You are building a SQL string with the field names you want. Then the select command pulls those fields from the table. Then you set the query definition with the sql string?

[HansV]

The code builds a SQL string that selects the fields from tblMain that are specified in the second list box, and adds a WHERE clause to select only those records with the company specified in the first list box (unless "BN" is selected, then the WHERE clause is omitted, so that all companies are returned).

Next, the SQL string of the query qryReportFilter is changed to the SQL string that the code constructed. If you open qryReportFilter directly, you'll see the fields and records specified on the form.

Finally, qryReportFilter is exported to an Excel workbook.

qryReportAll isn't used at all.