How do I run a Query from a form?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Can anyone point me to a simple tutorial or description of how an Access
query can be ran from a form and the results displayed in the form?

(I know how to build the basics of the form and lay down controls but to
make them function and reference the query is the part I am stuck on)

Thank you!

Quin
 
You can use code to set the form's record source to a query.

Forms!frmYourForm.RecordSource="qselYourQuery"

It would really help if we knew why you were attempting to create something
dynamic like this.
 
I also replied to your other post.

It sounds to me like you have created a query under the query tab and now
want to use it in a form.

If that is the case you go to that form in design view and open the
properties window and under the Data Tab you change the Record Source to
that query. - the down arrow button. If you use the wizards to create a
new form you can also select a query as the basis for the record source.
Then you can folow the direction for the command button to show the
records you want.
--
Craig Hornish

Visit weekly Access conferences
Tuesday, 11:00am-12:30pm (Eastern US)
Thursday, 3:30pm- 5:00pm (Eastern US)

http://community.compuserve.com/n/pfx/forum.aspx?webtag=ws-msdevapps

"Think outside the box, because anything is possible."
"How long it will take or whether it requires divine intervention is another
issue"
 
Yes that is correct. I have created a query under the query tab and now I
want to use it in a form. To do that is no problem for me but what I am
really asking is how do I supply criteria to the query that will then be
processed to produce the results of the query in the form. For example I
have a "like" statement in my querry. This like statement can accept any
criteria. I want to have a control on my form where a user can enter the
required criteria and then the results would be shown on the form.

I know how to link a form to a query and see results. I do NOT know how to
set up form controls to insert criteria into the query to filter the results
displayed in the form as desired.

Maybe I can make this clear with a hypothetical and very simple example:

I have a table that lists buyers and sellers. I design a query that if the
criteria =buyers it will list all the buyers. If the criteria =sellers it
will list all the sellers. OK I test this and it works but I will never get
my wife or sister or mother to understand how work under the query tab. So
my next step is to design a form that has a text box or a check box or what
ever other control that will supply the value to the query to list just the
buyers or just the sellers in the form.

I want to be able to check a box or enter a value into a form control that
will then display the list of buyers. Or, using that same form change the
value inputted and show just the sellers.

I am really stuck and will appreciate any information on this. My true goal
is more complex than this but knowing the basics behind this will take me a
long way toward geting the knowledge I am looking for.
 
Yes that is correct. I have created a query under the query tab and now I
want to use it in a form. To do that is no problem for me but what I am
really asking is how do I supply criteria to the query that will then be
processed to produce the results of the query in the form. For example I
have a "like" statement in my querry. This like statement can accept any
criteria. I want to have a control on my form where a user can enter the
required criteria and then the results would be shown on the form.
Click to expand...

Use a criterion such as

=[Forms]![NameOfYourForm]![NameOfTheTextbox]

or (for wildcard operation)

LIKE "*" & [Forms]![NameOfYourForm]![NameOfTheTextbox] & "*"

The control (which I'm calling NameOfTheTextbox) should be an unbound
control on the form; it works whether the form is bound to the query
or is an unbound form. If it's bound to the query you need to Requery
the form in the textbox's AfterUpdate event; if it's a different form,
open the bound form in the same event.

John W. Vinson[MVP]
 
Thank You John,

I think your answer is putting me on the right track for what I want to do.
If I understand correctly my critera for the query will be:

LIKE "*" & [Forms]![NameOfYourForm]![NameOfTheTextbox] & "*"

Then I will set up the form to be bound to that query.

Inside that form I will have an unbound control (text box) where the user
can input the wildcard information.

To set up the text box located in the form I will need to go to properties,
find the "after update" event procedure and set up a requery.

Here is where I am stuck. I found the "after update" section but I do not
know how to set up the requery. I see [event procedure] where I must enter
some code to create the requery. What do I type for the "event procedure"?
Do I need some sort of path or criteria to describe the name of the query
that is to be requeried? If so, what does that line of code look like?

Once the requery is set up I suspect the user would need to know to enter
his/her wildcard information into the text box and then hit the enter key to
activate the requery? Is there an easy way to set up a "submit" button for
users that do not know they must hit "enter" on the keyboard to submit their
wildcard requests?





John Vinson said:
Yes that is correct. I have created a query under the query tab and now I
want to use it in a form. To do that is no problem for me but what I am
really asking is how do I supply criteria to the query that will then be
processed to produce the results of the query in the form. For example I
have a "like" statement in my querry. This like statement can accept any
criteria. I want to have a control on my form where a user can enter the
required criteria and then the results would be shown on the form.
Click to expand...

Use a criterion such as

=[Forms]![NameOfYourForm]![NameOfTheTextbox]

or (for wildcard operation)

LIKE "*" & [Forms]![NameOfYourForm]![NameOfTheTextbox] & "*"

The control (which I'm calling NameOfTheTextbox) should be an unbound
control on the form; it works whether the form is bound to the query
or is an unbound form. If it's bound to the query you need to Requery
the form in the textbox's AfterUpdate event; if it's a different form,
open the bound form in the same event.

John W. Vinson[MVP]
Click to expand...
 
Here is where I am stuck. I found the "after update" section but I do not
know how to set up the requery. I see [event procedure] where I must enter
some code to create the requery. What do I type for the "event procedure"?
Do I need some sort of path or criteria to describe the name of the query
that is to be requeried? If so, what does that line of code look like?
Click to expand...

Click on the ... icon by the [Event Procedure] line, after selecting
that event. Choose Code Builder if you're offered a list of options.

The code would be something like

Private Sub NameOfTheTextbox_AfterUpdate()
Me.Requery
End Sub
Once the requery is set up I suspect the user would need to know to enter
his/her wildcard information into the text box and then hit the enter key to
activate the requery? Is there an easy way to set up a "submit" button for
users that do not know they must hit "enter" on the keyboard to submit their
wildcard requests?
Click to expand...

Well... you have to do SOMETHING to teach users how computers work.
The telepathic user interface will be coming out in 2019 if all goes
well...

All I can suggest is putting a label on the form next to the textbox
with instructions like

Enter the word that you want to search for in the box right here >>
and then press the Enter key on your keyboard.

or whatever will communicate to your users.

John W. Vinson[MVP]
 
I found the code builder, I built the code, it came out exactly like you
said.
After the code "is built" how is is hooked up to the after update event? Do
I need to name it or something? All I see on the line is Macro 1 or Macro 2
or [event procedure]

John Vinson said:
Here is where I am stuck. I found the "after update" section but I do not
know how to set up the requery. I see [event procedure] where I must enter
some code to create the requery. What do I type for the "event procedure"?
Do I need some sort of path or criteria to describe the name of the query
that is to be requeried? If so, what does that line of code look like?
Click to expand...

Click on the ... icon by the [Event Procedure] line, after selecting
that event. Choose Code Builder if you're offered a list of options.

The code would be something like

Private Sub NameOfTheTextbox_AfterUpdate()
Me.Requery
End Sub
Once the requery is set up I suspect the user would need to know to enter
his/her wildcard information into the text box and then hit the enter key to
activate the requery? Is there an easy way to set up a "submit" button for
users that do not know they must hit "enter" on the keyboard to submit their
wildcard requests?
Click to expand...

Well... you have to do SOMETHING to teach users how computers work.
The telepathic user interface will be coming out in 2019 if all goes
well...

All I can suggest is putting a label on the form next to the textbox
with instructions like

Enter the word that you want to search for in the box right here >>
and then press the Enter key on your keyboard.

or whatever will communicate to your users.

John W. Vinson[MVP]
Click to expand...
 
I found the code builder, I built the code, it came out exactly like you
said.
After the code "is built" how is is hooked up to the after update event? Do
I need to name it or something? All I see on the line is Macro 1 or Macro 2
or [event procedure]
Click to expand...

It should say [Event Procedure]. That means it IS hooked up - when the
event is triggered (say by updating the control on the form), that
event procedure will be executed. To see the details of the event
procedure, click the ... icon - but you won't see it on the form
itself.

John W. Vinson[MVP]
 
I got the Code in and it works... Sort of... I am finding that there is
something slightly wrong. Sometimes you have to click outside the control
box to reset it for the next input. I guess there is no good way of doing
the good stuff in Access without learning some code. I just may have to bite
the bullet and learn code!

If you want to see the simple "Names" access file I created that will look
up any ones name by typing part of their first name or part of their last
name you can go to:

http://members.cox.net/cquin2/names.mdb

It will download from there. It is 172 kb and I will keep it posted there
until September 2006. Probably I just need a slight tweek in the code to
make it work better.

PS: I know I named the form poorly. It would not let me change it after it
was set up.


John Vinson said:
I found the code builder, I built the code, it came out exactly like you
said.
After the code "is built" how is is hooked up to the after update event? Do
I need to name it or something? All I see on the line is Macro 1 or Macro 2
or [event procedure]
Click to expand...

It should say [Event Procedure]. That means it IS hooked up - when the
event is triggered (say by updating the control on the form), that
event procedure will be executed. To see the details of the event
procedure, click the ... icon - but you won't see it on the form
itself.

John W. Vinson[MVP]
Click to expand...
 
Sure I'm interested but I need code that works! : )

Anyway John... Thanks for taking me this far. I learned a lot in the last
week and I have the basics. I really liked the help you gave me with the
"like" statement and I was glad to at least see how a project like this
"might" be put together. I will keep working at it and asking questions and
over the next few months and years I am sure something good will come.
 
Back
Top