 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms Programming / May 2005Search and display multiple records
Hello All, Is it possible to put a search bar on form that will find multiple records. For example; find all the people who live in a city named Fremont. When I use the binocular search in Access it only lists one record at a time. I would like to see all the records that match a certain criterior. Hi Sky, I would base the search results form on a query, using an input box. Try putting something like this in the criteria field: Like '*' & [Message you want to show in the popup box] & '*' or if you prefer to do it via SQL view, the where part would be something like: WHERE (((TableName.FieldName) Like '*' & [Message you want to show in the popup box] & '*'));  Signaturehth, SusanV > Hello All, > [quoted text clipped - 4 lines] > the binocular search in Access it only lists one record at a time. I would > like to see all the records that match a certain criterior. Thanks Susan for your input on this. I need more practice with Access so I'll know what to do with the answers I get back. I'll still try using what you gave me though and see what happens. Many thanks though! > Hi Sky, > [quoted text clipped - 17 lines] > > the binocular search in Access it only lists one record at a time. I would > > like to see all the records that match a certain criterior. I usually make a main form, and then throw in a sub-form to "list" the hits. In the mains form text box after update event, you can do: dim strSql = "select * from tblCustomers where City like '" & txtCity & "*'" me.myCoolSubForm.Form.RecordSouce = strSql The above will give you a nice list. Here is some screen shots and some ideas of the above code in action: http://www.members.shaw.ca/AlbertKallal/Search/index.html SignatureAlbert D. Kallal (Access MVP) Edmonton, Alberta Canada pleaseNOOSpamKallal@msn.com http://www.members.shaw.ca/AlbertKallal Hi Albert, I currently required to do something as u showed as screen shots... is had used the code below... but have an error as shown below: Daily Report can’t find the macro ‘dim strSql = "select * from tblDaily Report where Origin like '" & txtOrigin & "*'" me.Daily Report1.Form.RecordSouce = strSql ’ The macro (or its macro group) doesn’t exist, or the macro is new but hasn’t been saved. Note that when you enter the macrogroupname.macroname syntax in an argument, you must specify the name the macro’s macro group was last saved under. Did i do anything wrong on it? Pls advice... Sorry i not gd in SQL.... Thanks Green > I usually make a main form, and then throw in a sub-form to "list" the hits. > [quoted text clipped - 8 lines] > > http://www.members.shaw.ca/AlbertKallal/Search/index.html You are setting the value on the declaration line (cannot currently do in vba) and does your table name have a space in it. dim strSql = "select * from tblDaily Report where Origin like '" & txtOrigin & "*'" what you need is dim strSql as string strSql = "select * from [tblDaily Report] where Origin like '" & txtOrigin & "*'" if you really do have a space in the name SignatureRegards Alex White MCDBA MCSE http://www.intralan.co.uk > Hi Albert, > [quoted text clipped - 32 lines] >> >> http://www.members.shaw.ca/AlbertKallal/Search/index.html Hi Alex, Thanks for the quick reply. Yes, my table name there's a space. But after editing what you have told me to do so, it still prompted me that message. Any idea what's wrong with it? Or i had did the form wrongly at the start? Can you guide me on what are the criteria that i required to do before putting this code? Thanks Best Regards Green > You are setting the value on the declaration line (cannot currently do in > vba) and does your table name have a space in it. [quoted text clipped - 47 lines] > >> > >> http://www.members.shaw.ca/AlbertKallal/Search/index.html Need a couple of things from you, what type of field is the origin field? where or what form are you calling the code from? if you Origin field is a text field then for access strSQL = "Select * from [tblDaily Report] where Origin Like '" & txtOrigin.value & "*'" for SQL strSQL = "Select * from [tblDaily Report] where Origin Like '" & txtOrigin.value & "%'" I would strongly recomend not using spaces in any names (tables/fields/etc), as it complicates things. use _ for spaces makes your life easier. SignatureRegards Alex White MCDBA MCSE http://www.intralan.co.uk > Hi Alex, > [quoted text clipped - 68 lines] >> >> >> >> http://www.members.shaw.ca/AlbertKallal/Search/index.html Hi Alex, Thanks for helping :) The feld type is text The main form is Origin_search and the subform where those records will be filtered and shown is Daily_Report_Subform Hope that i have answer your question Thanks Green > Need a couple of things from you, > [quoted text clipped - 88 lines] > >> >> > >> >> http://www.members.shaw.ca/AlbertKallal/Search/index.html Hi, You are using the phrase subform, do you really mean a form embeded within another form, or just called from the Origin_search form? if it is a sub form then the following should work ** Start ** dim strSQL as string strSQL = "Select * from [tblDaily Report] where Origin Like '" & me.txtOrigin.value & "*'" Form_Origin_search.Daily_Report_Subform.Form.RecordSource = strSQL Form_Origin_search.Daily_Report_Subform.Requery ** End ** For the above to work it must be called from the Origin_search form. Do you need to edit this data?, because if you don't then using a listbox on the main form maybe the best answer to your specific problem. SignatureRegards Alex White MCDBA MCSE http://www.intralan.co.uk > Hi Alex, > [quoted text clipped - 107 lines] >> >> >> >> >> >> http://www.members.shaw.ca/AlbertKallal/Search/index.html Hi Alex, Really sorry to trouble you again... but after i put it in to the properties-after update field, there is error in this line: strSQL = "Select * from tblDaily_Report where Origin Like '" & me.txtOrigin.value & "*'" I have listened to your advice and changed the table named to: "Daily_Report". Did i put in the correct field? Sorry i have totally no knowlegde on coding... Best Regards Green > Hi, > [quoted text clipped - 20 lines] > Do you need to edit this data?, because if you don't then using a listbox on > the main form maybe the best answer to your specific problem. Yep small typo strSQL = "Select * from Daily_Report where Origin Like '" & me.txtOrigin.value & "*'" the above is correct if, you have a field called Origin in a table called Daily_Report you have a textbox on the form called txtOrigin the typo was the table name SignatureRegards Alex White MCDBA MCSE http://www.intralan.co.uk > Hi Alex, > [quoted text clipped - 38 lines] >> on >> the main form maybe the best answer to your specific problem. Hi Alex, Sorry... there is an error on the line same line again... "compile error: syntax error" I have checked the table and yes, the Daily_Report has a field named Origin and I have changed the text box on the form to txtOrigin Sorry for the troubles Best Regards Green > Yep small typo > [quoted text clipped - 50 lines] > >> on > >> the main form maybe the best answer to your specific problem. Can you change the line to:- strSQL = "Select * from Daily_Report Where Origin Like " & chr(34) & me.txtOrigin.value & "1*" & chr(34) the reason for this is I think the error is in the speech marks the chr(34) is ascii for " so it enters them into the line without any problems. Does what I am sugesting make sense? your strings have to be enclosed within speech marks for the select statement to work. Good luck.. SignatureRegards Alex White MCDBA MCSE http://www.intralan.co.uk > Hi Alex, > [quoted text clipped - 65 lines] >> >> on >> >> the main form maybe the best answer to your specific problem. Hi Alex, I will try it out and let you know it soon... No matter whether it is successful, I really appreciate your help! Thanks Green > Can you change the line to:- > [quoted text clipped - 51 lines] > >> > Best Regards > >> > Green EAD > >> >> Hi, > >> >> [quoted text clipped - 23 lines] > >> >> on > >> >> the main form maybe the best answer to your specific problem. Hi Alex, it is still unsuccessful... i don't think that is the problem... is there any other way to change the "me." ? I'm thinking whether it is the problem.... but i may be wrong... Best Regards Green > Can you change the line to:- > [quoted text clipped - 79 lines] > >> >> on > >> >> the main form maybe the best answer to your specific problem. Hi Alex, I manage to remove the error... but there is an run time error '424' Oject Required. What is this??? Pls advice Thanks Green > Hi Alex, > [quoted text clipped - 88 lines] > > >> >> on > > >> >> the main form maybe the best answer to your specific problem. Hi, couple of things, strSQL = "Select * from Daily_Report Where Origin Like " & chr(34) & me.txtOrigin.value & "*" & chr(34) you did remove the 1 before the last * in the line above? and even though your sub form is called Daily_Report_SubForm if you type Form_Origin_search.Daily_Report_Subform.Form when you put the . on the end of that line do you get the context list come up, because if you don't then it sounds like the subform has been renamed once embeded on the main form, to check this right mouse click on the small black frame around the subform when in design mode to get the properties of the subform. SignatureRegards Alex White MCDBA MCSE http://www.intralan.co.uk > Hi Alex, > [quoted text clipped - 107 lines] >> > >> >> on >> > >> >> the main form maybe the best answer to your specific problem. Hi Alex, I just found this code in some other forum that allow me to filter the records successfully! Private Sub txtOrigin_AfterUpdate() With Me.[Daily_Report_subform].Form If .Dirty Then .Dirty = False End If If IsNull(Me.txtOrigin) Then .FilterOn = False 'Nothing entered: show all records. Else .Filter = "Origin Like ""*" & Me.txtOrigin & "*""" .FilterOn = True End If End With End Sub However, is there any idea only do i add on if i have more criteria added? Like field: Destination, and etc. By the way do you have any idea on how to prevent user from editing the subform? Like locked it? I try going to the properties and locked, but i was then unable to filter... Regards Green > Hi, > [quoted text clipped - 39 lines] > >> Best Regards > >> Green Hi, .AllowEdit Form_Origin_search.Daily_Report_Subform.Form.AllowEdits = False Should do the trick..... Will you ever need to edit anything in the subform? SignatureRegards Alex White MCDBA MCSE http://www.intralan.co.uk > Hi Alex, > [quoted text clipped - 72 lines] >> >> Best Regards >> >> Green Hi Alex, No i will not be editing in the form itself. it is just for filter and viewing. as for this: Form_Origin_search.Daily_Report_Subform.Form.AllowEdits = False where should i put it??? Sorry for teaching a dumb! Regards Green > Hi, > [quoted text clipped - 81 lines] > >> >> Best Regards > >> >> Green Hey, sorry for what, I personally get great pleasure out of helping people and training people very rewarding, glad I can help, it helps break my working day up into manageable chunks. because you are never editing the form, couple of things come to mind first if you design view the subform on it's own, and go to the properties of the form, (right mouse click on the square dot, top left when in design view, goto properties there is a few settings there .AllowEdits .AllowDeletions .AllowAdditions you can set these at a form level = No double click on the word yes and it will rotate between yes and no, save the form, see if that works, if that does not do what you want you can lock each control on the form by setting the properties of the textboxes to enabled = no locked = yes makes them readonly e.g. SignatureRegards Alex White MCDBA MCSE http://www.intralan.co.uk > Hi Alex, > [quoted text clipped - 98 lines] >> >> >> Best Regards >> >> >> Green Hi Alex, Really thanks for it! :-) It works! So how can i edit the code that i previously posted with more criteria added in? Thanks Green > Hey, sorry for what, I personally get great pleasure out of helping people > and training people very rewarding, glad I can help, it helps break my [quoted text clipped - 127 lines] > >> >> >> Best Regards > >> >> >> Green Hi, You will need to build the filter into a string, the idea here is simple, check the values on the form if there is anything in the boxes then build them into the strFilter variable, then at the end pass the variable as the filter. For more than one entry on the filter line 'AND' must be used to break them up, watch out for anything other than strings because they need to be formatted differently. With the idea below you can make you searches much more defined. Dim strFilter as string Dim bolAnd As Boolean bolAnd = False strFilter = "" If Len(Me.Title.Value) > 0 Then If bolAnd Then strFilter = strFilter & " and " End If strFilter = strFilter & " Title Like '*" & Me.Title.Value & "*'" bolAnd = True end if If Len(Me.First_Name.Value) > 0 Then If bolAnd Then strFilter = strFilter & " and " End If strFilter = strFilter & " First_Name Like '*" & Me.First_Name.Value & "*'" bolAnd = True end if .Filter = strFilter Good Luck, post back if you need more help. SignatureRegards Alex White MCDBA MCSE http://www.intralan.co.uk > Hi Alex, > [quoted text clipped - 151 lines] >> >> >> >> Best Regards >> >> >> >> Green Hi Alex, I don't quite get your idea. Yes, The other criteria is of different format which are date and number. As for the code, where should i put it in? Under the subform property or those unbounded fields in the main form? By the way, i see the code you did, i don't see any form to look into.. so will it filter and show the records in the subform when i entered in the main form? Pls advice, and let me know if i see it wrongly. Regards Green > Hi, > [quoted text clipped - 185 lines] > >> >> >> >> Best Regards > >> >> >> >> Green Hi, yep dates need to be wrapped in #, always format for US date format (I don't know where you are located) numbers have no " round them I guess you have a 'Search' button on the form, put the code in the click event for the button. Dim strFilter as string Dim bolAnd As Boolean bolAnd = False strFilter = "" If Len(Me.Title.Value) > 0 Then If bolAnd Then strFilter = strFilter & " and " End If strFilter = strFilter & " Title Like '*" & Me.Title.Value & "*'" bolAnd = True end if If Len(Me.First_Name.Value) > 0 Then If bolAnd Then strFilter = strFilter & " and " End If strFilter = strFilter & " First_Name Like '*" & Me.First_Name.Value & "*'" bolAnd = True end if With Me.[Daily_Report_subform].Form If .Dirty Then .Dirty = False End If If strFilter = "" Then .FilterOn = False 'Nothing entered: show all records. Else .Filter = strFilter .FilterOn = True End If End With change the textbox names to suit your needs SignatureRegards Alex White MCDBA MCSE http://www.intralan.co.uk > Hi Alex, > [quoted text clipped - 221 lines] >> >> >> >> >> Best Regards >> >> >> >> >> Green Susan, Albert, I will try both your suggestions. I like the sub form idea too but I'm not much of an expert on that yet. I think I'll buy a book on Access that can show me how to do sub forms. Do either of you have a suggested reference you can point me to? > Hello All, > > Is it possible to put a search bar on form that will find multiple records. > For example; find all the people who live in a city named Fremont. When I use > the binocular search in Access it only lists one record at a time. I would > like to see all the records that match a certain criterior. Here is some concepts on using sub-forms: http://www.members.shaw.ca/AlbertKallal/Articles/fog0000000005.html However, if you are looking for a book, here is a on-line one...it is old..but still worth reading: http://www.microsoft.com/accessdev/articles/bapp97/toc.htm SignatureAlbert D. Kallal (Access MVP) Edmonton, Alberta Canada pleaseNOOSpamKallal@msn.com http://www.members.shaw.ca/AlbertKallal Thank you Albert for your resource info re: Access. I shall look into them right now. I had forgotten about looking over the Microsoft site for information but you jogged my memory. Your alright with me dude. Now, if I could just become as good at this as you and others I'd be a happy camper :-) > Here is some concepts on using sub-forms: > [quoted text clipped - 4 lines] > > http://www.microsoft.com/accessdev/articles/bapp97/toc.htm |
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.