Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    LA, CA

    Question Unanswered: Interactive Query Columns- VBA? SQL???

    hi there... i have had a request, and i have no idea how to do it. The requestor wants me to make something "easier than the query tool" (HA!) so that a user of the access database I am writing can select (we are talking a choice of 200+ columns) the data columns they want to see.

    The data is held in two separate tables. Any thoughts on how i can do this?

    My knee jerk solution start would be to create a query where just the tables are linked. Create a table with the column names, create a selection subform to select which column names the user would like to appear in the result, and then i get stuck. How, progmatically, can I ask Access to return the columns that have been specified as selected in the subform???

    Oh pretty please, you amazing Access Gurus... don't fail me now!!


  2. #2
    Join Date
    May 2005
    Nevada, USA
    Provided Answers: 6
    You will have to build the SQL in VBA; there is no native way to have the chosen fields show up with just SQL. This type of thing:

    strSQL = "SELECT " & ChosenField1 & ", " & ChosenField2 & ...

    This will probably get pretty involved, so good luck.

  3. #3
    Join Date
    Nov 2003
    LA, CA
    sigh... thanks!

  4. #4
    Join Date
    Dec 2002
    Préverenges, Switzerland
    you could loop thru the controls (i'm assuming checkboxes, and i'm assuming they have the same .name as the corresponding field in the table and i'm assuming there is only one table)
    private sub butgo_click()
        dim ctl as control
        dim strSQL as string
        for each ctl in me.controls
            if typeof ctl is checkbox then
                if ctl.value = true then
                    strSQL = strSQL & & ", "
                end if
            end if
        strsql = "SELECT " & left$(strSQL, len(strSQL) - 2) & " FROM mytable"
    end sub
    life gets more complicated with two tables, but you could store table name in the .tag property of the checkbox and then
    strSQL = strSQL & ctl.tag & "." & & ", "
    ...then add the join stuff in there.
    a bit messy!

    currently using SS 2008R2

  5. #5
    Join Date
    Jan 2007
    Provided Answers: 10
    Isn't there an MSys table that contains column information?

    I'd agree with the methods suggested above, but I'd use one listbox to display and select the columns from each of the two tables.

    A couple of other things to consider:
    • will you always want INNER JOINs?
    • what about aggregate functions (sum, count, etc)?
    • WHERE clauses?
    Home | Blog

Posting Permissions

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