Search Keyword using VBA Part 1

[@abbottlineage]

I've been searching for something like this for weeks. I truly appreciate what you did. It was an outstanding tutorial and I feel so powerful.

[@tameraziz2104]

Hello... I would to thank you for making these wonderful tutorials for the community. They are all smooth and great and well done. I appreciate a lot your contribution... and teaching...!

[@dodomak9374]

OMG I've been searching for this for months and this is the only way works. Thank you!!!

[@sujayshil5830]

Sir, you have uploaded a great video. But sir if I want to create every day's records altogether of a customer(same ID.) then is it possible in ms access ?. If possible then please create a video about it. If not, then possible in visual basic ? Please create a video about it.

[@akashtribhuvan5807]

Hello Sir, I have got some concern with navigation form, when I created search combo box in a form (frmUserDetails) it perfectly works. Now I have a navigation form and a sub navigation form in which I added the form frmUserDetais but every time it gives me a message box of "Enter parameter value". How do I resolve this?

[@josiaha1885]

Great tutorial bruh, just am trying to make it more interactive when no record is found. Would you mind sharing your wisdom on how to go about it?

[@edwwjy]

Best tutorial for MS Access so far. Thank you.

[@sharmaneaurora6332]

Hello, can I use this if the fields in my query contains number values and not string?

[@PauloDoutel]

Hi. Thank you very much for all your examples that are very usefull in my daily job. Can you help me on this: If I want to hilight and give a colour to my search data like data1*data2*data3... how can I do It using VBA code? This is very usefull to mark the criteria we chosed. Thank you very much. Best regards from Portugal Paulo

[@kurtgilchrist8833]

ok i followed all the steps and created the form, now when i link it to a switchboard it doesnt works. How do i know it doesnt works because my information on the form doesnt load and plus when i try and search for a name nothing happens. Even when i click show all nothing happens. How do i solve this issue please?

[@jingweima7877]

Hey Tewan, I would like to ask you how could I can create a form from a table with a lot of columns, I did what you did in video but every time access told me that my table has so many columns that it can not be transferred to a form. How can I solve this problem? Please......

[@grace-peace4583]

Hi austin can you please help? I've done everything you said but my show all and search dont work, when I go back to design view to change to continuous I dont see this option there

[@conradblume4274]

Thank you for taking the time to put this together, it works perfectly for me!!!

[@AamirSaeedTajalli]

Hello Tewan, I am very new to Access and need help on: I want to get value of combobox and show the relevant country name (from countries table) in a separate text box. (Cities list populated from cities table, i have country id in cities table as FKey) Could you help ?

[@jamesorogan5848]

how about search then edit the record ? do you have that kind of video ?

[@daisylwcm2004]

only 1 column is not working for me. the rest is ok. is there any problem if my Data Type is a number?

[@kavourakos]

I'm using an ID as reference so i put "integer" instead of "string" in varuables and i always get "Type mismatch" error wtf

[@Access-lx4wo]

Hi Tewan, how to send a reminder from access using whatsApp according to criteria

[@blinkingstar9717]

Man this is Just Great, You made it very Simple, I have 2 things to ask hear 1) After you hit the Showall butten, the data in the keywords textbox (which we entered to search previously) is still there unless you go back to the box select the data and deleat, can you please advise how to clear it as you hit the showall butten.2)With this type of search option, how big database can be searched? i mean i am going to have like 30,000 rows of data in my table over a year

[@austin72406]

1. set a code to make a txtSearchbox = "" 2. it should be fine to handle 30,000 records

[@blinkingstar9717]

+austin72406 Thank You Austin, you did not specify where do i set the code txtSearchbox =""

[@austin72406]

set at ShowAll button if you're using VBA.

[@blinkingstar9717]

I have exactly copied the same concept like you did in this Video, Please tel me where exactly this code needs to be added.

[@kazimakbari9814]

Would you please explain shortly how it should work with a Drop down list in the form. In my case I can search with the ID but not with the actual value.

[@austin72406]

you will need to refer the ID on combo box to the actual value of combo box then compare to the search text box. for example, you have data on combo box like 1 = cat 2 = dog 3 = bird now you type "cat" on a search box. A code will be different from this video. you cannot search directly like cat = 1 on combo box. we need to link 1 to CAT on its table like dim strAnimal as string strAnimal = dlookup("Animal", "Animaltable", "ID =" & comboboxName ) strSearch = "select * from tablename where (AnimalName = " & strAnimal & ")"

[@InfoTechGuruji]

hello i am getting following error please help me, Runtime error 3008 The table tblBaskets is already opened exclusively by another user, or it is already open through the user interface

[@andersiskov8630]

Thanks alot this helped a ton!! If i have a table that is already filtered, and want to search, how what code do I Need to come back to this filter after searching with my keywords??

[@andersiskov8630]

@austin72406 can you help me here? I reallz appriciate your movies!

[@austin72406]

How about adding another search box that will search on the result of first search box

[@arnavmukherjee3832]

Hi Austin.I have applied the code you have show in the video but every time I am getting error message please help Syntax error (missing operator) in query expression ' (( Employee name like "*Alok Dhar*"))'. The Show all button is working fine but serach button is not working My code is given below; Dim Strsearch As String Dim StrDate As String StrText = Me.Txtsearch.Value Strsearch = "SELECT * from tblkaizenform where ((employee name like ""*" & StrText & "*"")" Me.RecordSource = Strsearch

[@austin72406]

you need a [....] bracket for field name like [Employee Name]

[@arnavmukherjee3832]

Thanks austin its working fine...grt job

[@laboranilmubudaya697]

Why we need bracket to write the field name ?? Please, tell me. Thanks a lot..

[@jojiofquilon7124]

@Nana sara very helpful thank you

[@NessanHarpur]

Hi Austin, How do I incorporate another table into the string search? So far I have this for my first table: strsearch = "SELECT * FROM [tblCompany] WHERE (([Company_name] LIKE ""*" & strText & "*"") OR..... How do I UNION or JOIN the second table to this search?

[@austin72406]

the easy way is creating a query that combines two tables then Select * from queryname.

[@akhonasomsam4387]

Great Tutorial indeed but I am experiencing problem when "show all" is clicked on. The response is Syntax (compile error). Please help me to proceed, I'm stuck.

[@pitagora8696]

is it possible to make this but with 2 textboxes? example: list propertises between 2 prices

[@austin72406]

I have the sample file for download, but not free here: www.iaccessworld.com/downloads/advance-search-keyword-ms-access/ It's just a little bit for contributing to my work.

[@ericma2937]

Like is a sequential search. Not by full text index. Am I right?

[@SuperPaunocudoyoutub]

Hi Tewan, congrats on the videos, everything works great

[@borderfishingclub7177]

how search Unicode in Form above , please help me, thanks

[@carbon1479]

Something funny happened the first time I tried this - couldn't get the fields to show up below Detail. I ended up throwing that particular form out, restarting from scratch and everything worked great. I think it could have been an order of operation issue at my end.

[@austin72406]

I'm grad you're able to solve it.

[@Steampunk915]

Thank you so much with this video it really has helped me well in making my database for my final project...

[@austin72406]

you're welcome. Thanks Wahyuddin

[@maryroseromulo5528]

nice tutorial. how i wish i learn the vba code. one question: i have a form with a subform. i want to search by keyword within the subform. do you have any idea how to do this?

[@austin72406]

I would prefer to use a split form rather than subform. a split form has a datasheet view like subform and header section to add your search box, so it uses one datasource for searching.

[@judhastianseba5595]

Pls solve out mine as your vba code I gave but no works

[@timohtykaleli3812]

works very great. what code do you put under the recordcount text box to display the results on pressing the ENTER key without pressing the the Search button?

[@austin72406]

for recordcount, I have the function at my website here: www.iaccessworld.com/function-count-records/ for getting result without pressing the Enter Key, Normally you will need to click the Search button to get a result. but you can call the on Click for Search button on the After Update Event of Search box. example, Private Sub CmdSearch_Click() '''=== search button Call Search End Sub Private Sub txtSearch_AfterUpdate() call CmdSearch_Click() End Sub

[@timohtykaleli3812]

Thanks sir for the response . indeed the the module works perfectly and much appreciated. But i realize the Recordcount Textbox will not indicate the number of the recordset on pressing the ENTER key Unless the Search Button is pressed. What code do i put under the RecordCount textbox on pressing the ENTER key. Kind regards

[@gabrielavila953]

strtext1 = Me.txtsearch1.Value getting error in this area of search can help me pls

[@rcool55]

absoluetly amazing . thanks

[@codybuzzard1]

Would it be possible to search with multiple keywords using this method? For example, could this be modified this so I could search for the words "John" & "Smith" and get the on entry for John Smith?

[@austin72406]

it will work with 2 keyword as search box treats it as one word. you can type in "John Smith" then will get the result as John Smith. If you want you can follow below...Search for two keywords The above example only search for a single word. If you want to search customer name by entering two words like "John Smith" you will need to revise the search code a little bit. You need to change the code where it says (CustomerName Like ""*" & strsearch & "*""). Start with Dim strSpaceFix as String Dim strLoad as String 'Load Text Box contents to strLoad variable strLoad = Me.txtSearch.Value 'Replace spaces with addition search code strSpaceFix = Replace(strLoad, " ", "" & "*"" AND [CustomerName] Like ""*" & "", 1, -1) Then add strSpaceFix to the SQL statement ([CustomerName] Like ""*" & strSpaceFix & "*"")

[@codybuzzard1]

Excellent. Thank you

[@nessandavidharpur1023]

Great video, keep it up! I just have one question: Is it possible to search two categories in one box? For instance could I return customers with the last name smith and all customers based in San Diego.

[@austin72406]

Watch this video advanced search. It may help kzbin.info/www/bejne/aYS8iaaan8Rkhck

[@limon1263]

thnaks for your video. Can u tell me when i create a report like this form what is the code i use......?

[@x-vol5711]

i try to woring it but it's can't because the Macro things not set... how to solve that please?

[@karenl.2002]

Hi Austin, is it possible to create a message when the search comes up empty? Do we use ElseIf ?

[@austin72406]

you can use count record method. If count record from search = 0 then generate the message. I have CountRecord function at my website here: www.iaccessworld.com/function-count-records/

[@eddiepacardo9046]

Hi i have a tried & used all your tutorial.. its really a big help.. i have search same code but im using macro but unfortunately it doesn't work in navigation form im using the split form there's a big con flick with the macro search code that i have when using navigation form.. i have a problem using the search when its Null.. like if someone accidentally leave the txtSearch and then press the search button it will pop-up an Run-time Error 94 Invalid use of Null..

[@austin72406]

you should verify txtSearch with if function first like If IsNull(Me.txtSearch) Or Me.txtSearch = "" Then msgbox " Please enter keyword first" else proceed the action end if

[@eddiepacardo9046]

austin72406 thanks for the reply i really appreciate it.. i have found a way around it i put and Error trapper "On Error Goto" then setfocus on the textbox "txtSearch".. your insight are really helpful.. keep it up.. Thank You and God Bless..

[@carbon1479]

+austin72406 Is there a way to run an If IsNull(Me.txtSearch) = True Then that would return all records?

[@katramirez6743]

Hi, for the Search button, I get an error, when I debug it does not like Me.RecordSource = strsearch. I don't know what I did wrong.

[@austin72406]

Kat Ramirez comes from your SQL invalid. please check your SQL statement again or you have the data for your SQL.

[@slyowusu99]

Hi all. Can you help? I know something isn't right with this especially the date parts → Dim strsearch As String Dim strtxt As String Dim startdate As Date Dim enddate As Date strtxt = Me.txtNAMESEARCH.Value startdate = Me.txtSTARTDATE enddate = Me.txtENDDATE strsearch = "SELECT * from qryALLSTAFFSHIFTSandDUTIES where (NameDetail like ""*" & strtxt & "*"" AND [Dates] between '# & startdate & #' and '# & enddate & # '" Can you help pls. Thanks.

[@sukronjazuli]

Hallo mr austin, i want to ask.. How to make a singgle report from sub form... For example: Can show report just one person... Not show many people... Thank you.. I love you video. 👏 please answer me...

[@austin72406]

it is similar to open form that link to specific id on current form. see my post here: www.iaccessworld.com/how-to-open-linked-form-using-macro/

[@edwardelricrockbell2085]

Hi... I was looking for the Count code which is on the upper left of the form counting the Total filtered records.

[@austin72406]

on my website here: www.iaccessworld.com/function-count-records/

[@edwardelricrockbell2085]

I'm new on access, cant make it work for the result on the filtered rows as shown in your video here. That's what I'm lacking. :(

[@laboranilmubudaya697]

Hello Austin72406 ! Thanks for your sharing ! I really like your video tutorial. But i'm in trouble with the code. In the video, you used main form, but in my training, i have been use sub form. So, your code didn't work.. PLEASE, HELP ME... I've got error in: Me.RecordSource = Strsearch the line was have yellow colour..

[@Pastie273]

These videos are excellent,.Can anyone point me in the direction of how to create a pop up or count box to count the number of records returned by my search. I can count the total number of records in my database, but not the number of filtered records based on the search

[@styvette]

This works! thanks! could you add condition code if else when result is not found. thank you!

[@sebble19844]

If IsNull(Me.TxtSearch) Then MsgBox "Please enter a value to search!", vbInformation, "Inglewood CRM" Else strText = Me.TxtSearch.Value strsearch = "SELECT * from ContactManagement where ((forname like ""*" & strText & "*"") or(surname like ""*" & strText & "*"") or(company like ""*" & strText & "*"") or(leadSource like ""*" & strText & "*""))" Me.RecordSource = strsearch End If

[@DanielSambrano23]

Seb Goring Thank You!

[@nurulaida2449]

can anybody help me with this error [run-time error "2580"] Private Sub Command173_Click() Dim strsearch As String strsearch = "SELECT * from tlb_Tempahan Peralatan" Me.RecordSource = strsearch -----[ the debug is here ] End Sub

[@AdheRei]

thanks a lot for the video, but something missing it is : What about the show nothing at the first form view ? how to do that sir ?

[@austin72406]

it's similar to show all, but add customerID = "" on the SQL line on the Form On Load Event

[@AdheRei]

thx a lot

[@dave_jones]

Can you explain why you used ""*" so often and what it means exactly? I'm not seeing the logical flow of why all those double-quotes are necessary (though they clearly are because I broke it without them!)

[@dave_jones]

For those interested, 2 double quotes is the escape char for another double quote. That is, """ within a string adds a single double quote to the string (the first two indicate to NOT end the string).

[@NessanHarpur]

David Jones the '*' means 'all'. So it's SELECT * (all) FROM tbl...

[@cynthiaramirez9714]

Hello austin72406, thank you for your quick response, it shows that I have write permission and am the administrator, still having no luck. Is there something else I need to check or may be doing wrong?

[@austin72406]

can you check if your still thre after reopen program? If Access saves your code then shouldn't be problem.

[@spiderzgy2138]

Hi, I found a bug. after you add the call function to the keyword textbox, if you type something in the keyword box, and the press the "show all" bottom it will run the keyword search function. not the show all function. how to fix that? thank you

[@austin72406]

clear keyword and called Show All function/command

[@spiderzgy2138]

thank you. Can you show us how to disable the shift bypasskey in access 2013?austin72406

[@RockyCanadian]

Do you have a tutorial, using date and keyword search at the same time? And also exporting the search result to an excel? Cheers

[@austin72406]

I have similar Video and sample file here: Search by Date Range :kzbin.info/www/bejne/e3e0Z3aifJh-i9U and the Sample download file: www.iaccessworld.com/downloads/search-multiple-fields/

[@RockyCanadian]

austin72406 Thank you! i did access more than a decade ago and your videos are the most helpful in KZbin! Thanks for sharing, keep it up! I just subscribed - your channel needs more views. =)

[@kholoudj.2098]

It work but no data visible in the body of result What can i do?

[@meilingli1020]

HI sir, I have followed all the steps but the search button doesn't work, would this function still works if I am using Access 2007? And I don't want the users able to add, edit or delete data in this form, how should I do?

[@austin72406]

it should work for all version since it uses the VBA. follow the How To for create form as read-only here: www.iaccessworld.com/set-form-as-read-only/

[@meilingli1020]

Problem solved, Thank you :)

[@romealzoomalan5653]

hello austin thankyou for a great video i used your code and it works but when there is nothing in the textbox and I click on search I get a runtime error 94 imvalid use of null can you please help me in fixing this issue or what I need to do to fix it

[@austin72406]

you need to test it if there is a test entered before processing the search. You can use If Statement to test it. If isnull(me.Textbox) then msgbox "Please enter a keyword" else ' process your search code here end if

[@quangpham4634]

I made the same thing with you but when to click on the Show All button, It showing the error - Run time error '3131': Syntax error in From Clause my code: Private Sub Command247_Click() Dim strsearch As String strsearch = "SELECT * from List of Clinical Trials" Me.RecordSource = strsearch End Sub DO you know where I am wrong

[@austin72406]

if your table is named with many words then your need bracket [...] strsearch = "SELECT * from [List of Clinical Trials]"

[@John-zk2sy]

Hi Austin, is it possible to make same search as type but not on the name or last name, but on ID or unique number please, can you do tutorial on that please?

[@austin72406]

it is the same logic. if the data type of your ID or unique number is number then you don't need an apostrophe (') referencing to the search text box.

[@John-zk2sy]

Thank you Austin, I'l try that, I am pretty new to VBA, so hopefuly it will work:) Thanx again , you are awesome:) I hope you won't mind me contacting you again if i stuck? I'm trying to do a cook book for me privately, if i mae it I will gladely share it with you if you want:) Thank you one more time for your time and help:) Gratefuly, John

[@MGGEwan]

Question... What if I need to include a field with multiple values to the search bar? I get a "#Name?" when I include that particular field on the form. Or I get an error for the WHERE or HAVING clause

[@cynthiaramirez9714]

Hello, this works great but once I exit out and log back in, it no longer works. Am I missing a step. Please Help!

[@austin72406]

Hello Super Sook, seem like you have same issue on different functions. It looks like the Access did not save your code. If you update Access on the Network and already opened by someone else then code is not saved after you close it. If you don't have a Write permission on the network or folder on network then Access does not save your code.

[@redlightningvlogs7849]

hey austin ive got a problem, can i used this code run on single form because im having a syntax error Thread: run-time error 3131 "syntax error in FROM clause" heres my code Private Sub ejstrider_Click() Dim strsearch As String Dim strText As String strText = Me.txtSearch.Value strsearch = "SELECT * from where ((DriversName like ""*" & strText & "*"")Or(Citizenship like ""*" & strText & "*"")Or(TypeOfLicensed like ""*" & strText & "*"")Or(TalentCategory like ""*" & strText & "*"")Or(ProjectSite like ""*" & strText & "*"")Or(Company like ""*" & strText & "*"")" Me.RecordSource = strsearch End Sub thank you in advance.

[@austin72406]

look like your code is missing table name : "SELECT * from where...

[@patrickjones276]

Thanks for the vid, but when I click the button, I get "Compile error: Method or data member not found" and TxtSearch is highlighted in the line strtext = Me.TxtSearch.Value

[@patrickjones276]

OK, I found what I did wrong, but now I get "Run-time error '3075': Syntax error in date in query expression '(item# like "*fuse*")'." and Me.RecordSource = strsearch is highlighted when I go to debug.

[@austin72406]

If you have a Date type field like [StartDate], Me.txtSearch is a text box on the form. Data type is not matching. You need to format txtSearch as Date if you search for date like 1/2/2014 on StartDate field. use Dim strDate as Date strDate = format(me.txtSearch.Value, Shortdate) then use as or ([StartDate] like ""*" & strDate & "*"") I hope this help.

[@AhmadAmin-84]

wow i follow your your vedio youutube its amazing

[@ireneng2583]

Hi Austin, I would like to search via number too, example customerID. However, it keeps giving me error. Could you help me check on the following code?strsearch = "SELECT * FROM tbl_customer where ((Cust_last like ""*" & strText & "*"") or (Cust_First like ""*" & strText & "*"") or (CustID like ""*" & strText & "*"" ))"Thanks a lot!

[@austin72406]

the error comes from (CustID like ""*" & strText & "*"" ) since your strText is a string as you decleared at the beginning. you can refer to the original entry text box (txtSearch) like: (CustID like ""*" & Me.textSearch & "*"" ) Or you may try to convert strText to number by using the CInt(strText) as (CustID like ""*" & CInt(strText) & "*"" )

[@ireneng2583]

Hi Austin, thank you for your reply. It works if I am only checking for customer ID itselfstrsearch = "SELECT * FROM tbl_customer where (CustID like ""*" & CInt(strText) & "*"" )"but when it is a general search I am getting Run-time error '13': Type Mismatch. Is there any reason for that error?Code: strsearch = "SELECT * FROM tbl_customer where ((Cust_last like ""*" & strText & "*"") or (Cust_First like ""*" & strText & "*"") or (CustID like ""*" & CInt(strText) & "*"" ))"Please Help

[@bobosycheung]

Hi Austin, I tried the VBA but still no work with error message "Run-time error '3131': Syntax error in FROM clause.

[@bobosycheung]

Private Sub Search_Click() Dim strsearch As String Dim strtext As String strtext = Me.Txtsearch.Value strsearch = "SELECT * from Database where ((IR__No like ""*" & strtext & "*"") or (PR_No like ""*" & strtext & "*"") or (Precise_Incident like ""*" & strtext & "*"")" Me.RecordSource = strsearchEnd Sub

[@bobosycheung]

Me.RecordSource = strsearch (YELLOW COLOR THIS)

[@bobosycheung]

It's work now... thank you very much !!!

[@SkilledPotatoX]

Thank you so much this helped me so much

[@prabhakarpeje4076]

Dear austin72406, I have created my file as said in your video but when i type name or id and click search box it gives an error "#Name?" in each of the results feilds can u help me out ? being a newbie pls help me....Thanking u in Advance...!

[@prabhakarpeje4076]

for image click this link drive.google.com/file/d/1EMr032EuXWK3SoiQ9jy0XqKNFWW3iWuT/view?usp=sharing

[@horizon8565]

Thank you very much

[@paulchristianespaldon7380]

hello when i try to click the search it give me a blank result thanks in advance,

[@austin72406]

please make sure you have all code correct.

[@EppBre]

Hey Tewan! Thanks a lot for your videos! I've already learnt a lot! My searchform is working well, but when I haven't typed anything in the searchfield and press "search" I get "Run Time Error Report '94' - Invalid use of Null"! What kind of code do I have to add that this error report disappears? {I tried If isnull (mytxtbox) then me.mytxtbox.setfocus} -> but doesn't work thank you!

[@austin72406]

yes you need IF Statement tests if is null then give message to enter a keyword elseperform the search functionend if

[@EppBre]

Thanks a lot Ive sorted it out!

[@hikmatmarashdeh2293]

Thanks a lot brother.

[@austin72406]

Always welcome

[@sarahbudiyani5085]

thanks bro! works like a charm!

[@jerhummemarcelo7194]

hi austin awesome video! thanks been searching for some answers for my project and i finaly saw ur vid.ure a great help but one thing i need to fix wich is the form even if i choose continous form instead of single form it still appears as a single record would appreciate your help i already subscribed thanks in advance

[@austin72406]

need to put all field under the Detail section. You also can use a Split form that show both form header and datasheet. my video of how to create Split form here: kzbin.info/www/bejne/jGSbpIZojqx9h7s

[@jerhummemarcelo7194]

+austin72406 i already put all the field in the detail section just like how you did in the tutorial then you format it to continous form but it didnt work with my form just like what you did

[@jerhummemarcelo7194]

+Jerhumme Marcelo austin i badly need your help as the form only display one record and even formatting it as continous form it still display one record unlike what you did which all records displayed tabularly

[@austin72406]

can you post your code here? I can take a look

[@jailbreaking249]

im having an error, runtime error '94' Invalid use of Null when i view my report?? how do i fix this.

[@austin72406]

that mean you search and one of field data is blank and search for blank like (Customerid =). this is no result from searching and will be error on preview report.

[@yanifaustino7337]

+austin72406 i have the same issue, how do you resolve this? what i want to happen is instead of having an error i would like to display a message box saying "Enter Keyword". Please help.

[@ILIILLLIIILL]

You are my lifesaver!

[@clausewitzschultz7161]

Nice Tutorial! Good Job :D Keep it up! Thank you very much! :)

[@quangpham4634]

Now is the error with the search Button, It showing the error - Run time error '3075' : Syntax error (missing operator) in query expression '(Study Coordinator like *Viola*)'. my code Dim strsearch As String Dim strText As String strText = Me.txtsearch.Value strsearch = "SELECT * from [List of Clinical Trials] where (Study Coordinator like ""*" & strText & "*"")" Me.RecordSource = strsearch DO you know where I am wrong

[@austin72406]

(Study Coordinator like should be [Study Coordinator] like

[@iasthaipeterson4825]

Nice! Very helpful. Thank you. :)

[@FareyFerrai]

austin72406 How can i lock the result form? I want disable any kind of editing, data entry on this search form. Thank you for very nice presentation.

[@austin72406]

under the design view and under the property of each field for datasheet, you can lock field by change Yes under Lock, No to enable, no to edition/deletion etc.

[@FareyFerrai]

@austin72406 Thanks. That works. Another issue i have is i have one of the field (eg: name field) as lookup field in the table. And when i apply this field under search field on your instructed event code, it does not work!! what seems to be the reason for that?

[@austin72406]

Name is a word that reserved for access VBA. You should not use it as a name of field name

[@FareyFerrai]

The field name is not just the name. Its EmployeeName. Since this field is lookup field from another table, search does not work if i use this field for search

[@FareyFerrai]

+austin72406 What i mean is how to make this code search values in an unbound combobox which is also on the form??

[@AmrFuad88]

Hello sir, I have one question, i made that search for number and for examlpe when i search for 33, it shows all records containing 33 like 15332, so i don't want that i want just show 33, how can i do that ? thanks a lot.

[@austin72406]

easy, you will need to change to strsearch = "SELECT * from tbl_customer where ([yourFieldName] = " & strText & " )"

[@AmrFuad88]

austin72406 yes, i did that after asking you, just by test , now i see your answer is just like my solution :) thanks a lot.

[@austin72406]

you're welcome

[@laboranilmubudaya697]

Why we need bracket to write the field name ?? Please, tell me. Thanks a lot..

[@MecJnk]

I entered the coded exactly. The search all works fine but search button always gives me a blank record... this is what I have Private Sub CmdSearch_Click() Dim StrSearch As String Dim StrText As String StrText = Me.TxtSearch.Value StrSearch = "SELECT * FROM TblMasterLog WHERE (Project_Manager like ""*" & StrText & "*"")" Me.RecordSource = StrSearch End Sub

[@austin72406]

your code is correct, but make sure your display form or subform is not linked to the main form. if the subform is linked (master field and child field) to the main form then it will be blank because no record found from search that matches with the main form.

[@abunowaf1000]

hi ...many thanks for every thank but i have some problem with the command run i have debug in // me.recordsource= task and me.recordsource= strsearch please help my why i have error!!!

[@austin72406]

only one source can be assigned to RecordSource. you can use And for both sources. if you have more than one source of data, you can combine them first by using the Select Statement SQL then assign to me.recordsource.

[@sebble19844]

Another top video!

[@abdulhamidalhaddadi2255]

Thank you

[@hashimam2u]

Hi austin, Thank you very much for your video, can you help the same way to search date (specific date) Thank you

[@austin72406]

i just update How to on my new website www.iaccessworld.com/create-search-form-by-keyword-using-vba-ms-access/

[@hashimam2u]

austin72406 Thank you very much i will go through it

[@rachidcoster5043]

IT DOES NOT WORK FOR ME

[@deepumb678]

you r great man

[@agussetiawan2285]

can you help me, how to make total like in your form

[@austin72406]

you can set Control Source of textbox to = count(*). it is good for small number. If you have a large number output then the best way is using a function that created by using recordset or count from recordset. then assign function to textbox. I have the example at my website : www.iaccessworld.com/function-count-records/

[@agussetiawan2285]

thank you, very helpfull

[@natarajanrajan9907]

Me.RecordSource = Strsearch i am having trouble in both the search button and search all. It shows debug and red colour is coming in Me.RecordSource = Strsearch

[@austin72406]

that means your SQL statement is incorrect.

[@natarajanrajan9907]

austin72406 please give me the correct SQL

[@christopherbernal7242]

austin72406 austin can u help me what if i want to put the data that i search in Subforms/Subreports? #TIA :)

[@chaosawaits]

PLEASE HELP! Nothing happens when I click "Search" command button Private Sub Command46_Click() Dim strSearch As String Dim strText As String strText = Me.txtSearch.Value strSearch = "SELECT * FROM TblSlot where ([ClassID] like ""*" & strText & "*"")" Me.RecordSource = strSearch End Sub I tried another tutorial by the same guy and also that one nothing happened as well. What gives? Thank you for whomever spots the error!

[@chaosawaits]

OMG! For all those having similar problems... Check to see if there is a box asking to "Enable Content." It really helps to enable.

[@vbaaccess1365]

Man you are cool!

[@danai360]

Thank a teacher :>

[@jaikushwaha12]

show all button is working fine but search button is not working

[@austin72406]

something missing on your code. please check carefully.

[@kholoudj.2098]

+austin72406 same thing to me. below my code if there is any mistake:Dim strsearch As String Dim strtext As String strtext = Me.Keyword.Value strsearch = " select * from PInfo where ((FirstName like ""*" & strtext & "*"")or(FamilyName like ""*" & strtext & "*""))" Me.RecordSource = strsearch

[@redlightningvlogs7849]

i have one more problem my friend how to make preview report i'm having a hard time on my code i try the one on you're other tutorial but i can't make it work here is the code. Private Sub Command57_Click() Preview Report Dim task, strCriteria As String On Error GoTo ErrorHandler strDoc = "DriversReport" If Me.cmdShowAll.Enabled = True Then DoCmd.OpenReport strDoc, acViewPreview, TempVars!vartempfilter Else task = " select * from DriversData_Table" DoCmd.OpenReport strDoc, acViewPreview, task ExitPoint: Exit Sub

[@austin72406]

did you able to open report? did you receive any error yellow line? please specify your error on code.

[@redlightningvlogs7849]

no my friend it won't open the report here's the image of error i1314.photobucket.com/albums/t574/Ejstrider_Symbianize/error%20report_zpszesk5okb.png

[@austin72406]

looks like your code is correct except: Preview Report you can delete it or put make it as comment by putting ' in front like 'Preview Report

[@redlightningvlogs7849]

i guess it work but come to an error like this i1314.photobucket.com/albums/t574/Ejstrider_Symbianize/error%202_zpshld1yxcq.png i1314.photobucket.com/albums/t574/Ejstrider_Symbianize/error%203_zpsx7ie2rvp.png

[@redlightningvlogs7849]

here is my database my friend www.dropbox.com/s/qbbtr9p95s2bkwb/mostafa_.accdb?dl=0 can you check it out im new regarding access and vba so im zero knowledge regarding this so i need help so badly, thank you very much

[@ravian4594]

I love the video, i will be checking out others that you have made but i am working on my final project for my intro to RDBMS class and i want a search box that a msg box will pop up if no results are found. I have another method for the search box that works, but i am trying your method and i can't get the show all button to work. When i click on it this error msg pops up, The expression On Click you entered as the event property setting produced the following error: A problem occurred while Microsoft Access was communicating with the OLE server or ActiveX Control. Here is my code: Private Sub ShowAll_Click() Dim strsearch As String strsearch = "SELECT * from DVD_BlueRay_Collection" Me.RecordSource = strsearch End Sub Any help you can provide would be greatly appreciated.

[@austin72406]

if you have total text box name txtTotal then you can add messge box like below after Me.RecordSearch = strsearch me.RecordSource = strsearch If Me.TxtTotal.Value = 0 Then MsgBox "No result found from searching" End Ifif showall button not working, check name of your table if correct. also make sure the data of your form is same as your recordsource on search.

[@bdhis24]

bdhis24

[@grace-peace4583]

I could send you my table if you so desire

[@mostaphaamrani2303]

Jeu vous dommande Search sur ecrir merci boco