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: ua-cam.com/channels/eXAUvo5xxDY_b-lSknPC1A.html
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 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.
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.
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.
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
In case you're not aware, once the form has a recordsource, you can use the ribbon>>Form Design>Add Existing tools button to select fields form the recordsource and drag them onto the form. Then use ribbon>>Form Arrange>>tabular to put the controls into a very useable grid layout.
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.
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.
Steve, who ARE you? You are doing such a great public service to we who are learning to work with Access. When I send you a question, you reply within half an hour AT THE MOST. For example, are you independently wealthy so that you don't need to have an actual paid job? Or maybe you're a Catholic monk or a Buddhist monk and this is your humble service to humanity. Please explain! Thx.
Hahah.. well I'm certainly not wealthy. I am a software developer for a small father/son company. I do these videos mostly on the weekend when I get a chance. I wish I could do it much more often but I have a family who wants my time too.
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!
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?
Another Suggestion: For those not fluent in SQL one can create a query that does the lastname search and then get into SQL view and cut the code. Then just paste it into the subroutine and adjust the syntax with the appropriate quotes and &'s and replace the criteria value the the control name. It is often easier to debug the query beforehand this way for more complicated queries.
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?
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.
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.
Richard...I like the way you instruct. So many instruct, but do not tell one what property to place coding in and why. Also, can you present a single form with a one control search, where one can use LIKE operator with wildcards, to search on any field.
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!
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.
Excellent! Answered many of my questions. Have another one. Can I use this type of search, but KEEP the results by appending the search to the end? Ultimately, I want to use the search results to create labels for only a few records in my table.
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?
This is a great video. Learned so much from it. I am just curious how I can write code if I wish to print the searched result especially using the continuous form? Thanks will wait to hear your response.
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
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?
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 ?
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!
+Tom Carnaha I have found datasheets to be of no use at all. I would much rather make my forms look a very specific way for the user to help them with the flow of data. You can make the textbox widths as wide as you want. You can use Anchoring to allow the width of the textboxes to change with the size of the form so people can resize the window and the textboxes will resize with it.
hi Steeve. i have created one tbl and in this table i have one field named 'HOUSE NO'. in this field i have suppose 4 members under house no 1. Now I wish to show house no 1 including all 4 member on one go. How can I search all 4 member by clicking just house no 1? please give me idea. i am using access 2007. Thanks
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
Suggestion: When creating a form define your data source first. Then to add fields just use Design--> Add existing fields, drag and drop. They will be bound and named with appropriate labels. In your example you can then just cut and paste the labels to the header area.
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
Very great video but i have a very important question. What if when i clicked the search button, i then want it to give me a parameter box and a drop down of the column headings im specifically trying to search in?
I DO HAVE A QUESTION AND YOU SAID FEEL FREE. HOW TO DO MULTIPLE KEYWORD SEARCH. I SEARCH FOR "A,B,C,D" AND THE FORM WILL SHOW ME DETAILS OF THOSE 4 ITEMS AT THE SAME TIME. HOW TO DO THAT? THANK YOU SIR! YOU CREATE GOOD VIDEOS. OLRAYT.
I watched a video on the split function which 'could' perhaps be combined with a for each loop? which I figure might have something to do with this, but yes, I was just about to ask this question myself...
Steve, first & foremost - THANK YOU. I've learned quite a bit of programming from your videos. Awesome work! However, I'm finally stumped on this: How can I apply THIS SEARCH TECHNIQUE to the following... I have a MAIN FORM which displays a Two Tab sub-form, each displaying a Datasheet (basically an index); one is for Business Clients and the other is for Public Clients. At the bottom of the form, I have the SEARCH text box and "Search" control button. The problem I can't figure out is, how do I apply the search code you provided so IF the user is looking at the "Business" clients tab, the search is applied to that specific datasheet (subform?) as well as when the user is looking at the "Public" clients Tab the search applies to that specific datasheet (subform)? I hope this makes sense... (Wish I could provide a screenshot, but it's my first time ever replying to anything on UA-cam, so I'm not too sharp on how all the comment features may work.) Thank you in advance for any feedback you could provide. And again -- Great Job on the instructional videos!!!
Do you ever try to use the datasheet view to do the same thing? I noticed that it doesn't seem like you have much control over the width of the columns or the names of the headers. Is there a trick to doing it with a datasheet?
This was a very good video and I enjoyed following along. I got the search to work perfectly but when I brought in other fields within the subform they looked fine scrolling through before I did the search but after the search/enter each field other than the fields in the string return #Name? . Can you direct me to another video?
the query pulls from a table of information. and when updating information, would a newly ran search yield results for the updated table? or is the search essentially a snapshot of a query that was done at the time? i think i know the answer to this question but i wanted to make sure before proceeding. It'll be an updated search right?
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!
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?
I generally prefer list boxes to continuous forms in that they're on the same form, have properties and methods that are (mostly) easier to use. My problem of course is that the user cannot resize or resort the columns. Sometimes I will put labels above the listbox which will change the ORDER BY if double-clicked, but still a lack of flexibility.
Thank you Mr. Bishop, I used this code in a form and it worked even though I did not use a subform. However I found one character that gave me an error when I included it in the search. This is the ' (apostrophe) character. Do you or does anyone know why this happens?
Great tutorial. Very easy to follow. I have just encountered one problem. When I use the code breaker and am trying the where it cannot find the me.??? as i named it. Any ideas as to why please?
Dear Steve, these projects are pretty great. But I do have a few challenges, and I hope you would help out, possibly with a video that addresses this for others too who may be in the same shoe. I want to assign from a particular table, some sort of figures say coded thus: AT-009, tagged Entry_Information. Now, this is to be assigned as guests arrive at the venue, on the system I want each No. assigned to be removed from the available list, so it will not be re-assigned to another guest because we have limited space for guests. I will appreciate getting a way around this. 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
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: ua-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.
Not only it was comprehensive, you explain it clearly and step by step. THANK YOU,
Thank you! Clear, concise, and thorough - this is hard combination to find but your video has all three.
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.
Mahalo plenty, Steve! This lesson was *exactly* what I've been looking for. Your explanations were clear, concise, and thorough.
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.
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.
THANK YOU! Been struggling with this months and watched other tutorials but this was the only one that finally worked out!
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
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
In case you're not aware, once the form has a recordsource, you can use the ribbon>>Form Design>Add Existing tools button to select fields form the recordsource and drag them onto the form. Then use ribbon>>Form Arrange>>tabular to put the controls into a very useable grid layout.
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.
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.
Steve, who ARE you? You are doing such a great public service to we who are learning to work with Access. When I send you a question, you reply within half an hour AT THE MOST. For example, are you independently wealthy so that you don't need to have an actual paid job? Or maybe you're a Catholic monk or a Buddhist monk and this is your humble service to humanity. Please explain! Thx.
Hahah.. well I'm certainly not wealthy. I am a software developer for a small father/son company. I do these videos mostly on the weekend when I get a chance. I wish I could do it much more often but I have a family who wants my time too.
Thanks for this easy-to-follow tutorial. I found it exceptionally helpful.
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!
your videos are amazing, I feel very grateful to find you on youtube!! you are my favorite guy!!
Well 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!
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!
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?
Your explanation is very clear and easy to follow.
Thanks.
Thank you!
Please Steve, keep doing these very good tutorial videos. Many Thanks
Hi, I learned a lot from your video...Thank you... You were the best to explain how to create this search button.
In 2018 this is still a valuable video. :)
Thanks Bishop easy to learn from your tutorial.
Liked and Subscribed. Very clear and concise tutorials
I'm learning so much from your video tutorials! Thank you.
these is what i am looking for many years..
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
Thanks so much. Should you treat a split form as a continuous form or a listbox?
I love this video! Perfect instruction! Is there a way to output the results of the filtered subform to Excel?
I have the same question/problem...I need to send the results of my Search to an Excel spreadsheet.
I could not find a better video than this! Good job A++++
Another Suggestion: For those not fluent in SQL one can create a query that does the lastname search and then get into SQL view and cut the code. Then just paste it into the subroutine and adjust the syntax with the appropriate quotes and &'s and replace the criteria value the the control name.
It is often easier to debug the query beforehand this way for more complicated queries.
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?
Thanks Chris - I do appreciate your gift.
Clear and the best tutorial ive learned a lot from you. Thank you!
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.
Great tutorial and well explained. What will you do if the input are integers or numeric?
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.
Richard...I like the way you instruct. So many instruct, but do not tell one what property to place coding in and why. Also, can you present a single form with a one control search, where one can use LIKE operator with wildcards, to search on any field.
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!
thank you very much for this video. It shows clearly how to start working with VBA in Access
That's so great. Exactly what I was looking for. Thanks for sharing.
Really clear explanation. Learned a lot.
Thank you steve,i would like to know can i substitute the keyword search for a number search ?
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.
Excellent! Answered many of my questions. Have another one. Can I use this type of search, but KEEP the results by appending the search to the end? Ultimately, I want to use the search results to create labels for only a few records in my table.
Best tutorial of its kind
+Uriel Ramirez It is available in 720p. You might need to click on the gear icon to change your resolution.
Hi Sir, do you have a video for calculating running balance of inventory? Or generating Month-end summary reports for transactions?
Steve, thank you so much! You are a fantastic instructor!
You, dear sir, are a Rockstar!!
Great video! So now I have my keyword form. Do you have a video on how I can select one of the search results and have it take me to another form?
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?
This is a great video. Learned so much from it. I am just curious how I can write code if I wish to print the searched result especially using the continuous form? Thanks will wait to hear your response.
I know it is 2017 but this video tutorial is excellent.
Thank you!
helping me in 2018!
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
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?
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 ?
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!
Your very good on explaining access technical details.. Thanks for this videos :)
Thank you!
Great, will start learning from beginning 👍 it's really hard to find best videos 😄
Thankyou very much. How about search by integer, the SQL how to change?
+Tom Carnaha I have found datasheets to be of no use at all. I would much rather make my forms look a very specific way for the user to help them with the flow of data. You can make the textbox widths as wide as you want. You can use Anchoring to allow the width of the textboxes to change with the size of the form so people can resize the window and the textboxes will resize with it.
hi Steeve. i have created one tbl and in this table i have one field named 'HOUSE NO'. in this field i have suppose 4 members under house no 1. Now I wish to show house no 1 including all 4 member on one go. How can I search all 4 member by clicking just house no 1? please give me idea. i am using access 2007. Thanks
Very nicely done! Working on a db at the moment and this is very helpful 🙏
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
Suggestion: When creating a form define your data source first. Then to add fields just use Design--> Add existing fields, drag and drop. They will be bound and named with appropriate labels.
In your example you can then just cut and paste the labels to the header area.
Thanks, easier than this impossible, what a masterful explanation
I realize I’m 3.5 years late, but if you get this I fucking love you!
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
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.
Hello, can you do two search boxes? The second search box is dependent on the first search box? Thank you!
Very great video but i have a very important question. What if when i clicked the search button, i then want it to give me a parameter box and a drop down of the column headings im specifically trying to search in?
Hi Steve. Many thanks. How to open the selected record on a form? Matthew
Great vid. It helps me a lot. Is there a way to use multiple keywords?
I DO HAVE A QUESTION AND YOU SAID FEEL FREE. HOW TO DO MULTIPLE KEYWORD SEARCH. I SEARCH FOR "A,B,C,D" AND THE FORM WILL SHOW ME DETAILS OF THOSE 4 ITEMS AT THE SAME TIME. HOW TO DO THAT? THANK YOU SIR! YOU CREATE GOOD VIDEOS. OLRAYT.
I watched a video on the split function which 'could' perhaps be combined with a for each loop? which I figure might have something to do with this, but yes, I was just about to ask this question myself...
Great question. I am curious about this as well.
Steve, first & foremost - THANK YOU. I've learned quite a bit of programming from your videos. Awesome work! However, I'm finally stumped on this: How can I apply THIS SEARCH TECHNIQUE to the following... I have a MAIN FORM which displays a Two Tab sub-form, each displaying a Datasheet (basically an index); one is for Business Clients and the other is for Public Clients. At the bottom of the form, I have the SEARCH text box and "Search" control button. The problem I can't figure out is, how do I apply the search code you provided so IF the user is looking at the "Business" clients tab, the search is applied to that specific datasheet (subform?) as well as when the user is looking at the "Public" clients Tab the search applies to that specific datasheet (subform)? I hope this makes sense... (Wish I could provide a screenshot, but it's my first time ever replying to anything on UA-cam, so I'm not too sharp on how all the comment features may work.) Thank you in advance for any feedback you could provide. And again -- Great Job on the instructional videos!!!
Hi Steve your videos are excellent.Thank you for your help.
wonderful
Can the where clause be for the entire table or does it have to be for specific column?
Do you ever try to use the datasheet view to do the same thing? I noticed that it doesn't seem like you have much control over the width of the columns or the names of the headers. Is there a trick to doing it with a datasheet?
This was a very good video and I enjoyed following along. I got the search to work perfectly but when I brought in other fields within the subform they looked fine scrolling through before I did the search but after the search/enter each field other than the fields in the string return #Name? . Can you direct me to another video?
the query pulls from a table of information. and when updating information, would a newly ran search yield results for the updated table? or is the search essentially a snapshot of a query that was done at the time? i think i know the answer to this question but i wanted to make sure before proceeding. It'll be an updated search right?
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!
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?
I generally prefer list boxes to continuous forms in that they're on the same form, have properties and methods that are (mostly) easier to use. My problem of course is that the user cannot resize or resort the columns. Sometimes I will put labels above the listbox which will change the ORDER BY if double-clicked, but still a lack of flexibility.
Great info! How would I publish this table to an html page?
Thank you Mr. Bishop, I used this code in a form and it worked even though I did not use a subform. However I found one character that gave me an error when I included it in the search. This is the ' (apostrophe) character. Do you or does anyone know why this happens?
Thank you, well well explain, best I have seen....
Can I use this to search for words in a Long text field? that is also in rich text format?
best video I found for this feature, thanks a lot! :)
Thank you so much I’m making soft so fast now back then I was confused, thanks
Great tutorial. Very easy to follow.
I have just encountered one problem.
When I use the code breaker and am trying the where it cannot find the me.??? as i named it. Any ideas as to why please?
Dear Steve, these projects are pretty great. But I do have a few challenges, and I hope you would help out, possibly with a video that addresses this for others too who may be in the same shoe.
I want to assign from a particular table, some sort of figures say coded thus: AT-009, tagged Entry_Information. Now, this is to be assigned as guests arrive at the venue, on the system I want each No. assigned to be removed from the available list, so it will not be re-assigned to another guest because we have limited space for guests. I will appreciate getting a way around this.
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
good explanation and helpful. i really learnt a lot from this tutorial. thank you