Multiselect DTB to modify Event Procedure

[Valery]

Hi everyone,

I have had this sample database for a while. It is an ".mdb". It allows the user to select the records to be included in a report.

I would like it modified so the user could also select the report he would like to print. Right now, somewhere in the event, a report is selected. I only know enough of MS Access to create command buttons to open a report. I would have no idea how to modify this event.

Once it works (with report selection), I plan to incorporate into a current MS Access 2010 database.

Hope someone can help THANK YOU!

 

[The_Doc_Man]

I cannot open your database from work as we have restrictions.

I take it you are trying to make a generic "Print this report" function. There are ways to make this work, though I would be very careful about how I approached it.

First question: Is your report basically just any report within that database or is it a subset of the reports,... such as only the ones for which your user could have selected records for inclusion in the first place?

If you used a wizard to create a command button to print a report, the template it uses is to do a

...
Dim stDocName as string
...
stDocName = "MySelectedReport"
...
DoCmd.OpenReport ... stDocName
...

where the wizard gave you a list of names and you picked one and the name you picked is the name that gets asserted in the "stDocName = " statement. Well, if you had a list of report names for which this assignment would make sense, then you could make a combo-box control to select a report. Then in the code where you assign the name of the document to print, you would get that value from the combo box rather than a constant string.

 

[Valery]

Thank you - nice to know. Unsure how I would integrate that into the existing coding - don't know how to write: "...then in the code where you assign the name of the document to print". Wish you could open the mdb. As for the combo box, yes, I would type the name of reports into it.

 

[Cronk]

You need to give the user the ability to select the report to be printed. Easiest would be to have the reports in a table and present the user with a combo based on this table.

Simplest is to place the combo in the header of your contacts form. Make sure the report name is the bound column.

Then modify your open report statement to read
DoCmd.OpenReport me.cboReports, acViewPreview, , strWhere

 

[Valery]

Thank you Cronk! Ok - I can create a table; I can make a combo box. Ok for the bound column. Where do I modify the event procedure enclosed in the sample? Can you provide the event, updated, please? I will test it out.

 

[jdraw]

See if this post is helpful.

Selecting a report for PrintPreview or Printing with some conditions.

 

[Valery]

Thank you Jdraw. That really helps to create the report list. How do I link this to my event, to select records for that selected report?

 

[jdraw]

The routine I showed allows you to select a report, from all reports in your database, and print it, preview it or viewReport. It does not deal with selecting records for a report.

 

[Valery]

Ok but my initial questions does - please see the database sample. Would be great if you could assist me. THANK YOU

 

[GinaWhipp]

Well, not they way I do it but this should work...

First, you need a Combo Box with the list of Reports, then adjust the code...

   Dim strWhere         As String
   Dim myReport As String
 
   myReport = Me.YourComboBox
 
   strWhere = MySelected
  
   If strWhere <> "" Then
      strWhere = "contactID in (" & strWhere & ")"
   End If
   
   DoCmd.OpenReport myReport, acViewPreview, , strWhere
   DoCmd.RunCommand acCmdZoom100

 

[Valery]

GINA, the wizard! An answer! THANK YOU!! Will try this right away!

PS: very open to "the way" you would to it - just fire away!

 

[GinaWhipp]

Hmm, don't get happy yet... I said *SHOULD* work.

They way I do it is... select report, select from a bunch of filters and then preview, print or eMail the report. So, you never see the *record*. That way I can allow more reports because I'm, not limited by the Recordsource of the Form. I do not have a working model it so nothing to upload to demonstrate.

 

[Cronk]

Incidentally, I had provided you with the code modification in my earlier post

Then modify your open report statement to read
DoCmd.OpenReport me.cboReports, acViewPreview, , strWhere

This way, whatever report had been selected in the combo box would have been printed.

My way and Gina's are essentially the same.

 

[Valery]

Thank you Cronk.

 

[Valery]

Works Great! Would like to add a Select All button. The button that deselects all has the following coding:

Private Sub cmdClearRecordSelection_Click()
   Set colCheckBox = Nothing
   Me.Check11.Requery
End Sub

Created a new button for Select All and tried:

Private Sub cmdClearRecordSelection_Click()
   Set colCheckBox [COLOR=red]= All[/COLOR]
   Me.Check11.Requery
End Sub

Does not work. Anyone know the proper syntax? Also tried "true".

Thank you!

PS: Been researching this on the Web and found that the code I am using was provided by Albert Kallal and that another person also needed to have a Select All option... but their coding discussion is way over my understanding. At the end, she says it worked... so somewhere in there is my answer, lol. But it is not as simple as what I was asking above!!

Can anyone assist? http://www.sikhphilosophy.net/threads/to-albert-d-kallal-multiselect-list-box-question.9448/

THANK YOU!

 

[Cronk]

When you want "select All", is that all reports or all records for a selected report?

The way you have put
Set colCheckBox = All
in as code, it seems all records.

If that is the case, instead of opening the selected report with strWhere, just leave it off so there is no filtering ie all records.

 

[Valery]

Yes, it is for all RECORDS. But the "all" or "true" does not work. It renders "compile error Type mismatch". The code is:

Private Sub cmdSelectAllRecords_Click()
   Set colCheckBox = True
   Me.Check11.Requery
End Sub

You suggest to remove the first instance of strWhere? The whole line? Will selecting the records one by one still work though?

Private Sub cmdCreateReport_Click()
   Dim strWhere         As String
   Dim myReport As String
 
   myReport = Me.cboReportName
 
   strWhere = MySelected
  
   If strWhere <> "" Then
      strWhere = "UnitNum in (" & strWhere & ")"
   End If
   
   DoCmd.OpenReport myReport, acViewPreview, , strWhere
   DoCmd.RunCommand acCmdZoom100
End Sub

The "select all" is a button. I don't want to prevent the user from making an individual selection - just want to add the possibility of selecting all records in one click as it will be the case for many of the reports.

 

[Cronk]

If you think about it, using strWhere is filtering the records to show only those that have been selected. If you do not supply as filter condition, you will get ALL records.

That's what I meant by
"instead of opening the selected report with strWhere, just leave it off so there is no filtering ie all records"

That is, just use on your all button

DoCmd.OpenReport myReport, acViewPreview

 

[GinaWhipp]

Here's the code...

    Dim rst As dao.Recordset
    Set rst = Me.RecordsetClone
    Set colCheckBox = Nothing ' <--- at this to clear all
    rst.MoveFirst
     Do While rst.EOF = False
     colCheckBox.Add CLng(rst!ContactID), CStr(rst!ContactID)
     rst.MoveNext
     Loop
     
     Me.Requery
     Set rst = Nothing

Side note, not selecting any records will also return all records.

 

[Valery]

Works great! See attached sample. Open the form, select a report, select one or more records, click on Preview Report!

Thank you everyone!

PS: one thing really bugs me: The select record(s) check box is in front of a command button, so when you select a record(s), all the checkboxes "flicker". It works fine - just annoying. If anyone has a solution, would be nice.