Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Jun 2006
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Open Table based on Query Result (2003)

    I need help in opening a table based on the results of a query.

    I have a form "frmTables" with a list box "lstResources." The list box has the values "Fire Engine" and "Truck."

    There are three tables: one containing information on Fire Engines, "tblFireEngines," one containing information on Trucks, "tblTrucks," and one, "tblTables," containing the list box entry and the corresponding table name for the information on fire engines and trucks. That is, "tblTables" is a 2x2 table (fields are named "Resource" and "TableName") with the following entries ("-" denotes next field):
    Fire Engine - tblFireEngines
    Truck - tblTrucks

    Finally, there is a query, "qryTable," that will take the value picked in the list box "lstResources" and return the corresponding table name.

    I would like to be able to click on a command button within "frmTables" that runs the query in the background and opens the table that corresponds to user's choice in the list box, but I don't know how to do so. Any help is much appreciated!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Open Table based on Query Result (2003)

    Welcome to Woody's Lounge.

    First, the answer to your question:
    - Set the Row Source Type property of the list box to Table/Query.
    - Set the Row Source property to tblTables.
    - Set the Column Count property to 2.
    - Set the Column Widths property to 1";0".
    - Put a command button on the form.
    - Name it cmdTable (for example).
    - Activate the Event tab of the Properties window.
    - Click in the On Click box.
    - Select [Event Procedure] from the dropdown list.
    - Click the ... button to the right of the dropdown arrow.
    - This will create the "skeleton" for the code you need. Complete it like this:

    Private Sub cmdTable_Click()
    If Me.lstResources.ListIndex = -1 Then
    MsgBox "Please select a resource.", vbExclamation
    Me.lstResources.SetFocus
    Exit Sub
    End If

    DoCmd.OpenTable Me.lstResources.Column(1)
    End Sub

    - You can now switch back to Access.
    - Close and save the form.

    Column(1) refers to the hidden second column of the list box, containing the table name. Counting starts at 0, so 0 = first column, 1 = second column.

    Actually, I would never let the user view a table directly. I'd create a form frmFireEngines based on tblFireEngines and a form frmTrucks based on tblTrucks, and open the appropriate form instead of the table. This would require having the form name in the second field of tblTables, and changing DoCmd.OpenTable to DoCmd.OpenForm in the code.

  3. #3
    New Lounger
    Join Date
    Jun 2006
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open Table based on Query Result (2003)

    Worked like a charm! Thanks! I also took your suggestion on opening a form instead of the table.

Posting Permissions

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