Search code examples
sqlms-access

Can I use a textbox reference to select specfic records in MS Access

I have a Union Query: Here's part of it:

SELECT CompletedTrainingsNew.Employee, CompletedTrainingsNew.AbuseNeglect AS Compdate, AbuseNeglect+(SELECT Expdays 
    FROM Trainings 
    WHERE Trainings.[Training Name] = "Abuse & Neglect"
) AS Expired, EmployeeInformation.[Last Name], EmployeeInformation.Employee, "Abuse & Neglect" AS Training
FROM EmployeeInformation INNER JOIN CompletedTrainingsNew ON EmployeeInformation.ID = CompletedTrainingsNew.Employee;

The rest of this query is just a bunch of Unions on different fields but all following the same format, pick a field give it an alias, Compdate, Calculate it's expiration date and give it an alias, Create a field that displays the name of the original field, name it "Training".

What I need to do, is have the query filter via either a Combobox on a form, or a textbox bound to that combo box.

I thought I could add in a WHERE clause at the end:

WHERE EmployeeInformation.Employee = "Forms!FORMNAME!TEXTBOX(Combobox)NAME.Value"

I have also tried this without the "", or the .Value... Without the "" it doesn't understand what I am asking it to do, Enter Parameter)

Now the Query runs, and returns the entire list of records properly, So no issues on that end. But when I try to have it select just the records matching the input box, in this case Employee name, it comes back blank. The employee Name box, currently a TEXT BOX attached to the form, is filled from a COMBO BOX on the same form, using the .Column(n) attribute.

= EmployeeLookup.Column(3)

This is a text column, And when the form is open, it populates the TEXT BOX with the correct information

"John Doe" for example

So is there a way to do this in SQL, or does it have to be done via VBA? Or am I just putting the Statement in the wrong location?

Edited to add, I have attached the first portion of this query, because I am trying to get it to work, I have removed all the UNION statements, and am just working right now with this simple query, in order to solve the problem.

Solution

  • Try with the normal syntax:

    WHERE EmployeeInformation.Employee = Forms!FORMNAME!TEXTBOX