Create Query from Form input

[musesshadow]

I've read all over the microsoft site and this one to try and find the answer to this. I've tried a number of solutions, but none of them actually work. I'm hoping someone can suggest something.

I have a form that displays information about hardware

Hardware-----Budget Year-----Price

I want to be able to look at all hardware items for one year, so on the form I also have an unbound text box where people can enter the year they're interested in.

I want to enter a year, push an update button, and have the form display the appropriate records (i.e. if the person enters 2004 as the budget year, only records from 2004 will show up for entry/modification on the form).

I've tried references to Forms![formName ]![txtBoxName ] but it always asks me for a Parameter when I open the form. This also happens when building the query dynamically or opening another form for the input. I have checked the name of my text box to ensure I am stating the right name, but to no avail.

This is enough to drive a guy batty.

 

[Ukraine82]

Make a query based on a table. In the criteria for the first field, enter:

Like“*”&[Forms]![Myform]![txtSearchCriteria]&”*”

Replace “MyForm” with whatever the name of your form that you wish to insert your Search Field.

Save the Query to any name you wish. In form a text box control, and give it the name txtSearchCriteria.

In the txtSearchCriteria field, add the formula below in After Update event:

Private Sub txtSearchCriteria_AfterUpdate()
DoCmd.OpenQuery "MyQuery"
End Sub

hth,

Michael

Type what you’re looking for and hit enter.

 

[sfreeman@co.mer]

Or...

Or, you can try this. This is the datasource for a form with a command button that simply does a "Me.Requery"

SELECT
DatePart("yyyy",[Date])

AS
DateField
, tblOne.Field2
, tblOne.Field4
, tblOne.Field3

FROM
tblOne

WHERE
(((DatePart("yyyy",[Date]))=DatePart("yyyy",[Forms]![frmTest]![DateField])));

 

[mattP]

Could you use a similar code for two search criteria . I need to run a query to look at all calls logged in a financial quarter or between two dates, so could this be used to look between two differnet calender dates ?
so for Qtr1 it would be 1st April 2004 - 30th June 2004 ?

 

[EMP]

Jon K posted an example in this thread.
http://www.access-programmers.co.uk/forums/showthread.php?t=72964

It may help.

 

[Zooropa]

Make a query based on a table. In the criteria for the first field, enter:

Like“*”&[Forms]![Myform]![txtSearchCriteria]&”*”

Replace “MyForm” with whatever the name of your form that you wish to insert your Search Field.

Save the Query to any name you wish. In form a text box control, and give it the name txtSearchCriteria.

In the txtSearchCriteria field, add the formula below in After Update event:

Private Sub txtSearchCriteria_AfterUpdate()
DoCmd.OpenQuery "MyQuery"
End Sub

hth,

Michael

Type what you’re looking for and hit enter.

I have something very similar to this, but when my form loads, the form is completely blank. No text boxes visible, just a big white box. I have feeling it's because it's pointed at an empty query?

I have a SearchBox, with a search button next to it. I enter an ID number in the box, and hit search. The button should open the query, run it for the value in the box, and display it in the fields in my form.
It just doesn't work though

 

[ecniv]

An unbound form for searching, then a new form for results (put in a default query which returns nothing?)

On the button click, get the sql statement for returning everything you want, hold it iin a string, then add a where clause with the data typed into the text field. Then open the results form and set the rowsource to the sql statement you've just created and requery the form.

Thats for the bound method. Similar but you could use a listbox to display the results.


I personally don't like the bound methods especially in a networked environment whilst developing/upgrading dbs as its a pain to make sure everyone is out of hte db to make the changes just my view.


Vince

 

[musesshadow]

Thanks for all the suggestions people, but I actually found an easier solution after *head desk*ing all afternoon.

I created a form on the table with all of the information, and then used it as a subform where the ChildLink Field was the year and the ParentLink Field was the name of the unbound text box. The naming conventions for things took a little getting used to (especially coming from a background as a Java coder where everything's just this.that) but I figured it out eventually.

Nice to see everyone's so willing to help.

The ideas about OnUpdate and stuff were however really useful!

Thanks again.