[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

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

Posted on 2010-04-01
7
Medium Priority
?
687 Views
Last Modified: 2013-11-28
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.
0
Comment
Question by:junaidIT
  • 3
  • 3
7 Comments
 
LVL 85
ID: 29328446
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 & "#"

0
 
LVL 11

Author Comment

by:junaidIT
ID: 29329705
apologies,

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)

thanks
0
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 400 total points
ID: 29331825
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 & "'"  
Else
  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.

0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
LVL 11

Author Comment

by:junaidIT
ID: 29337772
hi,

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.
0
 
LVL 85
ID: 29381117
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?
0
 

Assisted Solution

by:LadyHagood
LadyHagood earned 400 total points
ID: 29392265
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_24293025.html

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.
0
 
LVL 11

Author Comment

by:junaidIT
ID: 29403376
issue fixed. thanks all for the inputs
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

590 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question