Unreal. I have been looking for Something like this via google for weeks and been so confused by the"text based" learning... I needed to just see it ... What a fantastic find this video was. I'm going to go through and watch loads more. BRILLIANT. Thank you so much for your course(s)!
Loving these videos. 20 years ago I did a small amount of Access work but never got proficient at it. I've spent the last 10 years working with SQL (very basic query stuff) and in searching for help on some more complex queries stumbled across this series. I never realized just how good Access is and how useful it can be. Some of the query similarities are striking as well.
Yep! In fact, the people who usually hate on Access are the ones who don't know it well. "Oh, it's not a real database." Yeah! It certainly is! I've been working with Access for almost 30 years now, and not only have I made a career out of it, but it's hands-down the best FRONT-END rapid database development tool out there. Sure, SQL Server is a better back-end, but you can't beat Access for front-end design.
@@599CD Very quick indeed. Just for practice I built a rudimentary asset tracking system. 3 hours in and it was deployable. Could still use some tweaks and improvements but it's not bad. I don't think I could have done that back when using Access 97.
One of the greatest of your videos, I learned and enjoyed the information with your unique way of explanation. It would be guideline for decades as you said. God bless you.
Richard, Thank you for the Help. After Watching this video and the extended Member's cut. I was about to fix my issues. I have had been stuck for months and I don't know why I didn't watch them before. Thank you for the recommendations, You rock \m/ Have a wonder week.
I want to thank you for all the work you put into creating these videos. I am nolonger in the market, basically retired, but I do have a few projects in the works for personal use. I like the versatility and flexibility of Access and prefer VBA to Macros for the same reason. I am self taught in most of the languages I have used except Fortran, which I was taught in highschool along with Physics, yes I'm a dinosaur. Again, thank you for all the wonderful videos.
You're very welcome. I must be a dinosaur too because I learned FORTRAN in high school as well. They taught that and Modula-2 when I was begging them to teach something new (at the time) like C/C++. I bet my teacher that I could bring down the server within an hour or two. He laughed at me. The dummy didn't set disk quotas, so I wrote a recursive loop that filled up the hard drive with tiny random text files. I got called down to the office a few periods later. :/
Fortran course was "write name, address and phone number", took physics same yr and saw connection and began writing pgms in celestial mechanics, orbital velocity for earth, moon and other planets. Took courses in reverse order, computer science and data processing the following year. In dp had to make flow chart for "driving down road and have flat, flowchart change tire, most turned one or two pages in, I had 8 or 9, don't remember now. Instructor held up my papers and announced to class "this is is how you write a flowchart". Condition chks for weather, lanes, tools , tire, etc.
I've been watching your videos for a long time now. Extremely good, easy to understand. One question, though. At 18:06 you clear those textboxes with empty strings. Would it better to use "Null" when clearing textboxes? Empty strings gave me some trouble when I moved to SQL back end.
Thank you for the great tutorials. I am following your instructions but it is not working in my template because I have combo boxes for the field that I am trying to search
If I wanted a field to be exactly equal or be Null how would I enter that? I have [Form]![myform]![searchbox] Or is Null but that doesn't seem to work. Any suggestions? This is a combo box if that helps.
Great video once again. You don’t have a video showing how to put a simple search box into a form to do a live search for a record of the same name like the box at the bottom of access? Would be much appreciated Thanks a lot.
I get a ton of questions every day, and I don't have time to answer them all here on RUclips. Feel free to submit your question on my website at: 599cd.com/AskYT
Great video! No idea if you will see this but I had an issue that I just can't seem to fix. In one of my fields I need to sometimes search for things that include #. The issue is that # is a wildcard so it needs to be taken as a literal and every time I search with it I don't want to need to put brackets around it. Is there any good query I can put in criteria that could fix this? Also, how could I edit the search box to work for dropdowns? Edit: I found a way to allow searches with #!
Hi Richard, I'm loving your tutorials learning a lot! I have a question though, I've done this lesson 3 time now with the same results, it works brilliantly as a single form but if you use this form as a subform it pops up a msgbox asking for parameters on the FirstName and LastName Search fields? I've cheked all named objects to see if it was a fault there but no can't find the problem, Any ideas?
Yeah, this wasn't designed to be used with a subform. It will require you to understand how to reference fields on a subform. I would recommend putting the search boxes in the form header of the subform, and then you won't need to worry about that. Don't put the search boxes in the parent form; the whole lot will be easier that way.
Hi Richard. I keep getting stuck in similar places. I created a "customer table" this stores customer info such as first name etc. I then create a second table which stores info on their shortterm policies. I don't recreate "first name, last name" as you have often mentioned bot duplicating info. So the two tables link by means of an ID. So on the "policy table" when i open the form I managed to get it to show the clients "first name, last name" however in the table itself it stores the link as a number. So now when I try and include a search button the field has the client "ID" which is a number and not a name. How do I get around that? Thanks for all the great lessons. Dominic - Silver Member from South Africa
I used to use Access a lot for work and knew most of this stuff, but haven't used it for about 20 years. I'm now re-learning it just for personal use (because I'm a geek), and your videos are amazingly clear and helpful. I had a lot of yes/no fields in my main table and I've now gone and changed them to a separate table (and join table) where I can add tags at will, with a subform. Can you point me to a video that will help with doing searches using fields on the main form AND the subform, please?
Dear Sir I hope this message finds you well. I worked extensively with Microsoft Visual Basic (VB) and MS Access around 25 years ago. While I feel out of practice now, I still have a good understanding and would like to reconnect with these technologies. Would it be possible to share some sample files or projects with VB code and MS Access to help me trace and understand the methods again? I am happy to pay for any resources or guidance you can provide. Looking forward to your response. Thank you for your time and assistance! Best regards,
Great video! One question I discovered - in using the query as the record source for what would be CustomerListF, the Open Customer button no longer works. If I put the record source back to CustomerT, it works but then the Requery button does not. What’s the fix to get them both to work?
@@599CD Thank you for the reply! I'm building a vendor management database which is having the same issue. Looks like I'll just have to work my way through the SQL lessons! Greatly appreciate all of this instruction and content. I've been wanting to learn and utilize Access for numerous years, and couldn't until I started on your lessons. This is exceptional content, guidance, walkthroughs, and support, and very thankful you have built it all!
In my app, I use just one search box to search all fields all at once. That saves the user time of having to type in multiple boxes. Searching "francis" will return names, cities (San Francisco), etc. that has that word. Yes, the query is more complicated, but it would be a nice teaching moment for your students: e.g. Select * From Customers Where Instr(1, FirstName, 'francis')0 Or Instr(1, LastName, 'francis')0 Or Instr(1, StreetAddress, 'francis')0 ...
Hi there! Is that a way to 7sr a Ajax search in the access on which it will show the search result immediately as you type in the field and not pressing TAB button?
Very informative. The problem I'm having though is when I load the form or clear the query I'd like to show the null fields but when I search the field I'd like to not show null. SO when I add "IS NOT NULL" it will load the continuous form with the blank fields and during my search criteria it will show null as well. When I run the criteria [Like "*" & [Forms]![MarList]![MirSearch] & "*"] on load or clear it will not display the null fields.
First off Great Videos, these have helped me build a usable database for my work that help us coordinate and track almost everything in our department. I've been adding more features as the database grows and am completely stumped on one task I would like the database to do. I'm trying to figure out a way to do a anti-search of sorts. I want to know what employees have not attended a class for instance. I can get a query to return students that have been to a certain class, say Supervisor Class, but when I try to figure out who has not been to the Supervisor class it'll still bring up people that have been due to the fact that they attended other classes as well. Been racking my brain over this for about two weeks now but google searches and videos have come up empty. I feel the solution is super simple and it'll make total sense once I see it, but as of now it's just out of grasp. For example in your sample database it would be like to find all the customers that have not ordered a certain product. Doing a simple order query would still bring up people that have ordered the product due to them ordering other products and the query will list them multiple times for all the products that they did order. About ready to abandon this and just export everything to an excel document and search that way, so you and RUclips commenters are my final hope :)
It's funny you ask this because I've got a video on this topic coming up very soon! One of my members asked something very similar. Stay tuned. Don't give up.
Thank you so much I tried and it works great for the firist time When I try it for the second time when I write "like" it is changed to "Alike" what should I do?
So I’m looking for a way to immediately update the continuous form without leaving the record BUT at the same time store a value in a table. Is it possible at all? Reason is I have continuous form with products. But the total price in the form footer doesn’t update before leaving the record. And I do want it to.
So I followed these steps and it worked great! Everything was working so I was making some design changes (Moving the positions of the fields, Box sizes, colors, ect.) I came back a few minutes later and now it has broken and is asking for parameter values every time I try to open the form or query. I haven't changed any of the names of the boxes and even used the expression builder to make sure everything was spelled correctly. I deleted everything and started from scratch and the same thing happened. It worked for a few minutes then went back to asking me for Parameter Values when opening the form or query. Its driving me nuts. Any idea what is going wrong?
So literally seconds after posting this I found that in the code for the form there was an empty Private Sub that I mistakenly made after writing button code that I must have exited from. I deleted that and now everything works fine! I'll leave this up in case someone else somehow manages to accidentally produce this error
I get a ton of questions every day, and I don't have time to answer them all here on RUclips. Feel free to submit your question on my website at: 599cd.com/AskYT
Hi! I’m working on converting an existing db to an online db. The problem I’m running in to is there is a multivalue field I use to search specific items. Is there any alternative way to doing this in an online database??
Excuse me, do your table's column width can be adjusted freely by user if the value of the field is too long to appear on the field? If you already covered this in the your other video, can you show the link? For the limitation of my database and user knowledge, i need to give the user some sort of way to adjust the column width like what you do on excel's column.
One of my members actually asked a similar question recently, so I'll be making a TechHelp video on this soon. I'll be showing how to do it with VBA in the Extended Cut.
When I change my record source to the query I lose all my original data, which makes perfect sense to me, if I'm not pointing my form to the original table how can can the datafields be populated?
@RichardRost .. Im curious.Hope its not too intrusive. How did you learn all this where a lot just didnt? .. A lecturer at Uni sold me a computer .. when i opened i found a hidden application (not used), in either dos or files/folder.. about 2000ad..
Oh.. You definitely good at applying yourself to your findings . I'll have a read .. I find remembering this subject matter difficult even with practice.. it gets forgot.. Thx.. 😊
Please help! I followed your steps and everything worked exactly as intended with all my practice data, but with any new records added after I created this search box, they don't show up. They'll appear in the list when I open the search form, but the moment I hit Requery, or Clear Search, they disappear, even if nothing was entered. The Form load and clear search have the same exact code and the Requery button just doesn't have the Setdefault. Any idea what could be the issue?
THANKS FOR THE VIDEO HOW I CAN SEARCH FOR NUMERIC VALUES THAT I INSERT IN ONE SEARCH BOX IN MULTY COLUMNS LIKE WHEN I TYBE "R" IN "FIRST NAME" BOX IT SEARCH IN THE BOTH COLMUNS "FIRST NAME" AND "LAST NAME"
Love your videos! I have a problem with the criteria in the Search Query. When I put in the criteria, it says "does not recognize '[Forms]![SearchF]![SearchBox]' as a valid field name of expression." I used the suggested fields so I'm not sure what I am doing wrong?
Something is probably spelled wrong, or you have it in the wrong spot. It's impossible for me to tell without seeing your database. That's why I encourage my students to upload screen shots to my Forums. 599cd.com/AF
@@599CD I guess I wasn't quite clear when I originally asked the question. So I have multiple search forms which when opened individually work fine on their own however when I then try to put all of these forms onto a navigation form each search form upon selection from within the navigation form has an enter parameter value pop-up for each text box I use as a criteria for the search. Do you know how to prevent this?
@@tylerpatsula8805 Are you trying to put the forms with working search boxes into another form as subforms? I'm running into the same problem and I think it's because of the way the query is written. In his "bang v dot" video, he mentions towards the end about how to reference a subform versus a main form. I'm having the same problem and I think that's why. Did you manage to fix your problem?
I get a ton of questions every day, and I don't have time to answer them all here on RUclips. Feel free to submit your question on my website at: 599cd.com/AskYT
Great video and love watching. Have you do a multi search that updates as you type. But with tick boxes of different categories. For example: inventory Categories 1. Electrical goods 2. Plumbing items 3. Stationary Ok a short list of three but if ticked category 2 for example You have auto update search for part numbers as wildcard that contains part on the number but only in categories2 Or you do only search to what ever is ticked. If none ticked then search in all . If you know what I mean
I cover "search as you type" in my Search Seminar. Adding the check boxes would be simple too with the techniques I show there. 599cd.com/SearchSeminar
Sir How to manage font size of ms access report fields through other form. So user has right to change font size of report without opening report in design view in ms access.
That's tricky, but I do have a video coming out on this subject soon. I'll be covering it in my Holiday Greeting Card Mailing List Seminar very soon. Watch my web site for it. 599cd.com
Hello Mr. Rost, Your video was very helpful and the continuous form is exactly what I needed to show all of my inventory. I do have a question though, I designed my SearchList form (called frmSearchList) almost identical to the way you designed your Customer List. In my query, I placed 5 fields. (PartNum - AutoNumber), (PartDescription - Short Text), (CategoryDesc - Short Text), (SubCategoryDescription- Short Text), and (Available_Inventory - Number). There are times where some of my items do not contain a SubCategoryDescription and are meant to stay blank. In my query i placed the criteria as Like "*" & [Forms]![frmSearchList]![txtSubCategory] & "*" Or Is Null. My frmSearchList form is not returning blank records. Do you know where I may have gone wrong?
Hello Mister Rost, it's very useful tips How to display a dialog window which proposes to automatically add a missing element of the search form in the table? To make data entry management more efficient.
Thanks for the videos you provide. I have a question. Can you add a form footer total for the "IsAvtive" When the Tri state check box is clicked. EX. Null/Yes/No. It would be cool if a form footer total would sum the value when each is checked. Thanks Update: I used this as the Control Source, Sum(IIf([IsActive],1,0)) ,In a Text Box in the form footer. Works Great
This is not working for me at all. Whenever I try to run the query it shows a blank page. I’ve tried everything. I’m not getting errors so I wonder what might be wrong?
This method just is not working for me and I cannot figure out what I am doing. I went so far a making a similar simple database with no luck. I keep getting a pop-up asking for the perimeter. When I input the value in the search field, the perimeter pop-up comes up. Am I using an incorrect version of Access?
@@599CD Turns out I was searching for numbers instead of letters. Fixed that and I was able to perform the search using a City field with the query and the desired records showed up in the query. The records do not show up in the form. All records still are listed. I checked all the criteria, spellings and everything matches. I am at a loss.
I am having no luck in getting the form to requery. I can requery the query and it gives the results I ask for, but it does not go into the form. Frustrating.
Not sure if anyone has caught this but on the code builder for the clear search the line of code is FirstNameSearh = " " and so on. Had a person go in, type a letter and just click clear search and nothing would pupulate. They tried to put in a letter and nothing. I found that that is because " " is equivalent to a space. If you just do the code to be "", that solved it. Just wanted to say. Don't think it will happen often but just fyi
It just takes practice. You'll remember it eventually. I've tried making one of those "quick reference sheets" for Access, but there's no way to make it useful... if you put everything on the sheet that you need the font gets REAL small. :)
This video was immensely helpful; until I got to the point where I type in the search box. I could not type anything in the box. I didn't even get a curser. I thought I followed the instructions to the letter. Where did I go wrong?
Without seeing what you've got, it's hard to tell where you went wrong. If you can't even type in the text box, make sure it's not locked and you have Allow Edits set to yes.
I figured it out. Really hard to find the properties button for the right part of the form. Just went through an O365 update and they keep changing things.
I have been trying this for a day or so. I have the LIKE "*" & Forms!ContinousForm!btm1& "*" or null. Same for a second btn. but it askes me for a parameter value(Forms.Shared with.Value) has anyone run into this issue before?
Unreal. I have been looking for Something like this via google for weeks and been so confused by the"text based" learning... I needed to just see it ... What a fantastic find this video was. I'm going to go through and watch loads more. BRILLIANT.
Thank you so much for your course(s)!
This has to be one of, if not the best, Access instruction videos on the Internet. Richard, you are an Access Guru.
Thanks!
you're fantastic. you've assisted me a lot in programming course. thank you sir
smart teaching
Thanks
Loving these videos.
20 years ago I did a small amount of Access work but never got proficient at it.
I've spent the last 10 years working with SQL (very basic query stuff) and in searching for help on some more complex queries stumbled across this series. I never realized just how good Access is and how useful it can be.
Some of the query similarities are striking as well.
Yep! In fact, the people who usually hate on Access are the ones who don't know it well. "Oh, it's not a real database." Yeah! It certainly is! I've been working with Access for almost 30 years now, and not only have I made a career out of it, but it's hands-down the best FRONT-END rapid database development tool out there. Sure, SQL Server is a better back-end, but you can't beat Access for front-end design.
@@599CD Very quick indeed.
Just for practice I built a rudimentary asset tracking system. 3 hours in and it was deployable. Could still use some tweaks and improvements but it's not bad. I don't think I could have done that back when using Access 97.
One of the greatest of your videos, I learned and enjoyed the information with your unique way of explanation. It would be guideline for decades as you said. God bless you.
Haha thanks!!!
Can you create invoice that includes store goods, GST,Total and others....
Great Video Rich, you're really helping me out create a database from scratch for my company.
Glad to help!
Richard, Thank you for the Help. After Watching this video and the extended Member's cut. I was about to fix my issues. I have had been stuck for months and I don't know why I didn't watch them before. Thank you for the recommendations, You rock \m/ Have a wonder week.
Glad it helped
have learnt so much watching this and all your other videos. Makes it so much easier to learn and understand. Thank you Richard you're a great tutor
Welcome
I want to thank you for all the work you put into creating these videos. I am nolonger in the market, basically retired, but I do have a few projects in the works for personal use. I like the versatility and flexibility of Access and prefer VBA to Macros for the same reason. I am self taught in most of the languages I have used except Fortran, which I was taught in highschool along with Physics, yes I'm a dinosaur.
Again, thank you for all the wonderful videos.
You're very welcome. I must be a dinosaur too because I learned FORTRAN in high school as well. They taught that and Modula-2 when I was begging them to teach something new (at the time) like C/C++. I bet my teacher that I could bring down the server within an hour or two. He laughed at me. The dummy didn't set disk quotas, so I wrote a recursive loop that filled up the hard drive with tiny random text files. I got called down to the office a few periods later. :/
Fortran course was "write name, address and phone number", took physics same yr and saw connection and began writing pgms in celestial mechanics, orbital velocity for earth, moon and other planets. Took courses in reverse order, computer science and data processing the following year. In dp had to make flow chart for "driving down road and have flat, flowchart change tire, most turned one or two pages in, I had 8 or 9, don't remember now. Instructor held up my papers and announced to class "this is is how you write a flowchart". Condition chks for weather, lanes, tools , tire, etc.
Thanks!
Thank you very much. I appreciate that. :)
this guy is a genius. i have learnt so much
What a super video! Thank you, very helpful!
Welcome
Excellent video...you give clear and concise instructions while simultaneously enjoying what you do!
Glad you enjoyed it!
My man, you're a MSAccess wizard 👍(I enjoyed your presentation)
Thanks
That is an excellent video. It covered more than I thought it would and it has been extremely useful.
Thanks
Great video. Thanks for sharing your knowledge.
Welcome
This was an extremely helpful video. Thanks so much for explaining everything so well.
You're very welcome!
I've been watching your videos for a long time now. Extremely good, easy to understand. One question, though. At 18:06 you clear those textboxes with empty strings. Would it better to use "Null" when clearing textboxes? Empty strings gave me some trouble when I moved to SQL back end.
Yeah, SQL does tend to prefer NULL. In Access, I prefer using empty strings.
Excellent brother your are excellent teacher Allah bless you
thanks
Helped a lot for work, 1 question though. What can I do so that the user can still type in the search boxes, but not edit the records
This is the best and ease learning Thank you
Welcome
Very good! I like it!!! I am from Brazil, I am training to learning Microsoft Access! I am Microsoft Office Specialist Excel (MOS)
Nice to meet you. I'm in Florida. Always wanted to visit Brazil. :)
This is great. Love your videos.
Thanks so much!
Oh man that's a life saver. Thanks for sharing.
Welcome
Thank you for the great tutorials. I am following your instructions but it is not working in my template because I have combo boxes for the field that I am trying to search
Yeah, that adds a level of complexity to it. 599cd.com/SearchSeminar
If I wanted a field to be exactly equal or be Null how would I enter that? I have [Form]![myform]![searchbox] Or is Null but that doesn't seem to work. Any suggestions? This is a combo box if that helps.
599cd.com/Ask
Great video once again.
You don’t have a video showing how to put a simple search box into a form to do a live search for a record of the same name like the box at the bottom of access?
Would be much appreciated
Thanks a lot.
I get a ton of questions every day, and I don't have time to answer them all here on RUclips. Feel free to submit your question on my website at: 599cd.com/AskYT
You're a life saver!
Great video! No idea if you will see this but I had an issue that I just can't seem to fix.
In one of my fields I need to sometimes search for things that include #. The issue is that # is a wildcard so it needs to be taken as a literal and every time I search with it I don't want to need to put brackets around it. Is there any good query I can put in criteria that could fix this?
Also, how could I edit the search box to work for dropdowns?
Edit: I found a way to allow searches with #!
Hi Richard, I'm loving your tutorials learning a lot! I have a question though, I've done this lesson 3 time now with the same results, it works brilliantly as a single form but if you use this form as a subform it pops up a msgbox asking for parameters on the FirstName and LastName Search fields? I've cheked all named objects to see if it was a fault there but no can't find the problem, Any ideas?
Yeah, this wasn't designed to be used with a subform. It will require you to understand how to reference fields on a subform. I would recommend putting the search boxes in the form header of the subform, and then you won't need to worry about that. Don't put the search boxes in the parent form; the whole lot will be easier that way.
Hi what are you doing on 21.26? Which button are you pressing or typing so the all the tables are showing again? (In the VBA)
599cd.com/QQ
Enjoy and make me learn a lot
Thanks
Hi Richard. I keep getting stuck in similar places. I created a "customer table" this stores customer info such as first name etc. I then create a second table which stores info on their shortterm policies. I don't recreate "first name, last name" as you have often mentioned bot duplicating info. So the two tables link by means of an ID. So on the "policy table" when i open the form I managed to get it to show the clients "first name, last name" however in the table itself it stores the link as a number. So now when I try and include a search button the field has the client "ID" which is a number and not a name. How do I get around that? Thanks for all the great lessons. Dominic - Silver Member from South Africa
I used to use Access a lot for work and knew most of this stuff, but haven't used it for about 20 years. I'm now re-learning it just for personal use (because I'm a geek), and your videos are amazingly clear and helpful. I had a lot of yes/no fields in my main table and I've now gone and changed them to a separate table (and join table) where I can add tags at will, with a subform. Can you point me to a video that will help with doing searches using fields on the main form AND the subform, please?
599cd.com/Ask - and thank you for your compliments
Dear Sir
I hope this message finds you well. I worked extensively with Microsoft Visual Basic (VB) and MS Access around 25 years ago. While I feel out of practice now, I still have a good understanding and would like to reconnect with these technologies.
Would it be possible to share some sample files or projects with VB code and MS Access to help me trace and understand the methods again? I am happy to pay for any resources or guidance you can provide.
Looking forward to your response.
Thank you for your time and assistance!
Best regards,
Mindblown!!! Thank you so much!!! 😃
Hope I didn't hurt ya too bad. :) You're welcome.
Your Tutorials are amazingly good. On this one, though, I don't understand what happens between minute 21.17 and 21.40. somebody explain.
Great video! One question I discovered - in using the query as the record source for what would be CustomerListF, the Open Customer button no longer works. If I put the record source back to CustomerT, it works but then the Requery button does not. What’s the fix to get them both to work?
Don't change the record source. CustomerT is coded into the SQL in the VBA. You'd have to change it there too.
@@599CD Thank you for the reply! I'm building a vendor management database which is having the same issue. Looks like I'll just have to work my way through the SQL lessons! Greatly appreciate all of this instruction and content. I've been wanting to learn and utilize Access for numerous years, and couldn't until I started on your lessons. This is exceptional content, guidance, walkthroughs, and support, and very thankful you have built it all!
In my app, I use just one search box to search all fields all at once. That saves the user time of having to type in multiple boxes. Searching "francis" will return names, cities (San Francisco), etc. that has that word. Yes, the query is more complicated, but it would be a nice teaching moment for your students: e.g. Select * From Customers Where Instr(1, FirstName, 'francis')0 Or Instr(1, LastName, 'francis')0 Or Instr(1, StreetAddress, 'francis')0 ...
Yep. I cover that in my Search Seminar: 599cd.com/SearchSeminar
How to make search box as(while) we type at that time filter form(on that second filter form)??
Hi there!
Is that a way to 7sr a Ajax search in the access on which it will show the search result immediately as you type in the field and not pressing TAB button?
Yeah, there's a Key Press event. 599cd.com/ACD8
Very informative. The problem I'm having though is when I load the form or clear the query I'd like to show the null fields but when I search the field I'd like to not show null.
SO when I add "IS NOT NULL" it will load the continuous form with the blank fields and during my search criteria it will show null as well.
When I run the criteria [Like "*" & [Forms]![MarList]![MirSearch] & "*"] on load or clear it will not display the null fields.
Hi, did you ever figure out the solution to the problem? I am encountering the same issue as well.
First off Great Videos, these have helped me build a usable database for my work that help us coordinate and track almost everything in our department. I've been adding more features as the database grows and am completely stumped on one task I would like the database to do.
I'm trying to figure out a way to do a anti-search of sorts. I want to know what employees have not attended a class for instance. I can get a query to return students that have been to a certain class, say Supervisor Class, but when I try to figure out who has not been to the Supervisor class it'll still bring up people that have been due to the fact that they attended other classes as well.
Been racking my brain over this for about two weeks now but google searches and videos have come up empty. I feel the solution is super simple and it'll make total sense once I see it, but as of now it's just out of grasp.
For example in your sample database it would be like to find all the customers that have not ordered a certain product. Doing a simple order query would still bring up people that have ordered the product due to them ordering other products and the query will list them multiple times for all the products that they did order.
About ready to abandon this and just export everything to an excel document and search that way, so you and RUclips commenters are my final hope :)
It's funny you ask this because I've got a video on this topic coming up very soon! One of my members asked something very similar. Stay tuned. Don't give up.
Thank you so much
I tried and it works great for the firist time
When I try it for the second time when I write "like" it is changed to "Alike" what should I do?
599cd.com/a?10427
I can't seem to get the Setdefaults to work but it works when I hit clear
I thought it was because I forgot the # but that doesn't seem to be it
So I’m looking for a way to immediately update the continuous form without leaving the record BUT at the same time store a value in a table. Is it possible at all? Reason is I have continuous form with products. But the total price in the form footer doesn’t update before leaving the record. And I do want it to.
In the AfterUpdate event for each textbox, you'll need a Me.Refresh. See 599cd.com/AfterUpdate
I cover this in my Invoicing lessons, but I've also had this on my list to make a TechHelp video about it, so stay tuned...
So I followed these steps and it worked great! Everything was working so I was making some design changes (Moving the positions of the fields, Box sizes, colors, ect.) I came back a few minutes later and now it has broken and is asking for parameter values every time I try to open the form or query. I haven't changed any of the names of the boxes and even used the expression builder to make sure everything was spelled correctly. I deleted everything and started from scratch and the same thing happened. It worked for a few minutes then went back to asking me for Parameter Values when opening the form or query. Its driving me nuts. Any idea what is going wrong?
So literally seconds after posting this I found that in the code for the form there was an empty Private Sub that I mistakenly made after writing button code that I must have exited from. I deleted that and now everything works fine! I'll leave this up in case someone else somehow manages to accidentally produce this error
I get a ton of questions every day, and I don't have time to answer them all here on RUclips. Feel free to submit your question on my website at: 599cd.com/AskYT
Hi! I’m working on converting an existing db to an online db. The problem I’m running in to is there is a multivalue field I use to search specific items. Is there any alternative way to doing this in an online database??
Multivalue fields are evil. 599cd.com/Evil. You'll have to separate it out into a related table, like it should be. Online: 599cd.com/AccessOnline
Is it possible to search multiple fields in one search box? say I want to search first name, last name, or state all from the same box.
Sure you just got a code it that way. 599cd.com/EasySearchForm
Excuse me, do your table's column width can be adjusted freely by user if the value of the field is too long to appear on the field? If you already covered this in the your other video, can you show the link?
For the limitation of my database and user knowledge, i need to give the user some sort of way to adjust the column width like what you do on excel's column.
The user cannot adjust them easily at runtime like in Excel. Sorry. You can edit the column widths property in the design.
One of my members actually asked a similar question recently, so I'll be making a TechHelp video on this soon. I'll be showing how to do it with VBA in the Extended Cut.
When I change my record source to the query I lose all my original data, which makes perfect sense to me, if I'm not pointing my form to the original table how can can the datafields be populated?
Queries just show table data. Shouldn't matter.
@RichardRost .. Im curious.Hope its not too intrusive. How did you learn all this where a lot just didnt? .. A lecturer at Uni sold me a computer .. when i opened i found a hidden application (not used), in either dos or files/folder.. about 2000ad..
How did I learn what I know? Mostly books and trial 'n error. I've been an IT pro since 1994. 599cd.com/AboutRichard
Oh.. You definitely good at applying yourself to your findings . I'll have a read .. I find remembering this subject matter difficult even with practice.. it gets forgot.. Thx.. 😊
Please help! I followed your steps and everything worked exactly as intended with all my practice data, but with any new records added after I created this search box, they don't show up. They'll appear in the list when I open the search form, but the moment I hit Requery, or Clear Search, they disappear, even if nothing was entered. The Form load and clear search have the same exact code and the Requery button just doesn't have the Setdefault. Any idea what could be the issue?
Can't tell from here. 599cd.com/Ask
Gear video. It was very helpful.
Thanks
Thanks! very high
You're welcome!
THANKS FOR THE VIDEO
HOW I CAN SEARCH FOR NUMERIC VALUES THAT I INSERT IN ONE SEARCH BOX IN MULTY COLUMNS
LIKE WHEN I TYBE "R" IN "FIRST NAME" BOX IT SEARCH IN THE BOTH COLMUNS "FIRST NAME" AND "LAST NAME"
For numbers, don't use quotes.
@@599CD Ok but How i can search for the value from just one box in multi columns?
Love your videos! I have a problem with the criteria in the Search Query. When I put in the criteria, it says "does not recognize '[Forms]![SearchF]![SearchBox]' as a valid field name of expression." I used the suggested fields so I'm not sure what I am doing wrong?
Something is probably spelled wrong, or you have it in the wrong spot. It's impossible for me to tell without seeing your database. That's why I encourage my students to upload screen shots to my Forums. 599cd.com/AF
Cant thank you enough.
Welcome
Do you know how to prevent parameter pop up box for each field search textbox when trying to add the search form to a navigation form?
Use a form field. 599cd.com/FormName
@@599CD I guess I wasn't quite clear when I originally asked the question. So I have multiple search forms which when opened individually work fine on their own however when I then try to put all of these forms onto a navigation form each search form upon selection from within the navigation form has an enter parameter value pop-up for each text box I use as a criteria for the search. Do you know how to prevent this?
Yes. Use a form field instead of a query parameter, which is what is explained in that video I pointed you to.
@@599CD I'm already using form fields and getting the enter parameters boxes that what's confusing
@@tylerpatsula8805 Are you trying to put the forms with working search boxes into another form as subforms? I'm running into the same problem and I think it's because of the way the query is written. In his "bang v dot" video, he mentions towards the end about how to reference a subform versus a main form. I'm having the same problem and I think that's why. Did you manage to fix your problem?
Super ❤❤❤❤
can this be used to search for customer phone numbers in the entire database?
Sure
@@599CD how can i do that?..im kinda new to access...
Hi sir, everything supporting to me. If you can please update menu costing formats like search, editing with summary
I get a ton of questions every day, and I don't have time to answer them all here on RUclips. Feel free to submit your question on my website at: 599cd.com/AskYT
Great video and love watching.
Have you do a multi search that updates as you type.
But with tick boxes of different categories.
For example: inventory
Categories
1. Electrical goods
2. Plumbing items
3. Stationary
Ok a short list of three but if ticked category 2 for example
You have auto update search for part numbers as wildcard that contains part on the number but only in categories2
Or you do only search to what ever is ticked. If none ticked then search in all .
If you know what I mean
I cover "search as you type" in my Search Seminar. Adding the check boxes would be simple too with the techniques I show there. 599cd.com/SearchSeminar
Sir
How to manage font size of ms access report fields through other form. So user has right to change font size of report without opening report in design view in ms access.
That's tricky, but I do have a video coming out on this subject soon. I'll be covering it in my Holiday Greeting Card Mailing List Seminar very soon. Watch my web site for it. 599cd.com
After entering the first name search criteria in the query, when the query is run, a popup window appears which says Inter Parameter Value.
599cd.com/epv
how to create multiple form for receipt/sale invoice 5 to 10 items
Hello Mr. Rost,
Your video was very helpful and the continuous form is exactly what I needed to show all of my inventory. I do have a question though, I designed my SearchList form (called frmSearchList) almost identical to the way you designed your Customer List. In my query, I placed 5 fields. (PartNum - AutoNumber), (PartDescription - Short Text), (CategoryDesc - Short Text), (SubCategoryDescription- Short Text), and (Available_Inventory - Number). There are times where some of my items do not contain a SubCategoryDescription and are meant to stay blank. In my query i placed the criteria as Like "*" & [Forms]![frmSearchList]![txtSubCategory] & "*" Or Is Null. My frmSearchList form is not returning blank records. Do you know where I may have gone wrong?
That should work. Post this on my web site in the Forums along with some screen shots of your design. We'll take a peek.
599cd.com/AF
I'm wanting to hide the tables can these multifield searches be used on forms?
Of course
Hello Mister Rost, it's very useful tips
How to display a dialog window which proposes to automatically add a missing element of the search form in the table?
To make data entry management more efficient.
I don't understand. You can just type missing data right in the form.
Thanks for the videos you provide. I have a question. Can you add a form footer total for the "IsAvtive" When the Tri state check box is clicked. EX. Null/Yes/No. It would be cool if a form footer total would sum the value when each is checked. Thanks
Update: I used this as the Control Source, Sum(IIf([IsActive],1,0)) ,In a Text Box in the form footer. Works Great
You got it. :)
This is not working for me at all. Whenever I try to run the query it shows a blank page. I’ve tried everything. I’m not getting errors so I wonder what might be wrong?
It's impossible for me to tell from here try posting this in the Forum and we'll take a look at it 599cd.com/AF
This method just is not working for me and I cannot figure out what I am doing. I went so far a making a similar simple database with no luck. I keep getting a pop-up asking for the perimeter. When I input the value in the search field, the perimeter pop-up comes up. Am I using an incorrect version of Access?
599cd.com/EPV - I'll bet it's a typo
@@599CD Turns out I was searching for numbers instead of letters. Fixed that and I was able to perform the search using a City field with the query and the desired records showed up in the query. The records do not show up in the form. All records still are listed. I checked all the criteria, spellings and everything matches. I am at a loss.
I am having no luck in getting the form to requery. I can requery the query and it gives the results I ask for, but it does not go into the form. Frustrating.
How do I make a report based on these searches?
That's going to be the video for next Tuesday (May 9) here: Filter Report: 599cd.com/FilterReport
This changes Like “*”& [EVERYTHING!!!]
Haha Thanks
how to search in two tables as i have 2 different customer list please help me
599cd.com/UnionQuery
CustomerListQ shows me all the records on customerF regardless if the searchbox is empty or filled.
Then something is wrong. Try again.
I guess it should still work if you put it one below the orhe other in criteria under Last name , State respectively instead of adding "OR NULL"
Give it a try.
Not sure if anyone has caught this but on the code builder for the clear search the line of code is FirstNameSearh = " " and so on. Had a person go in, type a letter and just click clear search and nothing would pupulate. They tried to put in a letter and nothing. I found that that is because " " is equivalent to a space. If you just do the code to be "", that solved it. Just wanted to say. Don't think it will happen often but just fyi
Interesting. Thanks for sharing.
@@599CD no problem! Your videos have been extremely helpful! Thank you so much for all the hard work!
I am training to learning English too! rsrsr
Awesome! Good luck!
I wish there was an easier way for me to remember the commands to that query search from a Subform.- I end up stabbing at it till I get it right.
It just takes practice. You'll remember it eventually. I've tried making one of those "quick reference sheets" for Access, but there's no way to make it useful... if you put everything on the sheet that you need the font gets REAL small. :)
This video was immensely helpful; until I got to the point where I type in the search box. I could not type anything in the box. I didn't even get a curser.
I thought I followed the instructions to the letter. Where did I go wrong?
Without seeing what you've got, it's hard to tell where you went wrong. If you can't even type in the text box, make sure it's not locked and you have Allow Edits set to yes.
Me too. I've rebuilt it twice and can't figure out why.
I figured it out. Really hard to find the properties button for the right part of the form. Just went through an O365 update and they keep changing things.
I have been trying this for a day or so. I have the LIKE "*" & Forms!ContinousForm!btm1& "*" or null. Same for a second btn. but it askes me for a parameter value(Forms.Shared with.Value) has anyone run into this issue before?
599cd.com/EPV - probably spelling
@@599CD Thanks for the reply, I have checked the spelling a few times and it is correct.
What are other things it might be?