By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,483 Members | 1,039 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,483 IT Pros & Developers. It's quick & easy.

Creating a Query in VB

P: n/a
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.
Nov 12 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
Simon,

Lookup QueryDef in the Help file.
--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"Simon Bailey" <go*********@hotmail.com> wrote in message
news:1c**************************@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.

Nov 12 '05 #2

P: n/a
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" <go*********@hotmail.com> wrote in message
news:1c**************************@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.

Nov 12 '05 #3

P: n/a
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" <go*********@hotmail.com> wrote in message
news:1c**************************@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.

Nov 12 '05 #4

P: n/a
"Larry Linson" <bo*****@localhost.not> wrote in message news:<w_*****************@nwrddc02.gnilink.net>...
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" <go*********@hotmail.com> wrote in message
news:1c**************************@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.


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?
Nov 12 '05 #5

P: n/a
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" <go*********@hotmail.com> wrote in message
news:1c**************************@posting.google.c om...
"Larry Linson" <bo*****@localhost.not> wrote in message news:<w_*****************@nwrddc02.gnilink.net>...
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" <go*********@hotmail.com> wrote in message
news:1c**************************@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.


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?

Nov 12 '05 #6

P: n/a
"Larry Linson" <bo*****@localhost.not> wrote in message news:<M1***************@nwrddc03.gnilink.net>...
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" <go*********@hotmail.com> wrote in message
news:1c**************************@posting.google.c om...
"Larry Linson" <bo*****@localhost.not> wrote in message

news:<w_*****************@nwrddc02.gnilink.net>...
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" <go*********@hotmail.com> wrote in message
news:1c**************************@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.


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?


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
Nov 12 '05 #7

P: n/a
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" <go*********@hotmail.com> wrote in message
news:1c**************************@posting.google.c om...
"Larry Linson" <bo*****@localhost.not> wrote in message

news:<M1***************@nwrddc03.gnilink.net>...
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" <go*********@hotmail.com> wrote in message
news:1c**************************@posting.google.c om...
"Larry Linson" <bo*****@localhost.not> wrote in message

news:<w_*****************@nwrddc02.gnilink.net>...
> 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" <go*********@hotmail.com> wrote in message
> news:1c**************************@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.

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?


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

Nov 12 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.