Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Nov 2001
    Thanked 0 Times in 0 Posts

    queries run from tables selected on the fly? (Access 2000)

    Is it possible to have a query set up to run on a table that it specified at the time the query is to be run?
    I have a bunch of tables that are going to be imported from excel spreadsheets, and rather than have the
    users rename the tables as they go, I'd like them to be able to choose from a list or click on a button on
    a form or something like that. Many thanks for any info!

    Mary Brodd

  2. #2
    New Lounger
    Join Date
    Feb 2002
    Thanked 0 Times in 0 Posts

    Re: queries run from tables selected on the fly? (Access 2000)

    hi mebhoya,

    u could use a combobox on a form that lists all the tables inside your mdb.

    In this example also a commandbutton is used to let the user create and open the query.

    Private Sub cmdCreateQuery_Click()
    Dim qdf As QueryDef
    Dim db As Database

    If Trim(cboTables.Value) <> "" Then
    Set db = CurrentDb
    db.CreateQueryDef "qry_" & Me.cboTables.Value, "Select * from " & Me.cboTables.Value & ";"
    DoCmd.OpenQuery "qry_" & Me.cboTables.Value
    Set db = Nothing
    MsgBox "Please select a table from the combobox...", vbOKOnly + vbInformation
    End If

    End Sub

    Private Sub Form_Open(Cancel As Integer)
    Dim tdf As TableDef
    Dim strList As String

    For Each tdf In CurrentDb.TableDefs
    If UCase(Left(tdf.Name, 4)) <> "MSYS" Then
    strList = strList & tdf.Name & ";"
    End If
    Next tdf
    Me.cboTables.RowSourceType = "Value List"
    Me.cboTables.RowSource = strList
    End Sub

Posting Permissions

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