unable to filter access database using form control (1 Viewer)

i wanna filter a access database table by getting the value from a combo box

need help in this...

my code is :
Option Compare Database
Option Explicit
Dim fac_name As String




Private Sub Facility_Name_Click()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSelect As String

Set db = CurrentDb

Set qdf = db.QueryDefs("userFilter")


fac_name = Facility_Name.Text

MsgBox ("You selected " & fac_name & "; please bear with us to search your unit records")

strSelect = "SELECT factories_annual_return.[Facility Name] FROM factories_annual_return WHERE factories_annual_return.[Facility Name] = '" & fac_name & "';"

DoCmd.SetWarnings False
DoCmd.OpenQuery "userFilter", acNormal, acEdit
DoCmd.SetWarnings True

End Sub

remove the msgbox, it modal so your code will not proceed immediately,

open userFilter query in design mode.
add a Criteria on Facility Name field:

Criteria: Forms!yourFormName!Facility_Name

then your code will be:


Private Sub Facility_Name_Click()
DoCmd.OpenQuery "userFilter", acNormal, acEdit
End Sub

**replace yourFormName with the name of your form.