Filter query based on the combobox/textbox

[Sandy1992]

Hi friends,

I am using A2010. I have completed preparing the whole application but stuck with reports.

what I want to do:

There is 1 command button and 1 textbox/combobox(happy if works with any of the control) . When I type employeeId(which is numeric) in that control and click on command button, it should filter the query and display the result in report. It sounds easy , but not for me. I tried lots of ways to get it done. But from past 6 days stuck with this.

I am using query to fetch data in unbound combobox.

SELECT tblUserqry.[EmployeeId] FROM tblUserqry;

And below criteria in query:

Like [Forms]![Form3]![EmpID]

'EmpID is name of combobox

At last below Macro for onclick , that opens the query in datasheet .


Please note that I don't know how filter works in ms access. All types of suggestions are accepted.

I need to complete it ASAP.

Please help.

Looking forward for participations.

[jimatqsi]

Sandy1992,
First, let me quibble with your wording. You say you are stuck on 'reports' but you then describe a problem with opening a query. Viewing query results is not viewing a report; they are different things. I think there is no report involved. Please correct me if I am wrong.

Further, you don't tell what the problem is. You have button that runs this macro. The macro opens a query. It is not clear what the name of that query is. You describe this other(?) query:

SELECT tblUserqry.[EmployeeId] FROM tblUserqry; 

and then you describe a where clause

Like [Forms]![Form3]![EmpID]

Is that where clause part of the combobox query? That wouldn't make sense, so I think it must be part of the query the macro opens. And that looks pretty good, like it should work.

What happens when you click the button? You don't tell what result you are getting.

I think what you need to do is pretty simple. Getting help "asap" on this website should not be a problem if you will restate your problem clearly and completely. Give us more detail.

And congratulations on sticking with it. Six days fighting a program is frustrating.

Jim

[Sandy1992]

Thanks a lot for your response. But I solved my problem :)

[zmbd]

Sandy1992,
Very happy you solved your problem; however, perhaps you would be willing to share so that others may potentially benefit from your experience?

[Sandy1992]

Yeah Sure,

I will more than happy if I can help. :)

what I did is simple.

I used combobox to fetch the data to filter my form.

In Property window, under Row Source I used

SELECT [AddUser].[ID], [AddUser].[EmployeeId] FROM AddUser; 

This way my combobox was displaying the data which I wanted.

Now, I used a subform to show the filtered result in it.

I created a query and on the field I really wanted to filter I used :

Like [Forms]![Searchbyemployeeid]![EmpID]

And lastly, on Combobox -> after update I used code :

Private Sub cboEmployee_AfterUpdate()
 Dim Myemp As String
    Myemp = "select * from Tasks where ([EmployeeId] =" & Me.cboEmployee & ")"
    Me.Tasks_subform.Form.RecordSource = Myemp
    Me.Tasks_subform.Requery
 
 
End Sub

Try it out. Simple and fun :)

[zmbd]

thnx you.... you might take a look at our Insights articles. There are several examples of filtering methods:

• Microsoft Access / VBA Insights Sitemap
• How to select Records in an Access Subform from a Combo Box
• Cascaded Form Filtering
• Multiple Combobox Filter with Clear Option
• Example Filtering on a Form.