Dynamically refer to columns - Microsoft Access 2016

[Mading]

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":

[TABLE="width: 500"]
<tbody>[TR]
[TD]Training[/TD]
[TD]Teacher[/TD]
[TD]Admin[/TD]
[TD]Senior Management[/TD]
[/TR]
[TR]
[TD]Safeguarding[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]First Aid[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Fire marshall[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

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]

Sorry, the query I would need is:

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

 

[Joe4]

You would build the SQL code of the query in VBA, and then assign that SQL code to the query that your report is based on.
I showed someone how to do this in this post here: https://www.mrexcel.com/forum/micro...imilar-excel-find-all-result.html#post4108042

Your query is actually a bit simpler, and at the end, you would be opening the form based on your query (instead of your query).

 

[Micron]

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

[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 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.

 

[Joe4]

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]

you are probably correct in your interpretation. I guess time will tell, assuming this doesn't get solved somewhere else.

 

[Mading]

Hi Joe and Micron

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

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]

You are welcome!

 

[Micron]

Ditto!