Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2012
    Posts
    108
    Provided Answers: 1

    Answered: Choose Query To Run

    Hi all,

    Using Access 2013.

    Is it possible to prompt user to select a query to run much like a FileDialogPicker?

    thanks
    w

  2. Best Answer
    Posted by Sinndho

    "Yes it is. Here's a solution (among others):

    1. Create a form with the following properties and save it under the name «Frm_QueryPicker»:
    - Caption: QueryPicker
    - Default View: Single Form
    - Record Selector: No
    - Navigation Buttons: No
    - Control Box: Yes
    - Min Max Buttons: None

    2. On the form, create a ListBox with the following properties:
    - Column Count: 1
    - RowSource Type: Table/Query
    - RowSource:
    Code:
    SELECT MSysObjects.Name FROM MSysObjects WHERE (((MSysObjects.Type)=5) AND ((MSysObjects.Name) Not Like "~*")) ORDER BY MSysObjects.Name;
    - Bound Column: 1
    - On Dbl Click: [Event Procedure]

    3. In the FormFooter section of the form, create a Command Button with the following properties:
    - Name: Command_Open
    - Caption: Open
    - Picture: (none)
    - On Click: [Event Procedure]
    - ControlTip Text: Open selected Query

    4. In the FormFooter section of the form, create a second Command Button with the following properties:
    - Name: Command_Cancel
    - Caption: Cancel
    - Picture: (none)
    - On Click: [Event Procedure]
    - ControlTip Text: Cancel

    5. In the FormFooter section of the form, create a CheckBox control with the following properties:
    - Name: Check_AutoClose
    - Default Value: True
    For the associated label:
    - Name: Label_AutoClose
    - Caption: Close after opening Query

    6. Paste the following code in the Module of the form and check that the controls are actually associted with their respective Event Handlers:
    Code:
    Private Sub OpenQuery()
    
        Dim strQueryName As String
        
        strQueryName = Nz(Me.List_Queries.Value, "")
        If Len(strQueryName) > 0 Then DoCmd.OpenQuery strQueryName
        If Me.Check_AutoClose.Value = True Then DoCmd.Close acForm, Me.Name
    
    End Sub
    
    Private Sub Command_Cancel_Click()
    
        DoCmd.Close acForm, Me.Name
        
    End Sub
    
    Private Sub Command_Open_Click()
    
        OpenQuery
        
    End Sub
    
    Private Sub List_Queries_DblClick(Cancel As Integer)
    
        OpenQuery
        
    End Sub
    "


  3. #2
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    Yes it is. Here's a solution (among others):

    1. Create a form with the following properties and save it under the name «Frm_QueryPicker»:
    - Caption: QueryPicker
    - Default View: Single Form
    - Record Selector: No
    - Navigation Buttons: No
    - Control Box: Yes
    - Min Max Buttons: None

    2. On the form, create a ListBox with the following properties:
    - Column Count: 1
    - RowSource Type: Table/Query
    - RowSource:
    Code:
    SELECT MSysObjects.Name FROM MSysObjects WHERE (((MSysObjects.Type)=5) AND ((MSysObjects.Name) Not Like "~*")) ORDER BY MSysObjects.Name;
    - Bound Column: 1
    - On Dbl Click: [Event Procedure]

    3. In the FormFooter section of the form, create a Command Button with the following properties:
    - Name: Command_Open
    - Caption: Open
    - Picture: (none)
    - On Click: [Event Procedure]
    - ControlTip Text: Open selected Query

    4. In the FormFooter section of the form, create a second Command Button with the following properties:
    - Name: Command_Cancel
    - Caption: Cancel
    - Picture: (none)
    - On Click: [Event Procedure]
    - ControlTip Text: Cancel

    5. In the FormFooter section of the form, create a CheckBox control with the following properties:
    - Name: Check_AutoClose
    - Default Value: True
    For the associated label:
    - Name: Label_AutoClose
    - Caption: Close after opening Query

    6. Paste the following code in the Module of the form and check that the controls are actually associted with their respective Event Handlers:
    Code:
    Private Sub OpenQuery()
    
        Dim strQueryName As String
        
        strQueryName = Nz(Me.List_Queries.Value, "")
        If Len(strQueryName) > 0 Then DoCmd.OpenQuery strQueryName
        If Me.Check_AutoClose.Value = True Then DoCmd.Close acForm, Me.Name
    
    End Sub
    
    Private Sub Command_Cancel_Click()
    
        DoCmd.Close acForm, Me.Name
        
    End Sub
    
    Private Sub Command_Open_Click()
    
        OpenQuery
        
    End Sub
    
    Private Sub List_Queries_DblClick(Cancel As Integer)
    
        OpenQuery
        
    End Sub
    Attached Thumbnails Attached Thumbnails QueryPicker.jpg  
    Attached Files Attached Files
    Have a nice day!

  4. #3
    Join Date
    Feb 2012
    Posts
    108
    Provided Answers: 1
    Thanks Sinndho,

    Works great!

  5. #4
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    You're welcome !

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •