?
Solved

Need help with VBA syntax when referencing an option group for exporting Access reports

Posted on 2014-07-23
4
Medium Priority
?
399 Views
Last Modified: 2014-07-28
I need to give users the option of exporting Access reports in PDF or XPS format.  The code used for exporting the reports works fine, but I need help finding the syntax that will run the correct set of code once users have selected their desired exporting option.  There are two sets of code to run; if a user selects the PDF option then the syntax for exporting in PDF runs.  If the user selects the XPS option then the syntax for exporting in XPS runs.  

I created an option group with two buttons: PDF (name is optPDF; value = 1) or XPS (name is optXPS; value = 2).  The default is PDF.  I now realize that what matters is which button has the focus.  Thus, saying if Me.optPDF.value = 1 (or Me.optXPS.value =2) doesn't matter.  I'm guessing I need to say something like if Me.optPDF.HasTheFocus then proceed to run the code for exporting reports.

I'm familiar with Select Case statements but cannot find the right combination of syntax to make them work.  Please advise if my code needs to identify which option button has the focus and, if so, what is the correct syntax for that.  If there is a better way to do this I'm would appreciate any suggestions.
0
Comment
Question by:Liberty4all
[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
  • 2
  • 2
4 Comments
 
LVL 31

Assisted Solution

by:Helen Feddema
Helen Feddema earned 2000 total points
ID: 40215411
You need to get the value of the option group itself, rather than the buttons within it.  Here is some typical code:
Private Sub cmdMultipleContactsQuery_Click()
'Created by Helen Feddema 28-May-2014
'Last modified by Helen Feddema 30-May-2014

On Error GoTo ErrorHandler
   
   intMethod = Nz(Me![fraMethodQuery].Value, 1)
   
   Select Case intMethod
   
      Case 1
         DoCmd.OpenForm FormName:="frmDocPropsQuery"
         
      Case 3
         DoCmd.OpenForm FormName:="frmTypeTextQuery"
      
      Case 2
         DoCmd.OpenForm FormName:="frmMailMergeQuery"
      
      Case 4
         DoCmd.OpenForm FormName:="frmBookmarksQuery"
         
      Case 5
         DoCmd.OpenForm FormName:="frmCatalogMergeQuery"
         
      Case 6
         DoCmd.OpenForm FormName:="frmFormFieldsQuery"
            
      Case 7
         DoCmd.OpenForm FormName:="frmDocVarsQuery"
            
   End Select
   
   Parent.Visible = False
   
ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in " & Me.ActiveControl.Name & " procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Open in new window

0
 

Accepted Solution

by:
Liberty4all earned 0 total points
ID: 40215669
I appreciate your quick response.  I'm guessing that "fra" is short for frame?  If so, the default name for my option group is Frame46.  I renamed it to fraExp (for frame Export) and used two IF statements as follows.

If Me.fraExp = 1 Then (runs code to export reports in PDF format)

If Me.fraExp = 2 Then (runs code to export reports in XPS format)

I usually don't work with option groups so forgot the border (frame) of the option group is the name of the object, and the value of the group itself, and not the individual buttons within it, is what matters.  

Since the above worked I did not need to use Select Case statements but will keep your info for future use.

Thank you very much.
0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 40215738
An If ... ElseIf ... End If statement works fine if you just have two options.
0
 

Author Closing Comment

by:Liberty4all
ID: 40223725
See my comments above as to why I accepted my own solution.  For my purposes it worked the best, but I want the points awarded to Helen Feddema as I would not have been able to solve this without her help.
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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Suggested Courses
Course of the Month14 days, 10 hours left to enroll

771 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