Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2003
    Posts
    2

    Question Unanswered: Trying to modify query using Drop Downs

    What I am trying to do is make it easier for NON-ACCESS types to run reports from a single form. Here are details of what I have, and am trying to do…

    Currently there are two tables with the following information in each:
    Table#1
    ID
    SSN
    Last_Name
    First_Name
    MI
    Active (Yes/No)
    Section
    Section_Leader

    Table#2
    ID
    SSN
    Class_Name
    Class_Date
    FY_Quarter

    ( Table#1 has more info, but it is not relevant to this situation. Note that in the query the information for Table#1 is linked to Table#2 via SSN.)

    I first run a query (Query#1) that ..
    1- deletes any existing table by the name of EMP_Roster
    2- creates a new roster of all employees who have active marked as ‘YES’

    Next I am trying to take information from a drop down list in Form#1 that asks for:
    Training............Combo1
    Fiscal Year........Combo2
    Fiscal Quarter....Combo3
    Section.............Combo4

    Okay, now the problem – how do I get the above information into the query so it can look for the information? If I do it manually, I use a query such as this:


    Field: Class_FY_Quarter
    Table: Emp_Roster
    Update To: [Table#2].[FY_Quarter]
    Criteria:

    Field: Class_Name
    Table:Table#2
    Update To:
    Criteria:“Individual Safety”

    Field: FY_Quarter
    Table:Table#2
    Update To:
    Criteria:[What_FY_Quarter?]

    (I couldn't get this to format correctly in the way that it shows up on the screen)

    I need to be able to modify this query using VBA (I am weak in VBA but know NOTHING about SQL) so that my users selections from Form#1 can generate the correct data.

    Can someone help me? If necessary, you can email me with the information, or to ask for more clarification.

    THANK YOU !!

  2. #2
    Join Date
    Apr 2002
    Location
    Illinois
    Posts
    133

    Re: Trying to modify query using Drop Downs

    From what I can tell you have 4 different combo boxes. Regardless of how many, you can run your query based on the info in those combo boxes. To do this, link the Criteria in the field(s) of the query to the combo boxes using the Expression Builder. Check Access Help for more info.
    SteveH

  3. #3
    Join Date
    Sep 2003
    Location
    Washington
    Posts
    8
    Let's see.. In the criteria field of the query just use this format.

    =Forms!FormName!ComboBoxName

    That will pass the value from the current open form. However, I believe the form has to remain open.. So if you were printing a report based off the values in those combos.. You go do something like

    Private Sub CommandButton_Click()
    Docmd.minimize
    docmd.openreport "ReportName", acnormal
    End Sub

    Then in the report stick this in it.

    Private Sub Report_Close()
    Forms!FormName.SetFocus
    DoCmd.Restore
    End Sub

    So after it prints, when you close the form.. It sets focus back to the form and restores it..

Posting Permissions

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