Creating a Query in VB

How do you created a query in VB?
I have a button on a form that signifies a certain computer in a
computer suite. On clicking on this button i would like to create a
query searching for all details invovling that computer, for the user
to then view.
Any ideas on some code?

Many thanks for any help.

re: Creating a Query in VB

Simon,

Lookup QueryDef in the Help file.


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
resource@pcdatasheet.com
www.pcdatasheet.com


"Simon Bailey" <goatsupreme@hotmail.com> wrote in message
news:1ce64db5.0312111228.664133e5@posting.google.c om...[color=blue]
> How do you created a query in VB?
> I have a button on a form that signifies a certain computer in a
> computer suite. On clicking on this button i would like to create a
> query searching for all details invovling that computer, for the user
> to then view.
> Any ideas on some code?
>
> Many thanks for any help.[/color]

re: Creating a Query in VB

cheat . .
create the query as a normal query , then view the SQL code for it . . copy
this to the clipboard and paste it into your vba code . . you will need to
fiddle it a bit to make it work . . since you must create a final text
string that can be executed with a DoCmd.RunSql command.

cheers
paul g


"Simon Bailey" <goatsupreme@hotmail.com> wrote in message
news:1ce64db5.0312111228.664133e5@posting.google.c om...[color=blue]
> How do you created a query in VB?
> I have a button on a form that signifies a certain computer in a
> computer suite. On clicking on this button i would like to create a
> query searching for all details invovling that computer, for the user
> to then view.
> Any ideas on some code?
>
> Many thanks for any help.[/color]

re: Creating a Query in VB

The SQL for a query is simply text. Given that I don't have any idea what
data you have and what information you want, I couldn't be specific. You can
use the Query Builder to create a Query, switch to SQL view to get a
"headstart" -- writing SQL from scratch is needless effort. You can then
copy that SQL, modify as needed (for example, adding a WHERE clause to limit
the records returned) and use VBA DAO code to execute it.

Larry Linson
Microsoft Access MVP

"Simon Bailey" <goatsupreme@hotmail.com> wrote in message
news:1ce64db5.0312111228.664133e5@posting.google.c om...[color=blue]
> How do you created a query in VB?
> I have a button on a form that signifies a certain computer in a
> computer suite. On clicking on this button i would like to create a
> query searching for all details invovling that computer, for the user
> to then view.
> Any ideas on some code?
>
> Many thanks for any help.[/color]

re: Creating a Query in VB

"Larry Linson" <bouncer@localhost.not> wrote in message news:<w_aCb.2311$rr4.1696@nwrddc02.gnilink.net>...[color=blue]
> The SQL for a query is simply text. Given that I don't have any idea what
> data you have and what information you want, I couldn't be specific. You can
> use the Query Builder to create a Query, switch to SQL view to get a
> "headstart" -- writing SQL from scratch is needless effort. You can then
> copy that SQL, modify as needed (for example, adding a WHERE clause to limit
> the records returned) and use VBA DAO code to execute it.
>
> Larry Linson
> Microsoft Access MVP
>
> "Simon Bailey" <goatsupreme@hotmail.com> wrote in message
> news:1ce64db5.0312111228.664133e5@posting.google.c om...[color=green]
> > How do you created a query in VB?
> > I have a button on a form that signifies a certain computer in a
> > computer suite. On clicking on this button i would like to create a
> > query searching for all details invovling that computer, for the user
> > to then view.
> > Any ideas on some code?
> >
> > Many thanks for any help.[/color][/color]

Thanks for the help. I have tried the DoCmd.RunSQl command. So far I have written:

Private Sub Command0_Click()
DoCmd.RunSQL (SELECT StationName FROM tblproblem WHERE StationName = "H7-11")
End Sub

It doesnt seem to work. Any one have any ideas?

re: Creating a Query in VB

Did you look at the Help for RunSQL? It is for running action queries (e.g.,
delete, append, maketable...) but yours is a Select Query. What is it that
you want to _do_ with the data retrieved from this query?

If it is to edit the information, you might want to consider opening a form
based on the query. You can run a select query with DoCmd.OpenQuery. It
seems rare that you'd want to give a user access to the datasheet view of a
query that you opened from code, though, because there are so many things
the user can inadvertently do wrong from datasheet view.

Larry Linson
Microsoft Access MVP


"Simon Bailey" <goatsupreme@hotmail.com> wrote in message
news:1ce64db5.0312120740.14b47c45@posting.google.c om...[color=blue]
> "Larry Linson" <bouncer@localhost.not> wrote in message[/color]
news:<w_aCb.2311$rr4.1696@nwrddc02.gnilink.net>...[color=blue][color=green]
> > The SQL for a query is simply text. Given that I don't have any idea[/color][/color]
what[color=blue][color=green]
> > data you have and what information you want, I couldn't be specific. You[/color][/color]
can[color=blue][color=green]
> > use the Query Builder to create a Query, switch to SQL view to get a
> > "headstart" -- writing SQL from scratch is needless effort. You can then
> > copy that SQL, modify as needed (for example, adding a WHERE clause to[/color][/color]
limit[color=blue][color=green]
> > the records returned) and use VBA DAO code to execute it.
> >
> > Larry Linson
> > Microsoft Access MVP
> >
> > "Simon Bailey" <goatsupreme@hotmail.com> wrote in message
> > news:1ce64db5.0312111228.664133e5@posting.google.c om...[color=darkred]
> > > How do you created a query in VB?
> > > I have a button on a form that signifies a certain computer in a
> > > computer suite. On clicking on this button i would like to create a
> > > query searching for all details invovling that computer, for the user
> > > to then view.
> > > Any ideas on some code?
> > >
> > > Many thanks for any help.[/color][/color]
>
> Thanks for the help. I have tried the DoCmd.RunSQl command. So far I have[/color]
written:[color=blue]
>
> Private Sub Command0_Click()
> DoCmd.RunSQL (SELECT StationName FROM tblproblem WHERE StationName =[/color]
"H7-11")[color=blue]
> End Sub
>
> It doesnt seem to work. Any one have any ideas?[/color]

re: Creating a Query in VB

"Larry Linson" <bouncer@localhost.not> wrote in message news:<M1sCb.919$cO5.875@nwrddc03.gnilink.net>...[color=blue]
> Did you look at the Help for RunSQL? It is for running action queries (e.g.,
> delete, append, maketable...) but yours is a Select Query. What is it that
> you want to _do_ with the data retrieved from this query?
>
> If it is to edit the information, you might want to consider opening a form
> based on the query. You can run a select query with DoCmd.OpenQuery. It
> seems rare that you'd want to give a user access to the datasheet view of a
> query that you opened from code, though, because there are so many things
> the user can inadvertently do wrong from datasheet view.
>
> Larry Linson
> Microsoft Access MVP
>
>
> "Simon Bailey" <goatsupreme@hotmail.com> wrote in message
> news:1ce64db5.0312120740.14b47c45@posting.google.c om...[color=green]
> > "Larry Linson" <bouncer@localhost.not> wrote in message[/color]
> news:<w_aCb.2311$rr4.1696@nwrddc02.gnilink.net>...[color=green][color=darkred]
> > > The SQL for a query is simply text. Given that I don't have any idea[/color][/color]
> what[color=green][color=darkred]
> > > data you have and what information you want, I couldn't be specific. You[/color][/color]
> can[color=green][color=darkred]
> > > use the Query Builder to create a Query, switch to SQL view to get a
> > > "headstart" -- writing SQL from scratch is needless effort. You can then
> > > copy that SQL, modify as needed (for example, adding a WHERE clause to[/color][/color]
> limit[color=green][color=darkred]
> > > the records returned) and use VBA DAO code to execute it.
> > >
> > > Larry Linson
> > > Microsoft Access MVP
> > >
> > > "Simon Bailey" <goatsupreme@hotmail.com> wrote in message
> > > news:1ce64db5.0312111228.664133e5@posting.google.c om...
> > > > How do you created a query in VB?
> > > > I have a button on a form that signifies a certain computer in a
> > > > computer suite. On clicking on this button i would like to create a
> > > > query searching for all details invovling that computer, for the user
> > > > to then view.
> > > > Any ideas on some code?
> > > >
> > > > Many thanks for any help.[/color]
> >
> > Thanks for the help. I have tried the DoCmd.RunSQl command. So far I have[/color]
> written:[color=green]
> >
> > Private Sub Command0_Click()
> > DoCmd.RunSQL (SELECT StationName FROM tblproblem WHERE StationName =[/color]
> "H7-11")[color=green]
> > End Sub
> >
> > It doesnt seem to work. Any one have any ideas?[/color][/color]

Thanks for your help Larry. Basically, i want to create a query to
view as a report or likewise. I will be clicking on a button that
significies a certain computer in a certain room and the guery will
search and for all previous problems with that computer, the search
will be from tblproblem.
The help file on VBA has been of little help to me unfortunately,
however much i stare at it!!

Thanks again

re: Creating a Query in VB

You can either create the SQL and pick it up in the Open event of the Report
to replace the Report's RecordSource, or store it in the SQL property of the
Query used as the Report's RecordSource; or you can use the
DoCmd.OpenReport's WhereCondition argument to select the particular
information. The first method is somewhat more efficient in all cases, and a
great deal more efficient in a client-server environment; it is also a great
deal more efficient in a split-database over-the-network multiuser
environment IF the criteria fields are indexed.

In any case, you will not run the query... you'll just use it as the
RecordSource of the Report, or the "WHERE clause without the WHERE" as the
WhereCondition of DoCmd.OpenReport.

Good luck.

Larry Linson
Microsoft Access MVP
"Simon Bailey" <goatsupreme@hotmail.com> wrote in message
news:1ce64db5.0312130130.4b2cdbd6@posting.google.c om...[color=blue]
> "Larry Linson" <bouncer@localhost.not> wrote in message[/color]
news:<M1sCb.919$cO5.875@nwrddc03.gnilink.net>...[color=blue][color=green]
> > Did you look at the Help for RunSQL? It is for running action queries[/color][/color]
(e.g.,[color=blue][color=green]
> > delete, append, maketable...) but yours is a Select Query. What is it[/color][/color]
that[color=blue][color=green]
> > you want to _do_ with the data retrieved from this query?
> >
> > If it is to edit the information, you might want to consider opening a[/color][/color]
form[color=blue][color=green]
> > based on the query. You can run a select query with DoCmd.OpenQuery. It
> > seems rare that you'd want to give a user access to the datasheet view[/color][/color]
of a[color=blue][color=green]
> > query that you opened from code, though, because there are so many[/color][/color]
things[color=blue][color=green]
> > the user can inadvertently do wrong from datasheet view.
> >
> > Larry Linson
> > Microsoft Access MVP
> >
> >
> > "Simon Bailey" <goatsupreme@hotmail.com> wrote in message
> > news:1ce64db5.0312120740.14b47c45@posting.google.c om...[color=darkred]
> > > "Larry Linson" <bouncer@localhost.not> wrote in message[/color]
> > news:<w_aCb.2311$rr4.1696@nwrddc02.gnilink.net>...[color=darkred]
> > > > The SQL for a query is simply text. Given that I don't have any idea[/color]
> > what[color=darkred]
> > > > data you have and what information you want, I couldn't be specific.[/color][/color][/color]
You[color=blue][color=green]
> > can[color=darkred]
> > > > use the Query Builder to create a Query, switch to SQL view to get a
> > > > "headstart" -- writing SQL from scratch is needless effort. You can[/color][/color][/color]
then[color=blue][color=green][color=darkred]
> > > > copy that SQL, modify as needed (for example, adding a WHERE clause[/color][/color][/color]
to[color=blue][color=green]
> > limit[color=darkred]
> > > > the records returned) and use VBA DAO code to execute it.
> > > >
> > > > Larry Linson
> > > > Microsoft Access MVP
> > > >
> > > > "Simon Bailey" <goatsupreme@hotmail.com> wrote in message
> > > > news:1ce64db5.0312111228.664133e5@posting.google.c om...
> > > > > How do you created a query in VB?
> > > > > I have a button on a form that signifies a certain computer in a
> > > > > computer suite. On clicking on this button i would like to create[/color][/color][/color]
a[color=blue][color=green][color=darkred]
> > > > > query searching for all details invovling that computer, for the[/color][/color][/color]
user[color=blue][color=green][color=darkred]
> > > > > to then view.
> > > > > Any ideas on some code?
> > > > >
> > > > > Many thanks for any help.
> > >
> > > Thanks for the help. I have tried the DoCmd.RunSQl command. So far I[/color][/color][/color]
have[color=blue][color=green]
> > written:[color=darkred]
> > >
> > > Private Sub Command0_Click()
> > > DoCmd.RunSQL (SELECT StationName FROM tblproblem WHERE StationName =[/color]
> > "H7-11")[color=darkred]
> > > End Sub
> > >
> > > It doesnt seem to work. Any one have any ideas?[/color][/color]
>
> Thanks for your help Larry. Basically, i want to create a query to
> view as a report or likewise. I will be clicking on a button that
> significies a certain computer in a certain room and the guery will
> search and for all previous problems with that computer, the search
> will be from tblproblem.
> The help file on VBA has been of little help to me unfortunately,
> however much i stare at it!!
>
> Thanks again[/color]