Query from form!

  • Thread starter Thread starter Jill
  • Start date Start date
J

Jill

Hello,

This seems like its not to hard to answer; however, i am
having trouble finding someone to help me.

I have a form that takes a keyword and a field and
creates a report on it.

But i would also like a button that acts like a query so
that it generates a table with the results that match the
keyword in the selected field. This "appears" easy with
the keyword only being chosen from one field, but the
user can also select the field as well, and i am not good
enough at access to figure out how to write it so it
works.

Any help is appreciated,

Jill
 
Hi Jill,
Okay, let's say your saved query looks like this:

Select field1, field2, field3 From yourTable

and it's called yourQuery

Now let's say the controls on your form are called:
txtField and txtKeyword

Now this code goes in the click event of your cmd button:
**air code**

Dim db As Database
Dim qdf As QueryDef
Dim strSql As String

Set db = CurrentDb()
Set qdf = db.QueryDefs("yourQuery")
strSql = "Select field1, field2, field3 From yourTable " & _
"Where " & Me.txtField & " Like '*" & _
Me.txtKeyword & "*'"
'you might want to view the above to make sure
'it's syntax is correct
MsgBox strSql
qdf.SQL = strSql
'now the query that your report is
'based on has the chosen criteria
Set qdf = Nothing
Set db = Nothing
 
Hi Jill,
What I need to see is the evaluated string. The MsgBox line in
my original code will show you that.
Post it and we'll go from there.

--
HTH
Dan Artuso, Access MVP


Jill said:
Actually ive gotten it to this, when i use it without the
LIKE and have to enter the field exactly, it works, but
when i use the LIKE, it doenst, it opens a box for
parameter value then causes error, this is what i have
for one of my cases, i cant see why its doing this:

strsq2 = "[Project Title] Like ""*" &
Me.txtWot2Find & "*"""
strSq1 = "SELECT [Table 1: DC Main].[Project
No], [Table 1: DC Main].Company, [Table 1: DC Main].
[Project Title], [Table 1: DC Main].[DC Drawing No],
[Table 1: DC Main].Revision, [Table 1: DC Main].[Drawing
Title], [Table 1: DC Main].[Box No], [Table 1: DC Main].
[CD No] FROM [Table 1: DC Main]" _
& " WHERE " & strsq2

DoCmd.OpenQuery "FQ", acViewNormal, acReadOnly

qdf.SQL = strSq1
Set qdf = Nothing
Set db = Nothing
--------

it all fits on one line (that big part) so nothing wrong
there

-----Original Message-----
Hi Jill,
Okay, let's say your saved query looks like this:

Select field1, field2, field3 From yourTable

and it's called yourQuery

Now let's say the controls on your form are called:
txtField and txtKeyword

Now this code goes in the click event of your cmd button:
**air code**

Dim db As Database
Dim qdf As QueryDef
Dim strSql As String

Set db = CurrentDb()
Set qdf = db.QueryDefs("yourQuery")
strSql = "Select field1, field2, field3 From yourTable " & _
"Where " & Me.txtField & " Like '*" & _
Me.txtKeyword & "*'"
'you might want to view the above to make sure
'it's syntax is correct
MsgBox strSql
qdf.SQL = strSql
'now the query that your report is
'based on has the chosen criteria
Set qdf = Nothing
Set db = Nothing


--
HTH
Dan Artuso, Access MVP





.
Click to expand...
Click to expand...
 
I figured it out,thanks so much!
-----Original Message-----
Hi Jill,
What I need to see is the evaluated string. The MsgBox line in
my original code will show you that.
Post it and we'll go from there.

--
HTH
Dan Artuso, Access MVP


Actually ive gotten it to this, when i use it without the
LIKE and have to enter the field exactly, it works, but
when i use the LIKE, it doenst, it opens a box for
parameter value then causes error, this is what i have
for one of my cases, i cant see why its doing this:

strsq2 = "[Project Title] Like ""*" &
Me.txtWot2Find & "*"""
strSq1 = "SELECT [Table 1: DC Main]. [Project
No], [Table 1: DC Main].Company, [Table 1: DC Main].
[Project Title], [Table 1: DC Main].[DC Drawing No],
[Table 1: DC Main].Revision, [Table 1: DC Main]. [Drawing
Title], [Table 1: DC Main].[Box No], [Table 1: DC Main].
[CD No] FROM [Table 1: DC Main]" _
& " WHERE " & strsq2

DoCmd.OpenQuery "FQ", acViewNormal, acReadOnly

qdf.SQL = strSq1
Set qdf = Nothing
Set db = Nothing
--------

it all fits on one line (that big part) so nothing wrong
there

-----Original Message-----
Hi Jill,
Okay, let's say your saved query looks like this:

Select field1, field2, field3 From yourTable

and it's called yourQuery

Now let's say the controls on your form are called:
txtField and txtKeyword

Now this code goes in the click event of your cmd button:
**air code**

Dim db As Database
Dim qdf As QueryDef
Dim strSql As String

Set db = CurrentDb()
Set qdf = db.QueryDefs("yourQuery")
strSql = "Select field1, field2, field3 From
Click to expand...
yourTable "
& _
"Where " & Me.txtField & " Like '*" & _
Me.txtKeyword & "*'"
'you might want to view the above to make sure
'it's syntax is correct
MsgBox strSql
qdf.SQL = strSql
'now the query that your report is
'based on has the chosen criteria
Set qdf = Nothing
Set db = Nothing


--
HTH
Dan Artuso, Access MVP


Hello,

This seems like its not to hard to answer; however,
Click to expand...
i
am
having trouble finding someone to help me.

I have a form that takes a keyword and a field and
creates a report on it.

But i would also like a button that acts like a
Click to expand...
query
so
that it generates a table with the results that
Click to expand...
match
the
keyword in the selected field. This "appears" easy with
the keyword only being chosen from one field, but the
user can also select the field as well, and i am not good
enough at access to figure out how to write it so it
works.

Any help is appreciated,

Jill


.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top