Using VBA Variable in an Access Form Row Source Qry

[carlp69]

I have an Access Form with a drop down option for employees. the drop down using the following Row Source Query to retrieve the relevant data from an SQL database :-
"SELECT DISTINCT EmployeeID, Surname, FirstName, Junior FROM tbl_Employees WHERE (Junior = 1) ORDER BY Surname"

The above query retreives all the employees that are juniors; Within this dropdown I only want to include in the list employees that are juniors and that are based on a particular floor. I have in the VBA code the floor number.

How can I incorporate the VB Code (or a field on the form) into the Row Source Query?

 

[PHV]

The RowSource property may be a whole SQL instruction instead of a query name ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[carlp69]

The Row Source is in as an instruction and not a query name (ie typed in!)
How do I include the Variable / from field into this statement?

 

[PHV]

Something like this ?
Me![combo name].RowSource = "SELECT DISTINCT EmployeeID, Surname, FirstName, Junior FROM tbl_Employees WHERE Junior=1 AND Floor=" & intFloor & " ORDER BY Surname"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[carlp69]

Tried this but not getting any data in dropdown;

Private Sub JuniorName_Click()
Me!JuniorName.RowSource = "SELECT DISTINCT EmployeeID, Surname, FirstName, Junior, deptid FROM tbl_Employees WHERE (Junior = 1) ORDER BY Surname"
Me!JuniorName.ReQuery
End Sub

This query definetly works when entered onto the Row Source property of the form

 

[PHV]

Have changed the ColumnCount property to 5 ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[carlp69]

Sorted it.

The problem was with the Private Sub juniorName_Click(), it was not executing the code. I moved the code to the form load and the query now works.