Dynamically refer to columns - Microsoft Access 2016

Mading

New Member
Joined
Feb 10, 2016
Messages
7
Hi

Just wondering, as the title suggests how to write a query that - based on what is entered on a form - dynamically refers to a column heading in a table. So:

I have a table called "Required training":

TrainingTeacherAdminSenior Management
Safeguarding111
First Aid100
Fire marshall010

<tbody>
</tbody>

I have a form where I want the end user to be able to select "teacher" (for example), run a report which will be linked to the query, and see a report of all the training that a teacher is required to do. I know how to link the report to the query from within the form, my problem is I don't know how to make the query dynamic so that whatever is selected from the drop-down box on the form will be returned by the query i.e.

Select [column name from form drop-down]
From tblRequiredTraining
Where [colum name from form drop-down] = 1

Any help would be appreciated, let me know if I can do anything to make it clearer

Thanks
 

Mading

New Member
Joined
Feb 10, 2016
Messages
7
Sorry, the query I would need is:

Select Training
From tblRequiredTraining
Where [colum name from form drop-down] = 1
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,493
Office Version
365
Platform
Windows

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
1,767
I could be wrong, but I don't think that answers the question.
First, your query example is missing the field from the table you want to compare the column value to; i.e. [column name from form drop-down] has to be a field reference, not a "value from some type of control" kind of reference.
My take is that your query sql statement would be like
Code:
[COLOR=#333333]Select [Training] [/COLOR][COLOR=#333333]From [tblRequiredTraining] [/COLOR][COLOR=#333333]Where [Teacher] = Forms!frmNameOfYourForm.[/COLOR][COLOR=#ff0000]NameOfYourControl[/COLOR]
The red part represents your example of 1. This assumes that you want to run this query AFTER a combo selection is chosen, and use the chosen value. It also assumes the value you're getting from the control is a number and not text. If text, the expression must wrap the value in quotes. You will need to know how to concatenate a WHERE clause for such situations.

BTW,You're not referring to a column heading; you're referring to a control. If the control is a combo box with one column, you don't need to worry about columns. If it has more than one column, the columns collection is zero based, so the second column is 1, not 2. Then you'd need .NameOfYourControl.Column(n) where n represents the zero based column number.

By the time you see this, I will probably have embarked on a 3 day road trip and will be somewhat limited in my ability to respond in a timely manner, but I'll try to keep up.
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,493
Office Version
365
Platform
Windows
I could be wrong, but I don't think that answers the question.
I think you and I have different interpretations of the question.

It appears to me that the variable part if which field they are using in the criteria, not the value of a certain pre-determined field.
As such, I do not believe something like this would work:
Select [Training] From [tblRequiredTraining] Where [Teacher] = Forms!frmNameOfYourForm.NameOfYourControl
The point of the link in my post was to show how you can dynamically build SQL code on-the-fly. In that example, it does not show one where which field being used in the criteria is dynamic, but it shows you how you can build the code manually, for whatever part is dynamic/variable. You would just have the drop-down box on the form list the different table fields you might want to include in your query, and then build the SQL code based off of that selection.
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
1,767
you are probably correct in your interpretation. I guess time will tell, assuming this doesn't get solved somewhere else.
 

Mading

New Member
Joined
Feb 10, 2016
Messages
7
Hi Joe and Micron

Many, many thanks for your input - actually I found the solution by combining both of your answers! This worked:

Code:
Private Sub cmdSearchButton_Click()
'
    Dim mySQL As String
    
'   Build SQL code
    mySQL = "SELECT qryCourseTitles.[Course Title], qryCourseTitles.[" & Me.txtSearchValue & "]  FROM qryCourseTitles WHERE" & _
            " qryCourseTitles.[" & Me.txtSearchValue & "] = " & Chr(49) & ";"
            
'   Assign SQL code to pre-assigned query
    CurrentDb.QueryDefs("QuerySearch").SQL = mySQL
    
'   Open query with results
    DoCmd.OpenQuery "QuerySearch"
    
End Sub
Once again, thank you both
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,493
Office Version
365
Platform
Windows
You are welcome!
 

Forum statistics

Threads
1,082,250
Messages
5,364,020
Members
400,774
Latest member
Goldi paul

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top