?
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
?
355 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 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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
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.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

719 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