Solved

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

Posted on 2011-03-15
11
347 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
Technology Partners: 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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

623 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