Solved

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

Posted on 2011-03-15
11
332 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 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 57
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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
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…

856 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