Query and VBA

[Tech]

Hi again.

I want to know, I have made a query and a form of that query. I am wanting to know, is there a way (I know, use the Form Load event) in VBA where you enter the the criteria to search for and it does the search? I know, it would be something like:

sub form_load()

dim searchwhat as string

searchwhat = inputbox("What Customer number would you like to search for?")

But after that input box, how can I tell it to tell the query to go do that search on the searchwhat variable? Is it possible?

I thought maybe u can do something like:

Query![query name]![Field]!

![Criteria] = searchwhat or something like that??

 

[Jack Cowley]

Create a form with an unbound text box and a command button that opens the form you made that is bound to your query. Lets name this new unbound form frmSearch and save it once you have named the unbound text box txtSearch and have the code behind the command button to open your form. Now go to your query and put this line of code in the Criteria line of the field you want to filter on:

[Forms]![frmSearch]![txtSearch]

Save the query. Now open your frmSearch, enter the criteria into the txtSearch control and click your command button. Your form will open and will display the filtered records.

hth,
Jack

 

[Tech]

that is great explanation, tyvm

ok, I have done as u said, except that the form has all the details in it, the form is made from that query. in the query, in the order date section, I entered this in the criteria:

[Forms]![daily order log]![txtSearch]

and I made the unbound txtsearch text box.

so now, on form load event I would put in:

dim searchwhat as string

searchwhat = inputbox("Please enter date in format dd/mm/yyyy")

then what do I put in after that in the code?

 

[Jack Cowley]

Delete all the code in the On Load event of the form. You do not need it. The form will open showing the filtered data with just the form I suggested and the query and the form based on the query....

hth,
Jack

 

[Tech]

thank-you

this is what I did:

put in the command button, follow the wizard for

"Form operations > open form"

then I told it to open the Daily log record form

then I selected to open the form to find specific data

then I selected the txtSearch and the Order Date from the other list box and hitted the <-> button, then hitted next and finished the wizard.

then when I run this new form and type in, lets say:

20/02/2003 and hit the button, the form opens but no records show! If I take out the criteria I entered in the Query and type in that date, the records appear!

y?

 

[Tech]

it works..sorted.

TY!!

 

[Jack Cowley]

let me try it this way... You have two forms and one query. I will call them frmSearch, frmResults and qryResults.

frmSearch will have an unbound text box named txtSearch and a command button. The code in the command button will say:

DoCmd.OpenForm "frmResults"

That is all there is to frmSearch.

frmResults is the form you created using the query qryResults as the Record Source.

qryResults will have this line of code in the Criteria line of the field you want to filter on:

[Forms]![frmSearch]![txtSearch]

That is it for the query.

No if you open frmSearch and type in a date then click the command button frmResults will open showing the record(s) that have the date you typed into the frmSearch. Do not close frmSearch when you open frmResults.

Jack