Beginner here- Can I query with a form?

[timpu]

Hi Everyone,
I'm kind of new to access and am trying to figure something out. I have a database, and I've made a query to bring back data from specified fields from different tables. What I'd like to do now is create a form that lets me input search criteria. I was hoping this should be easy, but I can't seem to figure it out. Do I need to actually write code myself? All I want to do is have 2 search text buttons, a submit button, and then an exit button. Unfortunately I'm stuck on all accounts here. If anyone can give a pointer on what I should look to, I would really appreciate it. Thanks everyone for helping out a beginner

 

[CraigWarmy]

Yes you can do it. It is actually called query-by-form. Make an unbound form with the text box(s) that hold the criteria for your qbf. After that make a query that holds all of the data that you need and also put in some criteria in the criteria row. Should look like [forms]![frmInputForm]![CustomerName].

Should work great for you.

 

[timpu]

Yes you can do it. It is actually called query-by-form. Make an unbound form with the text box(s) that hold the criteria for your qbf. After that make a query that holds all of the data that you need and also put in some criteria in the criteria row. Should look like [forms]![frmInputForm]![CustomerName].

Should work great for you.

Thanks Craig. I'm still a bit clueless, so I appreciate the help.
I think I may be missing something here. I created a form. I'll call it QRT_form. In making the form I connected it to the query where it says "choose the table or query where the object's data comes from". Does that now mean that it's no longer "unbound" ?

Anyways, I went to this query and in the criteria I put what you recommended above, [forms]![frmInputForm]![CustomerName], however I changed it to work with the newly created form, so would the criteria be this? [QRT_form]![frmInputForm]![City].

So now when I run this new form, I have the Enter Parameter Value box come up and it says QRT_form!frmInputFrom!City above the text box. Is there a way to make that disappear? After I put a city in and hit ok, I get all the fields of the form I created, but I have a scroll bar on the bottom to toggle between all the entries. I'd rather have a list of all the entries on one page. Is there a way to make this happen?
Also, when the first Enter Parameter Value box comes up when I initialize the form, is there a way I can customize that?

Thanks again for your help, Tim

 

[pbaldy]

You almost got it. "Forms" should stay the same. You replace "frmInputForm" with your form's name.

 

[CraigWarmy]

I also use comboboxes for using criteria. You should also use continious forms on your form properties if your not using them yet.

 

[timpu]

Thanks for your help. I'm still not getting it though. I changed the criteria in the query so it reads [forms]![QRT_form]![City]. City is the name of field I want filter from, and QRT_form is the form I built.

Now however, I'm not sure how to build this QRT_form.

I want it to to have(for right now) just one field I can type in and have a query run. When the query runs, I want all 17 fields from the query to display. No matter what I've done though to create this form, it doesn't happen. I'm completely missing something here. Is there a chance you might be able to walk me through the best way to create this form?

Should I create a new one in design view, in form wizard? Should I at this point choose the query to get the info from? It seems I'm trying all these different ways and nothing is working for me.

What I want to happen is have a form come up that says something like "enter city". Once I enter it, I would then like to see all 617 results pop up with all 17 fields - just like if I ran the query with parameters without a form. Does this make sense? God I'm a beginner. Thanks for any help.

 

[CraigWarmy]

Make sure you have the form which will show the results. Have the results form refer to the forms recordsource.

Second, the input form should not refer to anything. Just have your comboboxes set to list the cities in the table that you already have for by using a combobox.

Third, make sure the criteria in the combobox in the query has that run's on the input box has unique values. You set that by going to the query and right clicking on the query(where the tables are) and then play around with the "Unique Values" and "Unique Records". I hope can get it from here.

 

[timpu]

Thanks again for your help. I'm still stupid though and have more questions. From what you listed to do....


Make sure you have the form which will show the results.

How do I make sure of that?

Have the results form refer to the forms recordsource.

And how do I do this?

Second, the input form should not refer to anything. Just have your comboboxes set to list the cities in the table that you already have for by using a combobox.

So I just created a basic form with a combobox. I just manually added two cities that I would like to search from first, but I did nothing else except save it as QRT_form. I didn't go into properties and change anything or point it to anything.

Third, make sure the criteria in the combobox in the query has that run's on the input box has unique values. You set that by going to the query and right clicking on the query(where the tables are) and then play around with the "Unique Values" and "Unique Records". I hope can get it from here.

I did change "Unique Values" to yes on the Query.

Thanks again for all your help.