CODEWITHSUNDEEP
comboboxms-access-2007
user2294977
user2294977

Reputation: 111

Form ComboBox Acting As Table Column Header In Access 2007 Query

I have a form with an unbound combobox that has all the column headings for the table dbo_orderheader. What I want to do is use the combobox field to act as the table column header instead of hard coding it to a specific table column header, that way a user can search dynamically from the form on the column they choose instead of having a huge list of search boxes for each table column.

Please can anyone help on a way to do this in an access query? I am using Access 2007.

Thanks.

See Picture Attached

Upvotes: 0

Views: 595

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123829

I'm pretty sure that there's no way to imbed a form reference as a column heading in a static query design, but you could use code behind your form to dynamically update the query design and then open the query, something like this

Private Sub btnOpenQuery_Click()
Dim cdb As DAO.Database, qdf As DAO.QueryDef
Const queryName = "flexQuery"

Set cdb = CurrentDb
DoCmd.Close acQuery, queryName, acSaveNo
On Error Resume Next
DoCmd.DeleteObject acQuery, queryName
On Error GoTo 0
Set qdf = cdb.CreateQueryDef(queryName, _
        "SELECT URN, StyleNo, [" & Me.Combo3.Value & "] " & _
        "FROM dbo_OrderHeader " & _
        "WHERE [" & Me.Combo3.Value & "]=""" & Me.Text5.Value & """" _
    )
Set qdf = Nothing
Set cdb = Nothing
DoCmd.OpenQuery queryName, acViewNormal
End Sub

Note: This sample code assumes that the "dynamic" column is a Text column, so it puts " characters around the Text5.Value when constructing the SQL statement. This code would have to be enhanced to handle other column types (e.g., no quotes for numeric columns, and perhaps # delimiters for dates).

Upvotes: 1

Related Questions