Results 1 to 9 of 9

Thread: Access 2007

  1. #1
    Join Date
    Jan 2014
    Posts
    5

    Unanswered: Access 2007

    Hi Guys
    Short time reader; first time poster (of text on the forum as opposed to promotional material for your wall).

    I'm trying to set up a form to generate a specific query based on what the user selects in a drop down box. I have a list of queries ready to be called upon and a drop down box on the form equipped with the names of the queries in list form. Is this possible or am I some kind of delusional novice tampering with forces I could never fully understand?

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If I understand correctly what you're trying to achive, you could use:
    Code:
    Private Sub Combo_Queries_AfterUpdate()
        Me.RecordSource = Me.Combo_Queries.Value
    End Sub
    Replace the names used in my example by the actual ones in your project.
    Have a nice day!

  3. #3
    Join Date
    Jan 2014
    Posts
    5
    Cheers for the reply, dude. That didn't work, though. I've attached a picture of what I have so far. Essentially I want the code to generate the appropriate query from the left based on the text in the combo box list...
    Am I way off base here?
    Attached Thumbnails Attached Thumbnails Database.png  

  4. #4
    Join Date
    Jan 2014
    Posts
    5

    Cheers, dude

    Thanks for the reply, man. That didn't work, though. I've attached a screendump of what I have so far. I want the code to call upon the queries on the left based on the text selected in the combo box list.
    Is that possible?
    Attached Thumbnails Attached Thumbnails Database.png  

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by LaurenceCahir View Post
    Cheers for the reply, dude. That didn't work, though. I've attached a picture of what I have so far. Essentially I want the code to generate the appropriate query from the left based on the text in the combo box list...
    Am I way off base here?
    There are no attachments, pictures or whatsoever in the 2 last messages you posted. Please post the code you use and a sample of the contents of the combo.
    Have a nice day!

  6. #6
    Join Date
    Jan 2014
    Posts
    5

    I'm going to level with you

    the last time I had anything to do with databases was ten years ago and I'm lost in a sea of trial, error and lack of understanding. I have gotten as far as making up about 27 different query tables and I have a form with a combo box containing the names of those different queries that I input manually.
    I just need to generate a specific query based on the selected text within the combo box... if it's possible...

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If the rows in the combobox contain each the name of a SELECT query, what do you want to do with this query? Open it? If so, you can use:
    Code:
    Private Sub Combo_Queries_AfterUpdate()
        Docmd.OpenQuery Me.Combo_Queries.Value
    End Sub
    Note: In an application, users should not be allowed to open tables or queries. They should have to open a form the RecordSource property of which is a table or a query, hence my first answer.
    Have a nice day!

  8. #8
    Join Date
    Jan 2014
    Posts
    5

    Aah

    I'm starting to grasp what you're trying to explain to me now... Cheers, mate.

    I need to do some redesigning and a LOT of soul searching about where my life is going...

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You dont necessarily need to refactor everything and the work does not need to be heavy.

    1. For each query in the combo, create a form in datasheet view and neme it after the query name, but with a prefix to avoid confusion. e.g. query: Administration --> Form: F_Administration, query: Retail --> Form: F_Retail, etc. With the form wizard this can be done very quickly.

    From that moment you'll be able to use something like:
    Code:
    Private Sub Combo_Queries_AfterUpdate()
        Docmd.OpenForm "F_" & Me.Combo_Queries.Value
    End Sub
    2. If you want to refine further, you could use a main (parent) form and use the forms created in 1. as child forms (subforms):
    a. Create the parent form and copy the combo into it in the header section.
    b. Add a subform/subreport control in the Detail section of the form but do not specify any SourceObject for it (use the Cancel button of the wizard if it activates). Name it ChildForm.
    c. Link the AfterUpdate event of the combo to this procedure:
    Code:
    Private Sub Combo_Queries_AfterUpdate()
        Me.ChildForm.SourceObject = "F_" & Me.Combo_Queries.Value
    End Sub
    Have a nice day!

Tags for this Thread

Posting Permissions

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