How To Create A Keyword Search in Access 2013 🎓
ฝัง
- เผยแพร่เมื่อ 15 พ.ค. 2015
- In this Access 2013 tutorial video, Steve Bishop explains how to create a keyword search for your forms to filter your subforms and listboxes.
Work Files: github.com/Xipooo/Access2013H...
Looking for DAILY content? Join us on my other channel “Coffee With Steve” for daily videos where we discuss Technology, Software Development, Politics, Culture, and many other things.
Coffee With Steve: th-cam.com/channels/eXAUvo5xxDY_b-lSknPC1A.html
Hi thank you for your tutorials. I just wnt to ask how to create a button that everytime you click increases the number? Thank you so much =)
This is the best tutorial video I have viewed ever about search forms.
thank you sir.
searching this topic for weeks and nothing come across better than this keep the good work you are truely the best teacher
This is by far the best tutorial I have viewed on Access Database in searching forms with lots of option in building your SQL query.
Thank you! Clear, concise, and thorough - this is hard combination to find but your video has all three.
Thanks, I started using Ms Access a few months ago and I find your videos really good. This one helped me refine my search form in my current DB project. Thanks a lot.
Mahalo plenty, Steve! This lesson was *exactly* what I've been looking for. Your explanations were clear, concise, and thorough.
Thanks Steve your tutorials are amazingly clear and easy to follow but more than that your explainations make it understandable also, i really learn when i watch your tutorials as opposed to just being able to copy
I have to tell you that you have done an excellent job here by creating these videos. After these many years it's still useful to people who trying to know Access as it offers, I came here to learn to how to built query for a rather seemed to be a difficult calculation and now I am building an inventory system by myself. only thanks to you. The details and extra features that you explain on your videos are so useful and which can be useful in different senarios.
Anyway meny thanks and you may get the best out of your life.
THANK YOU! Been struggling with this months and watched other tutorials but this was the only one that finally worked out!
It is a pleasure to listen to you share your knowledge! By far, one of the absolute best instructional/demo videos I have come across for MS Access. I subscribed.
Just want to say a massive thank you for this incredible tutorial. I am a total novice at this and this was easy to follow and iv managed to create a brand new search database for my work colleagues to now search for information rapidly.
Thanks very much for this. Brilliant and perfect.
I am so glad to hear you found this video helpful. Hopefully you find the rest of them equally helpful.
Not only it was comprehensive, you explain it clearly and step by step. THANK YOU,
Man you are the Good Lecturer ever. man i love it. Not only this video. All your videos. You really knows the thing. Thanks so much. you've tought me very well. God bless you.
Hi Steve, I've been through most all of your videos and they have helped me a lot. Thanks For Your Time And the great Asset You made Available to Us.
I'm so glad to hear that!
your videos are amazing, I feel very grateful to find you on youtube!! you are my favorite guy!!
Well thank you!
Hi, I learned a lot from your video...Thank you... You were the best to explain how to create this search button.
This is a great video and I've implemented it with great results. I've enhanced it slightly by adding one more VBA line and that is returning focus to the keyword text box so I don't have to select the contents of this text box, hit delete and then Enter to get all the records or to enter a new search. "tion" returns all records with this sub-string and then just typing "national" returns all records with this new sub-string. It saves a lot of keystrokes and keeps my hands on the keyboard when I'm trying to search using different strings.
This is the best search form tutorial on youtube. Thanks
Great, will start learning from beginning 👍 it's really hard to find best videos 😄
That's so great. Exactly what I was looking for. Thanks for sharing.
I'm learning so much from your video tutorials! Thank you.
Steve, your voice is so assuring and you instructions very clear. I would like to show my appreciation. How can I donate a token to the great work you're doing?
excellent video tutorial to show how to implement search criteria
whether it is for a form, listbox, or something else! Great job, Steve @ProgrammingMadeEZ -- So nice to see really good videos for Access! thank
you ~ crystal
Please Steve, keep doing these very good tutorial videos. Many Thanks
Clear and the best tutorial ive learned a lot from you. Thank you!
2017 and this amazing tutorial still works perfectly! One question for Mr Bishop - is it possible to make the same code search using more than one text box? I've managed to make five text boxes each with their own search button, but it would be so nice to have one button for all! Thankyou for the video Mr Bishop, you're a lifesaver!
Thanks for this easy-to-follow tutorial. I found it exceptionally helpful.
thank you very much for this video. It shows clearly how to start working with VBA in Access
Thanks Bishop easy to learn from your tutorial.
Steve, thank you so much! You are a fantastic instructor!
Thanks Chris - I do appreciate your gift.
Thank you so much!
I learned from you many useful things!
Your very good on explaining access technical details.. Thanks for this videos :)
Thank you!
good explanation and helpful. i really learnt a lot from this tutorial. thank you
I like your video very much. It's really great. I'll keep an eye on your channel. I am your fan and I will support you.
Exactly what I needed, it seems that Access gives you much more control over the forms if you don't start with the wizard.
Yes, I despise the wizards!
Very nicely done! Working on a db at the moment and this is very helpful 🙏
In 2018 this is still a valuable video. :)
Thank you very much, this is easy explained and saved my time....
+Uriel Ramirez It is available in 720p. You might need to click on the gear icon to change your resolution.
Hi Steve, I've been through most all of your videos and they have helped me a lot. I have a form where I created a keyword search of 1 table that, when clicking on a button, a user is prompted to enter a value and record(s) are returned data filling 8 text fields in my main form.
What I want to do is use a returned value from a field in the main form for a 2nd keyword search of 2 other joined tables in a subform. Using the techniques you demonstrate in this video, I am able to use a "keyword button" search to validate the subform works.
Here is the code for the button search of the main form
Private Sub BtnSSN_SN_Click()
Dim SQL As String
SQL = "SELECT [Test_ KVDBA_kV_Matchpoint_Data].ID_GE_Serial_No, [Test_ KVDBA_kV_Matchpoint_Data].ID_BME_No, [Test_ KVDBA_kV_Matchpoint_Data].ID_Reg_Scan_1, " _
& "[Test_ KVDBA_kV_Matchpoint_Data].ID_Brd_Scan_1, [Test_ KVDBA_kV_Matchpoint_Data].ID_Brd_No1_SN_1, [Test_ KVDBA_kV_Matchpoint_Data].ID_Brd_Scan_2, " _
& "[Test_ KVDBA_kV_Matchpoint_Data].ID_Brd_No2_SN_1, Left([id_GE_Serial_No],8) AS Meter_SN_In " _
& "FROM [Test_ KVDBA_kV_Matchpoint_Data] " _
& "GROUP BY [Test_ KVDBA_kV_Matchpoint_Data].ID_GE_Serial_No, [Test_ KVDBA_kV_Matchpoint_Data].ID_BME_No, [Test_ KVDBA_kV_Matchpoint_Data].ID_Reg_Scan_1, " _
& "[Test_ KVDBA_kV_Matchpoint_Data].ID_Brd_Scan_1, [Test_ KVDBA_kV_Matchpoint_Data].ID_Brd_No1_SN_1, [Test_ KVDBA_kV_Matchpoint_Data].ID_Brd_Scan_2, " _
& "[Test_ KVDBA_kV_Matchpoint_Data].ID_Brd_No2_SN_1 " _
& "HAVING ((([Test_ KVDBA_kV_Matchpoint_Data].ID_Brd_No1_SN_1)=[Scan AMR Barcode])) OR ((([Test_ KVDBA_kV_Matchpoint_Data].ID_Brd_No2_SN_1)=[Scan AMR Barcode])) " '
[Form_Test_MP_SSN_Form].Form.RecordSource = SQL
Me.Repaint
Exit_BtnSSN_SN_Click:
End Sub
I want to use the value returned from field Meter_SN_IN as a keyword to query the subform. Below is the code for After Update Event.
Private Sub TxtMeter_SN_IN_AfterUpdate()
Dim SQL As String
SQL = "SELECT KVDBA_TTST_SET.ID_SERIAL_NO, KVDBA_TTST_SET.TMSTMP_TST_START, KVDBA_TTST_SET.TMSTMP_TST_END, KVDBA_TTST_SET.ID_CAT_NO, KVDBA_TTST_SET.CD_CAL_PASS, " _
& "KVDBA_TTST_SET.CD_CAL_REASON1, KVDBA_TTST_SET.CD_CAL_REASON2, KVDBA_TTST_SET.CD_CAL_REASON3, KVDBA_TTST_SET.ID_STN_NO, KVDBA_tssn_testdata.ncc_card_barcode, " _
& "KVDBA_tssn_testdata.calibration_pass_fail_status, KVDBA_tssn_testdata.ssn_result_code, KVDBA_tssn_testdata.customer_serial_no, KVDBA_tssn_testdata.ge_serial_no " _
& "FROM KVDBA_TTST_SET INNER JOIN KVDBA_tssn_testdata ON KVDBA_TTST_SET.TMSTMP_TST_START = KVDBA_tssn_testdata.calibration_date " _
& "WHERE [ID_SERIAL_NO] = '" & Me.Meter_SN_IN & "' " _
& "GROUP BY KVDBA_TTST_SET.ID_SERIAL_NO, KVDBA_TTST_SET.TMSTMP_TST_START, KVDBA_TTST_SET.TMSTMP_TST_END, KVDBA_TTST_SET.ID_CAT_NO, KVDBA_TTST_SET.CD_CAL_PASS, " _
& "KVDBA_TTST_SET.CD_CAL_REASON1, KVDBA_TTST_SET.CD_CAL_REASON2, KVDBA_TTST_SET.CD_CAL_REASON3, KVDBA_TTST_SET.ID_STN_NO, KVDBA_tssn_testdata.ncc_card_barcode, " _
& "KVDBA_tssn_testdata.calibration_pass_fail_status, KVDBA_tssn_testdata.ssn_result_code, KVDBA_tssn_testdata.customer_serial_no, KVDBA_tssn_testdata.ge_serial_no " _
& "ORDER BY KVDBA_TTST_SET.TMSTMP_TST_START " _
Me.SubCalSSNList.Form.RecordSource = SQL
Me.SubCalSSNList.Form.Requery
End Sub
Again, the main form works, and the subform also works. The problem is the subform does not run its query AfterUpdate of Meter_SN_IN
Thanks,
Keith
Your explanation is very clear and easy to follow.
Thanks.
Thank you!
Thank you so much! Very clear instructions.
I could not find a better video than this! Good job A++++
Liked and Subscribed. Very clear and concise tutorials
Hi Steve your videos are excellent.Thank you for your help.
Thank you so much for this video! Made my life way easier.
Thank you so much I’m making soft so fast now back then I was confused, thanks
Great video, very helpful, thank you for sharing
Thank you so much for this! This was well explained!
these is what i am looking for many years..
Excellent! Thank you very much, Steve.
You, dear sir, are a Rockstar!!
This is fabulous! Please tell me where I can find more videos like this one. I've been looking all over for Access "tutorials," but they seem to be very rare. I'm working in Access 2016, but the code worked just fine, and I'm jonesin for more!
OH man I love you so much! And every Tutorial you do
Hey, you really helped with my dissertation so thanks.
Hey Steve, great video! This was just what I needed for my application. However, in my case, I would like to add more objects into the table if it does not appear there initially. Do you have another video I can reference for this? I appreciate all the work you do, great job. :)
You explain in such a way that anyone could understand thanks a lot.
How can search records depends on two text fields and between two dates in a split form?
Thanks in advance
Thanks, easier than this impossible, what a masterful explanation
This is a GREAT tutorial video....how would you set up the form if you wanted to use check boxes as inputs (hard code options) to the search filed as well?
best video I found for this feature, thanks a lot! :)
Thank you so much for your videos! They have been amazing. I was wondering if this code would work for numbers instead of words, meaning can I set it up to search for ID # instead of names?Thank you :D
Best tutorial of its kind
Hey Steve, I’ve learned so much from your channel, thank you! Question: how do in filter for a value come from another query. e.g. filter for users entering data from the users table on an orders table.
Thank you for this great tutorial :)
I'm having an issue - my form returns only first row of my query, although query itself gets it all. Would you maybe know what am I doing wrong?
Thank so much steve, this was a life saver.
Really clear explanation. Learned a lot.
Really these videos helped me a lot in my daily job. I have question that how can we able to multi record selection through check box in continuous form and record those selected records record in table.
Thanks so much. Should you treat a split form as a continuous form or a listbox?
TNice tutorials is so amazing thanks for the videooo ;D
Hi, thanks for the great video! Do you have a video outlining how to export the results from listboxes to an excel document? Thank you
Thank you, well well explain, best I have seen....
Steve as always your videos are clear, understandable and right to the point. Super job. In viewing this video, I was looking for an implementation that is built into Excel. What, I mean is if you enter a name of any other search string into the Excel search box the search scope the every column in every row in the spread sheet. Can this be implemented in Access and is there a video that demonstrates it?
Hi Steve, Thanks a lot. This is one of the best explaining video i'd ever seen. Can we use this method for combo box search also?. Advance thanx for your reply.
thank you so much man, i LOVE IT
Great tutorial and well explained. What will you do if the input are integers or numeric?
Hello. I love your videos. They have helped me more than you know. Quick question: I ran into an error if the search field has values not contained in the data being searched. How could I prevent this, and perhaps return blank search in such instances? Thanks so much!
First of All, Thank You for putting the video together. It is very helpful. I am running into a problem where some of my keywords are not pulling the information from the table in the search form. I recreated the search/form and still have the same problem. I recreated a basic search query and it works fine. Any assistance would be greatly appreciated.
Tahnk you, also for anyone watching : it works with multiple or statments (at least works for 20 or statements for me) :D
I have my subform tied to a query that is pulling data from a table created by an Excel import. I tried to use the SQL from the query but it did not work. Is there another way I can achieve the same results with my configuration that are in this video? Or do I need to recreate my subform based on the table instead? Doing that would put a kink in the workflow I'm trying to achieve because I'm going to be importing Excel data each month and appending the table from which the query is based. Thank you for these wonderful instructional videos!
Great video. Thank you. Is there a way to search for multiple keywords at the same time? IE: "Elizabeth, John" and have results for Elizabeth or John show up on one query.
This was an excellent video, subs all around.
I know it is 2017 but this video tutorial is excellent.
Thank you!
helping me in 2018!
Thank you steve,i would like to know can i substitute the keyword search for a number search ?
Extremely useful video..well explained
This really helped me out!
thanks you so much
I want to ask you: Can I use a variable as the name of a control in Microsoft Access VBA? please reply. Thanks
Help me
Thanks! I've used this video a few times. Would I be able to search by a date field as well with this syntax? I haven't been successful so far...
Excellent tutorial video
Great video! Access Form has on navigation buttons little search field, but it is small. In your future video, can you show how to create text box with same functionality to search entire form and highlight what has found from VBA?
Very nice and clear video
Thank you sir
I have a question
How can I display a report of the results in that area instead of the list box or the continuous form ?
Thanks for your wonderful videos! You've saved me more than once. :)
+António Júlio Sousa You're welcome!
Mr. Bishop, Would this key word search be able to be adjusted to allow you to search different tables all at the same time. If you are handling different parts of a project, and they are handled in different tables, would you be able to set up a search just like this but that will look through 3 or 4 tables? if so would you be able to help me with the coding that would allow that? thank you for your time and the AMAZING videos.
Thank you, very helpfull. I used the same code in an access report, however, the keyword textbox does pass the value of my keyword. Need your help.
Thanks alot sir
you have easy way to explain
many thanks
Hi, thank you for this video, it helped me a lot; however, I'm still struggling a bit. Basically, what I did is created a library database in which I had recorded almost 800 books. Now, in order to be able to find items quickly, I created two search buttons: 1) to find authors (which works like magic) and 2) to find books - unfortunately, whenever I try to search a book, genre, etc. I get the following message: Run-time error '3079': The specified field 'Title' could refer to more than one table listed in the FROM clause of your SQL statement. Next when I click debug the following statement is highlighted: Me.subLibraryList.Form.RecordSource = SQL
I'm at a loss and pretty frustrated at this point and have no idea how to fix this. Please help. To give you a better picture (hopefully!) I've pasted below the two codes I'd associated with the said buttons. Thank you in advance.
Private Sub btnSearch_Click()
Dim SQL As String
SQL = "SELECT Authors.[Last Name], Authors.[First Name], Authors.[Middle Name], Authors.[Second Author], Books.Title, Books.[Sub-title], Books.Series, Books.Notes, Books.Genre, Books.Location " _
& "FROM Authors INNER JOIN Books ON Authors.[Author ID] = Books.[Authors ID] " _
& "WHERE [Last Name] LIKE '*" & Me.txtKeywords & "*' " _ & " OR [First Name] LIKE '*" & Me.txtKeywords & "*' " _ & " OR [Middle Name] LIKE '*" & Me.txtKeywords & "*' " _
& " OR [Second Author] LIKE '*" & Me.txtKeywords & "*' " _
& "ORDER BY Authors.[Last Name] "
Me.subLibraryList.Form.RecordSource = SQL Me.subLibraryList.Form.Requery
End Sub
______________________
Private Sub
btnSearchBook_Click()
Dim SQL As String
SQL = "SELECT Authors.[Last Name], Authors.[First Name], Authors.[Middle Name], Authors.[Second Author], Books.Title, Books.[Sub-title], Books.Series, Books.Notes, Books.Genre, Books.Location " _
& "FROM Authors INNER JOIN Books ON Authors.[Author ID] = Books.[Authors ID] " _
& "WHERE Title LIKE '*" & Me.txtSearchBook & "*' " _
& " OR [Sub-title] LIKE '*" & Me.txtSearchBook & "*' " _
& " OR Series LIKE '*" & Me.txtSearchBook & "*' " _
& " OR Notes LIKE '*" & Me.txtSearchBook & "*' " _
& " OR Genre LIKE '*" & Me.txtSearchBook & "*' "
Me.subLibraryList.Form.RecordSource = SQL
Me.subLibraryList.Form.Requery
End Sub
Great Video, Thank you for this tutorial! I do have a question however. What if you're creating an entry form and you create a search box to bring up a record. Once you're done with the record, you have a clear blank Search box and it should bring up a clean, empty form. However you hit the Search button on an empty Search text box and it brings up the first record. What code would you have to place into the Search button that brings up a clear form when ever the Search Text Box is empty?
Question: how do I set the records in the list box to open up the highlighted record when I click on it? (I set the on click function to open the current form however when I click on an item from the list it does not change what record it opens)
Thank you so much!!!! From Azerbaijan!