pass user inputbox value to filter records using query and display on a form in access 2007

i have a form that is having fields from a query. what i am looking for is , to have user click on a button to enter a string, accept that as the criteria and filter and display only those records on the form.

I am using access 2007.
LVL 11
junaidITManager, ICT ServicesAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Assuming your Form is based on the records you wish to filter:

Dim sInput As String

sInput = InputBox("Enter your value: ")

If Len(sInput) > 0 Then
  Me.Filter = "SomeField='" & sInput & "'"
  Me.FilterOn = True
End If

This assumes that you're filtering on a Text value.

If filtering on a Numeric:
  Me.Filter = "SomeField=" & sInput

If filtering on a Date:
  Me.Filter = "SomeField=#" & sInput & "#"

junaidITManager, ICT ServicesAuthor Commented:

totally new to the programming  or even for that matter any sort of coding

may be if i tell the scenario, that will make it more clear.

i have a table wth one of the fields called dealcode(defined as plain text)

now what i want is , when  a user clicks on a command button which is on a separate form, , he gets a input box , where he could enter the text(which is equivalent or similar to the field dealcode) and this gets passed onto a query as a criteria, where all the fields from the query are displayed on the form)

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
First: You don't need to pass anything into the query. You can "filter" the form your query is based on using several methods. If you're going to be opening the form, the simplest way is to use the OpenForm method, which provides an argument where you can supply a valid WHERE clause, and Access will automatically filter your records based on that.

Is the form you want to show based on your query? If it is, then you can use code like this in the Click Event of your button:

Sub MyButton_Click()

Dim sInput As String

sInput = InputBox("Enter your value: ")

If Len(sInput) > 0 Then
  DoCmd.OpenForm "YourFormName", , , "dealcode='" & sInput & "'"  
  Msgbox "You didn't enter anything"
End If

End Sub

To add this code, open the form with the button in Design view and select that Button. From the Properties dialog, click the Events tab, then find the Click event. Select "Event Procedure" from the dropdown, then click the Build button (the button to the right of the box). This will take you to the VBA Editor, and Access will have the Event "stub" in place for you (i.e the "Sub YourButtonName_Click()" and "End Sub" lines). Copy/paste everything from "Dim sInput As String" to "End Sub" between the two lines Access inserted for you.

You'll have to change the name of "YourFormName" to the form you want to show. Assuming that form is based on the query, Access should show you the data from that query, filtered by the value from the InputBox function.

See the Help file for more information on the OpenForm method.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

junaidITManager, ICT ServicesAuthor Commented:

tried your code. however what is happening is that , an input box comes twice, first like a normal input box, second as if there is a parameter query running. and then it displays all the records that are there and doesnt filter anything.

the record source for the form is a query that pulls data out of the main table.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You must make certain that you have correctly spelled everything, including the name of the Field in your query.

Is "dealcode" a TEXT or NUMERIC field in the table?

If you open your query directly, do you get a prompt?

take a look at this thread and the filtering code that i used.  Had a similar problem a while back. The code that I had entered will give you the result you are looking for.  In this thread, I go a step further to actually create a report from the filter results.
junaidITManager, ICT ServicesAuthor Commented:
issue fixed. thanks all for the inputs
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.