Solved

Print button on a form to print a range of records by highlighting records in a subform

Posted on 2011-03-15
11
345 Views
Last Modified: 2013-11-28
For simplicity, I have a form called A and an embedded suform called B where A and B are linked by an identifier called ID.  Subform B is displayed in datasheet view with a field called SN.  So one ID on form A could have multiple same IDs on subform B displaying a one-to-many SNs.  What I want to accomplish is the following:

The user highlights the records in the suform B (one column display of SN), and then clicks the Print button on the main form A and the report called "Labels90F0069" is opened in preview mode based on ONLY the highlighted records in subform B.  This VBA coding is beyond me and I could really use expert help.  Thanks.

stDocName = "Labels90F0069"
 DoCmd.OpenReport stDocName, acPreview
0
Comment
Question by:sxxgupta
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 3
  • +2
11 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 167 total points
ID: 35143582
it will be a lot easier, if you can add a logical (Print) field to the table
add the logical field to the subform ( select for records to be displayed in the report)
add the logical field to the report (you can hide the check box)

use the Print logical field to filter the report

stDocName = "Labels90F0069"
 DoCmd.OpenReport stDocName, acPreview,,"[Print]=-1"
0
 
LVL 58
ID: 35143645
<<it will be a lot easier, if you can add a logical (Print) field to the table>>

 Concur.  While it is possible to determine the selected range, it's a lot more work.  

JimD.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35143961
...and also remember that you should consider using a listbox to do the selections, because a subform in datasheet view will not allow you to make non-contiguous selections
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35144161
0
 

Author Comment

by:sxxgupta
ID: 35147302
A logical field like a checkbox for each record.  So if I want to print select records, then I would have to check the box 80 times........
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35147334
you can add a button to select all..

0
 

Author Comment

by:sxxgupta
ID: 35147354
So in my subform, for a record on my main form, I have say 80 serial numbers (80 unique records) listed (datasheet view in the subform).  Now say I highlight 30 records in the middle of the subform with the mouse and click print..............that is what I am after........
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35147532
ok, try this codes in the click event of the subform

dim i as integer, strSN
with me.recordsetclone
   .movefirst
   .move me.seltop-1
   for i=1 to me.selheight
        strSN=strSN & "," & ![serialnumber]
       .movenext

   next
end with

msgbox strSN
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 167 total points
ID: 35147954

This is why I suggested using a Listbox.
This way you can easily select a "Contiguous Range" of values (Click-Shift)
(Click the first record you want then hold down shift and click on the last record you want.)
Or do a Control-Click to select non contiguous records.

If you wanted to select a contiguous range of records with a checkbox, then yes, you would have to click each individual checkbox.

So for a contiguous range of records, a listbox might be more convent.
For non contiguous ranges, the time will be the same.

Again, selecting a non-contiguos range by clicking the record selector is not possible with a standard subform in any event.

JeffCoachman







0
 
LVL 31

Assisted Solution

by:Helen Feddema
Helen Feddema earned 166 total points
ID: 35156481
A multi-select listbox would indeed be a better idea here.  Here is some code for processing selected items in a multi-select listbox:
Private Sub cmdExportData_Click()

On Error GoTo ErrorHandler

   Dim intColumn As Integer
   Dim intColumns As Integer
   Dim intCount As Integer
   Dim intIndex As Integer
   Dim intRow As Integer
   Dim intRows As Integer
   Dim lst As Access.ListBox
   Dim strData As String
   Dim strPrompt As String
   Dim strTest As String
   Dim strTitle As String
   Dim varItem As Variant
   
   Set lst = Me![lstSelectContacts]
      
   'Check that at least one item has been selected
   If lst.ItemsSelected.Count = 0 Then
      strTitle = "No items selected"
      strPrompt = "Please select at least one item"
      MsgBox prompt:=strPrompt, _
         buttons:=vbInformation + vbOKOnly, _
         Title:=strTitle
      lst.SetFocus
      GoTo ErrorHandlerExit
   End If
   
   intColumns = lst.ColumnCount
   intRows = lst.ItemsSelected.Count
   strTitle = "Information missing"
   
   'Test for required information, using listbox columns
   For Each varItem In lst.ItemsSelected
      'Check for required address information (or whatever you need to check)
      strTest = Nz(lst.Column(5, varItem))
      Debug.Print "Street address: " & strTest
      If strTest = "" Then
         strPrompt = "Skipping this record -- no street address!"
         MsgBox prompt:=strPrompt, _
            buttons:=vbExclamation + vbOKOnly, _
            Title:=strTitle
         GoTo NextItem
      End If
      
      'Do something with info from the listbox columns, using
      'this syntax
      strData = Nz(lst.Column(5, varItem))
      
NextItem:
   Next varItem

ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & "; Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Open in new window

0
 

Author Closing Comment

by:sxxgupta
ID: 35158611
Ok, thanks for the information experts.  I am playing around with both ideas suggested.  Checkboxes vs. listboxes
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question