Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2011-03-15
11
Medium Priority
?
363 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
  • 3
  • 3
  • 3
  • +2
11 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 668 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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 668 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 664 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

Industry Leaders: 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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

926 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