Help me Toubleshoot SQL cmd, please

[Grub]

I have a form that contains a list box (lstSearch). In the AfterUpdate
event procedure of lstSearch I would like to execute a query. I built
this query in Access and then copied and pasted the SQL into the VB
event procedure. My problem is this: When I update the listbox, VB
throws me an error saying 'RunSQL must execute an SQL statement'. Why
am I recieveing this error? Here is my code:

Private Sub lstSearch_AfterUpdate()

'Set SQL from Queries as Variables
Dim SQLtxtDescription As String
Dim SQLtxtSolution As String
Dim SQLtxtNotes As String
Dim SQLtxtFieldContact As String

If lstSearch.Value = "Problem Description" Then
msgBox "It Worked", 0, "My Test Box"

SQLtxtDescription = "SELECT tblMain.NDate, tblMain.Key,
tblMain.FieldContact, tblMain.PTCContact, tblMain.Equipment,
tblMain.FailureType, tblMain.FailureName, tblMain.LocationName,
tblMain.ProblemDescription, tblMain.ProblemSolution, tblMain.Notes,
tblMain.Resolved FROM tblMain WHERE(((tblMain.ProblemDescription) Like
""*"" & [Enter a Keyword] & ""*""))"

DoCmd.RunSQL (SQLtxtDescription)

End If

End Sub

[Rick Brandt]

"Grub" <Gr****@gmail.comwrote in message
news:11**********************@s34g2000cwa.googlegr oups.com...

>I have a form that contains a list box (lstSearch). In the AfterUpdate
event procedure of lstSearch I would like to execute a query. I built
this query in Access and then copied and pasted the SQL into the VB
event procedure. My problem is this: When I update the listbox, VB
throws me an error saying 'RunSQL must execute an SQL statement'. Why
am I recieveing this error? Here is my code:

RunSQL is only for "Action Queries". Queries that APPEND, UPDATE, or DELETE.
It cannot be used for SELECT queries.

What exactly do you want to have happen? Do you want a query datasheet to
appear on the screen? If so, then you need to open a saved query. You cannot
display a code generated SQL statement unless you create a saved QueryDef from
the SQL and then open that.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[Grub]

What I have is a query that Searches a particular field using a Keyword
that the user inputs. I implemented this using the this statement in
the Criteria:

Like "*" & [Please Input a Keyword to Search For:] & "*"

My overall goal is to have the user select from the list box the field
to search, then the Afterupdate procedure will determine what the value
was for the field selected (using the IF,THEN,ELSE I have in my code),
run that query for that particular field, and the query asks the user
what word to search for.

Thanks for the help BTW

Grub

[Rick Brandt]

"Grub" <Gr****@gmail.comwrote in message
news:11**********************@42g2000cwt.googlegro ups.com...

>
What I have is a query that Searches a particular field using a Keyword
that the user inputs. I implemented this using the this statement in
the Criteria:

Like "*" & [Please Input a Keyword to Search For:] & "*"

My overall goal is to have the user select from the list box the field
to search, then the Afterupdate procedure will determine what the value
was for the field selected (using the IF,THEN,ELSE I have in my code),
run that query for that particular field, and the query asks the user
what word to search for.

But what is the purpose of running the query? Running a SELECT query in and of
itself accomplishes nothing. You need a report, form, saved QueryDef, or
control on a form (ListBox, ComboBox) that displays the result of the query
before it is useful for anything.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[Grub]

>
But what is the purpose of running the query? Running a SELECT query in and of
itself accomplishes nothing. You need a report, form, saved QueryDef, or
control on a form (ListBox, ComboBox) that displays the result of the query
before it is useful for anything.

Guess I hadn't gotten that far yet. I would like to display the
results in a form.

[Rick Brandt]

"Grub" <Gr****@gmail.comwrote in message
news:11**********************@h40g2000cwb.googlegr oups.com...

>

>>
But what is the purpose of running the query? Running a SELECT query in and
of
itself accomplishes nothing. You need a report, form, saved QueryDef, or
control on a form (ListBox, ComboBox) that displays the result of the query
before it is useful for anything.

Guess I hadn't gotten that far yet. I would like to display the
results in a form.

Then make that query the RecordSource of the form. Then all your code needs to
do is open the form.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[Tim Marshall]

Grub wrote:

Like "*" & [Please Input a Keyword to Search For:] & "*"

If English is your first language, you might want to make sure you're
using it properly. A lot of developers don't realize how awful their
apps look when the spoken language in which it's written is totally wrong.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Grub]

Thanks Rick..it worked great!

Rick Brandt wrote:

"Grub" <Gr****@gmail.comwrote in message
news:11**********************@h40g2000cwb.googlegr oups.com...

>
But what is the purpose of running the query? Running a SELECT query in and
of
itself accomplishes nothing. You need a report, form, saved QueryDef, or
control on a form (ListBox, ComboBox) that displays the result of the query
before it is useful for anything.

Guess I hadn't gotten that far yet. I would like to display the
results in a form.

Then make that query the RecordSource of the form. Then all your code needs to
do is open the form.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com