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

searching using text box and query

P: n/a
DP
hi,

i have a films table and form. i have a txt field in teh form called
txtSearch , and i;ve created a query with all the film table fields in it.

how can i get the query to load up, wth the required film infromation in
it??

i've a query called qryFilmQuery

and on the actors criterea bit, i've got;

[Forms]![frmFilm]![txtSearch]

so that indicates, wateva is in the txtSearch field in the frmFilm. right.

on the film form i've got;

Private Sub Command55_Click()

DoCmd.OpenQuery "qryFilmQuery"

End Sub

but i need a line in there, which gives the search value? e.g. if i put in
jet li, it shuold load the query, and display every film with jet li in it.

TIA

dev
Feb 24 '06 #1
Share this Question
Share on Google+
11 Replies


P: n/a
On Fri, 24 Feb 2006 17:42:12 GMT, "DP" <DP@hotmail.com> wrote:

What if that query qryFilmQuery would "look back" at your form where
you entered the actor (presumably a dropdown with ActorID,ActorName):
select * from Films where
SillyActorID=Forms!MyCriteriaForm!MyActorsDropdown

-Tom.
hi,

i have a films table and form. i have a txt field in teh form called
txtSearch , and i;ve created a query with all the film table fields in it.

how can i get the query to load up, wth the required film infromation in
it??

i've a query called qryFilmQuery

and on the actors criterea bit, i've got;

[Forms]![frmFilm]![txtSearch]

so that indicates, wateva is in the txtSearch field in the frmFilm. right.

on the film form i've got;

Private Sub Command55_Click()

DoCmd.OpenQuery "qryFilmQuery"

End Sub

but i need a line in there, which gives the search value? e.g. if i put in
jet li, it shuold load the query, and display every film with jet li in it.

TIA

dev


Feb 25 '06 #2

P: n/a
DP
i thought of doing that first, but realised that after say 100 records, the
drop down list would become too big. thats y i think it would be easier if
it was assigned to a text box.

wat do u think?

dev

"Tom van Stiphout" <no*************@cox.net> wrote in message
news:lu********************************@4ax.com...
On Fri, 24 Feb 2006 17:42:12 GMT, "DP" <DP@hotmail.com> wrote:

What if that query qryFilmQuery would "look back" at your form where
you entered the actor (presumably a dropdown with ActorID,ActorName):
select * from Films where
SillyActorID=Forms!MyCriteriaForm!MyActorsDropdown

-Tom.
hi,

i have a films table and form. i have a txt field in teh form called
txtSearch , and i;ve created a query with all the film table fields in it.
how can i get the query to load up, wth the required film infromation in
it??

i've a query called qryFilmQuery

and on the actors criterea bit, i've got;

[Forms]![frmFilm]![txtSearch]

so that indicates, wateva is in the txtSearch field in the frmFilm. right.
on the film form i've got;

Private Sub Command55_Click()

DoCmd.OpenQuery "qryFilmQuery"

End Sub

but i need a line in there, which gives the search value? e.g. if i put injet li, it shuold load the query, and display every film with jet li in it.
TIA

dev

Feb 25 '06 #3

P: n/a
I would use after update and not on click
Is it not working with what you have?
What happens when you try to execute
Jerry

On Fri, 24 Feb 2006 17:42:12 GMT, "DP" <DP@hotmail.com> wrote:
hi,

i have a films table and form. i have a txt field in teh form called
txtSearch , and i;ve created a query with all the film table fields in it.

how can i get the query to load up, wth the required film infromation in
it??

i've a query called qryFilmQuery

and on the actors criterea bit, i've got;

[Forms]![frmFilm]![txtSearch]

so that indicates, wateva is in the txtSearch field in the frmFilm. right.

on the film form i've got;

Private Sub Command55_Click()

DoCmd.OpenQuery "qryFilmQuery"

End Sub

but i need a line in there, which gives the search value? e.g. if i put in
jet li, it shuold load the query, and display every film with jet li in it.

TIA

dev

Feb 25 '06 #4

P: n/a
I've just sent you a zip file (via email) with a sample database that
I might use..is this what you're after?
Jerry
Feb 25 '06 #5

P: n/a
DP
i've done it. sorry for troubling you.

it works perfectly.
i wanted to know if i can use that same query to carry out different
searches? e.g could i create a search for directors, using the same query??

or would i have to create another query, for each different search?

thanx
d
ev
"Jerome Ranch" <ra*****@mchsi.com> wrote in message
news:ju********************************@4ax.com...
I've just sent you a zip file (via email) with a sample database that
I might use..is this what you're after?
Jerry

Feb 25 '06 #6

P: n/a
yes

if you're using text boxes, I usually have a cmd button clear all the
text boxes

and in the query I would use in the criteria
(this will wildcard for actor, and director)

like *&[forms]![frmMovieSelect]![txtActor]&*

and in the director criteria use

*&[forms]![frmMovieSelect]![txtDirector]&*
you can use both at the same time to, and wildcard in the text box


On Sat, 25 Feb 2006 15:18:07 GMT, "DP" <DP@hotmail.com> wrote:
i've done it. sorry for troubling you.

it works perfectly.
i wanted to know if i can use that same query to carry out different
searches? e.g could i create a search for directors, using the same query??

or would i have to create another query, for each different search?

thanx
d
ev
"Jerome Ranch" <ra*****@mchsi.com> wrote in message
news:ju********************************@4ax.com.. .
I've just sent you a zip file (via email) with a sample database that
I might use..is this what you're after?
Jerry

Feb 25 '06 #7

P: n/a
DP
hi,

so this is wat i've got;

SELECT tblFilm.FilmID, tblFilm.FilmTitle, tblFilm.Format,
tblFilm.ActorsActresses, tblFilm.Director, tblFilm.Genre, tblFilm.Link,
tblFilm.RentPriceCode, tblFilm.Rating, tblFilm.Certificate, tblFilm.CopyNo,
tblFilm.DatePurchased, tblFilm.Active, tblFilm.Available, tblFilm.Condition,
tblFilm.DateReleased
FROM tblFilm
WHERE (((tblFilm.ActorsActresses)="*" & forms!frmFilm2!txtActorSearch & "*")
And ((tblFilm.Director)="*" & forms!frmFilm2!txtDirectorSearch & "*"));

thats the sql for the query.(above)

below is the code i have for the form; (Note it may look confusing, because
i created 2 queries to do searches, but now i;m trying your way);

Private Sub cmdActorSearch_Click()
DoCmd.OpenQuery "qryFilmQuery1"
txtActorSearch = ""
End Sub
Private Sub cmdDirectorSearch_Click()
DoCmd.OpenQuery "qryFilmQuery1"
txtDirectorSearch = ""
End Sub

i;ve tried wat u gave me, and i cant get it to work. i tihnk it might be teh
sql statement? (ive never seen the '*' and '&' used before? so i dont know
how to use them. Its probably a syntax error.

thanx
dev

"Jerome Ranch" <ra*****@mchsi.com> wrote in message
news:u8********************************@4ax.com...
yes

if you're using text boxes, I usually have a cmd button clear all the
text boxes

and in the query I would use in the criteria
(this will wildcard for actor, and director)

like *&[forms]![frmMovieSelect]![txtActor]&*

and in the director criteria use

*&[forms]![frmMovieSelect]![txtDirector]&*
you can use both at the same time to, and wildcard in the text box


On Sat, 25 Feb 2006 15:18:07 GMT, "DP" <DP@hotmail.com> wrote:
i've done it. sorry for troubling you.

it works perfectly.
i wanted to know if i can use that same query to carry out different
searches? e.g could i create a search for directors, using the same query??
or would i have to create another query, for each different search?

thanx
d
ev
"Jerome Ranch" <ra*****@mchsi.com> wrote in message
news:ju********************************@4ax.com.. .
I've just sent you a zip file (via email) with a sample database that
I might use..is this what you're after?
Jerry

Feb 25 '06 #8

P: n/a
>i;ve tried wat u gave me, and i cant get it to work. i tihnk it might be teh
sql statement? (ive never seen the '*' and '&' used before? so i dont know
how to use them. Its probably a syntax error.
in the criteria of a select qry, I have this in one of my db that
works fine

Like "*" & [forms].[frmHELLO].[selWILDCARDELEMENT] & "*"
[selWILDCARDELEMENT] is a txt box in a form where the user inputs any
string to find the string in a field; they double click a button that
has a docmd.requery
But it looks like that's what you have in your sql below..you should
need only one query since you're wildcarding the search fields...do
you clear them after each qry?

On Sat, 25 Feb 2006 15:34:37 GMT, "DP" <DP@hotmail.com> wrote:
hi,

so this is wat i've got;

SELECT tblFilm.FilmID, tblFilm.FilmTitle, tblFilm.Format,
tblFilm.ActorsActresses, tblFilm.Director, tblFilm.Genre, tblFilm.Link,
tblFilm.RentPriceCode, tblFilm.Rating, tblFilm.Certificate, tblFilm.CopyNo,
tblFilm.DatePurchased, tblFilm.Active, tblFilm.Available, tblFilm.Condition,
tblFilm.DateReleased
FROM tblFilm
WHERE (((tblFilm.ActorsActresses)="*" & forms!frmFilm2!txtActorSearch & "*")
And ((tblFilm.Director)="*" & forms!frmFilm2!txtDirectorSearch & "*"));

thats the sql for the query.(above)

below is the code i have for the form; (Note it may look confusing, because
i created 2 queries to do searches, but now i;m trying your way);

Private Sub cmdActorSearch_Click()
DoCmd.OpenQuery "qryFilmQuery1"
txtActorSearch = ""
End Sub
Private Sub cmdDirectorSearch_Click()
DoCmd.OpenQuery "qryFilmQuery1"
txtDirectorSearch = ""
End Sub

i;ve tried wat u gave me, and i cant get it to work. i tihnk it might be teh
sql statement? (ive never seen the '*' and '&' used before? so i dont know
how to use them. Its probably a syntax error.

thanx
dev

"Jerome Ranch" <ra*****@mchsi.com> wrote in message
news:u8********************************@4ax.com.. .
yes

if you're using text boxes, I usually have a cmd button clear all the
text boxes

and in the query I would use in the criteria
(this will wildcard for actor, and director)

like *&[forms]![frmMovieSelect]![txtActor]&*

and in the director criteria use

*&[forms]![frmMovieSelect]![txtDirector]&*
you can use both at the same time to, and wildcard in the text box


On Sat, 25 Feb 2006 15:18:07 GMT, "DP" <DP@hotmail.com> wrote:
>i've done it. sorry for troubling you.
>
>it works perfectly.
>i wanted to know if i can use that same query to carry out different
>searches? e.g could i create a search for directors, using the samequery?? >
>or would i have to create another query, for each different search?
>
>thanx
>d
>ev
>
>
>"Jerome Ranch" <ra*****@mchsi.com> wrote in message
>news:ju********************************@4ax.com.. .
>> I've just sent you a zip file (via email) with a sample database that
>> I might use..is this what you're after?
>> Jerry
>

Feb 25 '06 #9

P: n/a
DP
i done it now.

i just put an 'OR' in between the statements.

is there a way to clear all the txt boxes in a form?
i want to clear all of them, and not just one of them. (and i dont want to
type in clear for all the fields.)

thanx

dev

"DP" <DP@hotmail.com> wrote in message
news:hO*******************@newsfe6-win.ntli.net...
hi,

so this is wat i've got;

SELECT tblFilm.FilmID, tblFilm.FilmTitle, tblFilm.Format,
tblFilm.ActorsActresses, tblFilm.Director, tblFilm.Genre, tblFilm.Link,
tblFilm.RentPriceCode, tblFilm.Rating, tblFilm.Certificate, tblFilm.CopyNo, tblFilm.DatePurchased, tblFilm.Active, tblFilm.Available, tblFilm.Condition, tblFilm.DateReleased
FROM tblFilm
WHERE (((tblFilm.ActorsActresses)="*" & forms!frmFilm2!txtActorSearch & "*") And ((tblFilm.Director)="*" & forms!frmFilm2!txtDirectorSearch & "*"));

thats the sql for the query.(above)

below is the code i have for the form; (Note it may look confusing, because i created 2 queries to do searches, but now i;m trying your way);

Private Sub cmdActorSearch_Click()
DoCmd.OpenQuery "qryFilmQuery1"
txtActorSearch = ""
End Sub
Private Sub cmdDirectorSearch_Click()
DoCmd.OpenQuery "qryFilmQuery1"
txtDirectorSearch = ""
End Sub

i;ve tried wat u gave me, and i cant get it to work. i tihnk it might be teh sql statement? (ive never seen the '*' and '&' used before? so i dont know
how to use them. Its probably a syntax error.

thanx
dev

"Jerome Ranch" <ra*****@mchsi.com> wrote in message
news:u8********************************@4ax.com...
yes

if you're using text boxes, I usually have a cmd button clear all the
text boxes

and in the query I would use in the criteria
(this will wildcard for actor, and director)

like *&[forms]![frmMovieSelect]![txtActor]&*

and in the director criteria use

*&[forms]![frmMovieSelect]![txtDirector]&*
you can use both at the same time to, and wildcard in the text box


On Sat, 25 Feb 2006 15:18:07 GMT, "DP" <DP@hotmail.com> wrote:
i've done it. sorry for troubling you.

it works perfectly.
i wanted to know if i can use that same query to carry out different
searches? e.g could i create a search for directors, using the same query??
or would i have to create another query, for each different search?

thanx
d
ev
"Jerome Ranch" <ra*****@mchsi.com> wrote in message
news:ju********************************@4ax.com.. .
> I've just sent you a zip file (via email) with a sample database that
> I might use..is this what you're after?
> Jerry


Feb 25 '06 #10

P: n/a
I use a command button on the form
use an On Double Click Event
Here's an example from a db of mine

a double click clears the two cbo (in your case the txt boxes),
reloads the data source with the requery, and then clears the form
(i.e. runs the query with nothing selected)

Private Sub cmdClear_DblClick(Cancel As Integer)
Me.cboCREATORSELECT = ""
Me.cboEXPTSELECT = ""
Me.cboCREATORSELECT.Requery
Me.cboEXPTSELECT.Requery
Me.frmEXPTINFO.Requery
End Sub
you won't need but the first two where you set the txt boxes = ""

jerry
On Sat, 25 Feb 2006 16:57:14 GMT, "DP" <DP@hotmail.com> wrote:
i done it now.

i just put an 'OR' in between the statements.

is there a way to clear all the txt boxes in a form?
i want to clear all of them, and not just one of them. (and i dont want to
type in clear for all the fields.)

thanx

dev

"DP" <DP@hotmail.com> wrote in message
news:hO*******************@newsfe6-win.ntli.net...
hi,

so this is wat i've got;

SELECT tblFilm.FilmID, tblFilm.FilmTitle, tblFilm.Format,
tblFilm.ActorsActresses, tblFilm.Director, tblFilm.Genre, tblFilm.Link,
tblFilm.RentPriceCode, tblFilm.Rating, tblFilm.Certificate,

tblFilm.CopyNo,
tblFilm.DatePurchased, tblFilm.Active, tblFilm.Available,

tblFilm.Condition,
tblFilm.DateReleased
FROM tblFilm
WHERE (((tblFilm.ActorsActresses)="*" & forms!frmFilm2!txtActorSearch &

"*")
And ((tblFilm.Director)="*" & forms!frmFilm2!txtDirectorSearch & "*"));

thats the sql for the query.(above)

below is the code i have for the form; (Note it may look confusing,

because
i created 2 queries to do searches, but now i;m trying your way);

Private Sub cmdActorSearch_Click()
DoCmd.OpenQuery "qryFilmQuery1"
txtActorSearch = ""
End Sub
Private Sub cmdDirectorSearch_Click()
DoCmd.OpenQuery "qryFilmQuery1"
txtDirectorSearch = ""
End Sub

i;ve tried wat u gave me, and i cant get it to work. i tihnk it might be

teh
sql statement? (ive never seen the '*' and '&' used before? so i dont know
how to use them. Its probably a syntax error.

thanx
dev

"Jerome Ranch" <ra*****@mchsi.com> wrote in message
news:u8********************************@4ax.com...
> yes
>
> if you're using text boxes, I usually have a cmd button clear all the
> text boxes
>
> and in the query I would use in the criteria
> (this will wildcard for actor, and director)
>
> like *&[forms]![frmMovieSelect]![txtActor]&*
>
> and in the director criteria use
>
> *&[forms]![frmMovieSelect]![txtDirector]&*
>
>
> you can use both at the same time to, and wildcard in the text box
>
>
>
>
> On Sat, 25 Feb 2006 15:18:07 GMT, "DP" <DP@hotmail.com> wrote:
>
> >i've done it. sorry for troubling you.
> >
> >it works perfectly.
> >i wanted to know if i can use that same query to carry out different
> >searches? e.g could i create a search for directors, using the same

query??
> >
> >or would i have to create another query, for each different search?
> >
> >thanx
> >d
> >ev
> >
> >
> >"Jerome Ranch" <ra*****@mchsi.com> wrote in message
> >news:ju********************************@4ax.com.. .
> >> I've just sent you a zip file (via email) with a sample database that
> >> I might use..is this what you're after?
> >> Jerry
> >


Feb 25 '06 #11

P: n/a
DP
yep, i've already done that. thanx for all yor help. i got them all working.

thanx again

dev

"Jerome Ranch" <ra*****@mchsi.com> wrote in message
news:nu********************************@4ax.com...
I use a command button on the form
use an On Double Click Event
Here's an example from a db of mine

a double click clears the two cbo (in your case the txt boxes),
reloads the data source with the requery, and then clears the form
(i.e. runs the query with nothing selected)

Private Sub cmdClear_DblClick(Cancel As Integer)
Me.cboCREATORSELECT = ""
Me.cboEXPTSELECT = ""
Me.cboCREATORSELECT.Requery
Me.cboEXPTSELECT.Requery
Me.frmEXPTINFO.Requery
End Sub
you won't need but the first two where you set the txt boxes = ""

jerry
On Sat, 25 Feb 2006 16:57:14 GMT, "DP" <DP@hotmail.com> wrote:
i done it now.

i just put an 'OR' in between the statements.

is there a way to clear all the txt boxes in a form?
i want to clear all of them, and not just one of them. (and i dont want totype in clear for all the fields.)

thanx

dev

"DP" <DP@hotmail.com> wrote in message
news:hO*******************@newsfe6-win.ntli.net...
hi,

so this is wat i've got;

SELECT tblFilm.FilmID, tblFilm.FilmTitle, tblFilm.Format,
tblFilm.ActorsActresses, tblFilm.Director, tblFilm.Genre, tblFilm.Link,
tblFilm.RentPriceCode, tblFilm.Rating, tblFilm.Certificate,

tblFilm.CopyNo,
tblFilm.DatePurchased, tblFilm.Active, tblFilm.Available,

tblFilm.Condition,
tblFilm.DateReleased
FROM tblFilm
WHERE (((tblFilm.ActorsActresses)="*" & forms!frmFilm2!txtActorSearch &

"*")
And ((tblFilm.Director)="*" & forms!frmFilm2!txtDirectorSearch & "*"));

thats the sql for the query.(above)

below is the code i have for the form; (Note it may look confusing,

because
i created 2 queries to do searches, but now i;m trying your way);

Private Sub cmdActorSearch_Click()
DoCmd.OpenQuery "qryFilmQuery1"
txtActorSearch = ""
End Sub
Private Sub cmdDirectorSearch_Click()
DoCmd.OpenQuery "qryFilmQuery1"
txtDirectorSearch = ""
End Sub

i;ve tried wat u gave me, and i cant get it to work. i tihnk it might be
teh
sql statement? (ive never seen the '*' and '&' used before? so i dont

know how to use them. Its probably a syntax error.

thanx
dev

"Jerome Ranch" <ra*****@mchsi.com> wrote in message
news:u8********************************@4ax.com...
> yes
>
> if you're using text boxes, I usually have a cmd button clear all the
> text boxes
>
> and in the query I would use in the criteria
> (this will wildcard for actor, and director)
>
> like *&[forms]![frmMovieSelect]![txtActor]&*
>
> and in the director criteria use
>
> *&[forms]![frmMovieSelect]![txtDirector]&*
>
>
> you can use both at the same time to, and wildcard in the text box
>
>
>
>
> On Sat, 25 Feb 2006 15:18:07 GMT, "DP" <DP@hotmail.com> wrote:
>
> >i've done it. sorry for troubling you.
> >
> >it works perfectly.
> >i wanted to know if i can use that same query to carry out different
> >searches? e.g could i create a search for directors, using the same
query??
> >
> >or would i have to create another query, for each different search?
> >
> >thanx
> >d
> >ev
> >
> >
> >"Jerome Ranch" <ra*****@mchsi.com> wrote in message
> >news:ju********************************@4ax.com.. .
> >> I've just sent you a zip file (via email) with a sample database that > >> I might use..is this what you're after?
> >> Jerry
> >

Feb 26 '06 #12

This discussion thread is closed

Replies have been disabled for this discussion.