 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 1 / February 2006searching using text box and query
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 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, > [quoted text clipped - 26 lines] > >dev 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 > What if that query qryFilmQuery would "look back" at your form where > you entered the actor (presumably a dropdown with ActorID,ActorName): [quoted text clipped - 33 lines] > > > >dev 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 >hi, > [quoted text clipped - 26 lines] > >dev 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 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 > 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 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 >i've done it. sorry for troubling you. > [quoted text clipped - 11 lines] >> I might use..is this what you're after? >> Jerry 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 > yes > [quoted text clipped - 27 lines] > >> I might use..is this what you're after? > >> Jerry >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? >hi, > [quoted text clipped - 63 lines] >> >> I might use..is this what you're after? >> >> Jerry 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 > hi, > [quoted text clipped - 63 lines] > > >> I might use..is this what you're after? > > >> Jerry 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 >i done it now. > [quoted text clipped - 80 lines] >> > >> I might use..is this what you're after? >> > >> Jerry yep, i've already done that. thanx for all yor help. i got them all working. thanx again dev > I use a command button on the form > use an On Double Click Event [quoted text clipped - 100 lines] > >> > >> I might use..is this what you're after? > >> > >> Jerry |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.