none
Select query with changable field name RRS feed

  • Question

  • Hi ,

    I want to make a select query whose one field should be based on user requirement.

    my table has fields :  ID, Name, fld1, fld 2, fld3....fld20.

    Now a forms has a combo box having value fld1, fld2, fld3....fld20.

    Now when a user select field name in combobox and hit "GO" i want to run a select query having fields ID, name and selected field by user, may be fld1, or fld2 or any.

    I know it can be done but i don't know how.

    Can any one help me to do that?

     Thanks

    Friday, October 23, 2015 7:20 AM

Answers

  • You can change the SQL of the query:

    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef
    Set dbs = CurrentDb
    Set qdf = dbs.QueryDefs("MyQuery")
    qdf.SQL = "SELECT [ID], [Name], [" & Me.ComboBoxName & "] FROM [MyTable]"

    Change the names as needed.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, October 23, 2015 9:36 AM
  • You might be interested in the following function which opens a datasheet of any SQL SELECT statement passed into it as a string expression:

    Public Function OpenTempQuery(strSQL As String)

        Dim qdf As DAO.QueryDef
        Static n As Integer
        
        n = n + 1
                   
        ' delete temporary querydef object if exists
        On Error Resume Next
        CurrentDb.QueryDefs.Delete "Temp" & n
        Select Case Err.Number
            Case 0
            ' no error
            Case 3265
            ' temporary querydef does not exist,ignore error
            Case Else
            ' unknown error
            MsgBox Err.Number, vbExclamation, "Error"
        End Select
        ' create temporary querydef object
        Set qdf = CurrentDb.CreateQueryDef("Temp" & n)
        CurrentDb.QueryDefs("Temp" & n).SQL = strSQL
        ' open query and then delete temporary querydef object
        DoCmd.OpenQuery "Temp" & n
        ' delete temporary querydef object
        CurrentDb.QueryDefs.Delete "Temp" & n

    End Function

    As many datasheets as you wish can remain open simultaneously by calling the function repeatedly, passing in a different SQL statement each time.  In fact, this is why I originally wrote the function, to provide a quick way of doing 'what if' comparisons by changing the WHERE clause of the SQL statement each time.

    Ken Sheridan, Stafford, England

    Friday, October 23, 2015 12:14 PM

All replies

  • You can change the SQL of the query:

    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef
    Set dbs = CurrentDb
    Set qdf = dbs.QueryDefs("MyQuery")
    qdf.SQL = "SELECT [ID], [Name], [" & Me.ComboBoxName & "] FROM [MyTable]"

    Change the names as needed.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, October 23, 2015 9:36 AM
  • Thanks Hans Vogelaar, I am going to check it and will intimate you
    Friday, October 23, 2015 10:27 AM
  • You might be interested in the following function which opens a datasheet of any SQL SELECT statement passed into it as a string expression:

    Public Function OpenTempQuery(strSQL As String)

        Dim qdf As DAO.QueryDef
        Static n As Integer
        
        n = n + 1
                   
        ' delete temporary querydef object if exists
        On Error Resume Next
        CurrentDb.QueryDefs.Delete "Temp" & n
        Select Case Err.Number
            Case 0
            ' no error
            Case 3265
            ' temporary querydef does not exist,ignore error
            Case Else
            ' unknown error
            MsgBox Err.Number, vbExclamation, "Error"
        End Select
        ' create temporary querydef object
        Set qdf = CurrentDb.CreateQueryDef("Temp" & n)
        CurrentDb.QueryDefs("Temp" & n).SQL = strSQL
        ' open query and then delete temporary querydef object
        DoCmd.OpenQuery "Temp" & n
        ' delete temporary querydef object
        CurrentDb.QueryDefs.Delete "Temp" & n

    End Function

    As many datasheets as you wish can remain open simultaneously by calling the function repeatedly, passing in a different SQL statement each time.  In fact, this is why I originally wrote the function, to provide a quick way of doing 'what if' comparisons by changing the WHERE clause of the SQL statement each time.

    Ken Sheridan, Stafford, England

    Friday, October 23, 2015 12:14 PM
  • Thank you Hans Vegelaar,

    It is exactly what I want.

    Thank you

    Friday, October 23, 2015 3:25 PM
  • Thank you for Your Reply,

    I am going to check it

    Friday, October 23, 2015 3:26 PM
  • Thanks Ken,

    Its very nice and useful.

    Tuesday, October 27, 2015 5:37 AM