21. (Advanced Programming In Access 2013) Improving Tab & Subform Loading Speed

[@bumpersmith]

Again good subject that explains the tab object but also the select case. Thanks again for the exceptional explanations of the subject.

[@santiagoangos4045]

Your videos saved me man, literraly. Thank you!!! Big hug from Argentina

[@jeffreyjansen]

Hello Steve, First I want you to know that I love your videos. They help me a lot in trying to understand Access. While watching this video I am trying to solve a complex problem. I have a table called tblGiven_answers. This table is an export of a mySQL-database created by somebody else and cannot be altered. This table contains ALL answers of ALL subscribers in ALL categories. But worse of all: There are also answers of single and multiple choice questions in that same table! The single choice questions are for example “Gender” where they can pick “M” or “F” from a combobox. A multiple choice question like “check when you are not available” can be answered by checking checkboxes of “friday-afternoon”, “friday-night”, “saturday-morning”, “saturday-afternoon” etc etc. on a form on a website. If a subscriber checks multiple checkboxes, only the checked aswers will be stored in tblGiven_answers. The value that will be stored as the answer of both the single and multiple questions is an integer. That integer comes from a table “tblAnswers” which contains all possible answers of the multiple choice questions. For example: In the table tblAnswers there’s a record which has the ID of 351, a “question_id” of 558, and in the “answer`’ field`: friday night. If checked in the webform this answer would save in the table tblGiven_answers as “351” in the “answer-field”. Of course the records in tblGiven_answers also contains fields like “user_id” en “question_id” . Because a subscriber can choose more than one answers it’s is possible that there are more records with the question_id = 558. After this long introduction I want to explain the problems that I encounter. I use TABS to display and edit the subscriber’s information. The first two tabs I use for contact info and for the open questions. No problems so far because I use your method described in your video. For the single choice and multiple choice questions I can not use regular subforms in Access. I can not make a continuous form because the answers were picked with a combobox or checkboxes. And I want to display the answers in the same way as the fill-in form on the website, with comboboxes (with default value as answered) and checkboxes (checked if answer is present in table). I managed to make such a form and it worked, but… very slow. And that’s because I didn’t find a way NOT to use a form (with his own source code) for each question/answer. I’m sure that there’s a smarter way because in fact only one table namely tblGiven_answers is involved in all the questions and answers. Maybe I can drill it down to one fundamental question: Is there a way to use multiple parameter variables (is that the proper name?) in an SQL statement in VBA? I also have difficulty in writing the proper syntax of SQL statements in VBA especially when it involves variable names. You showed me in lesson 17 how to deal with values of for example a combobox as part of an SQL statement. My idea is to use the SQL statement once with all the variables for user_id, question_id, and answer_id and write some code for each combobox (related to a certain question and having a unique answer_id). But how would the syntax of the SQL statement look like? For the multiple choice questions I managed to make one SQL statement for all checkboxes (with a “set” control in it). I use rs.FindFirst “answer=’” & me.txtQ109A1 & “’” and if rs.NoMatch then me.chk109A1= False Else me.chk109A1= True I realize I have used way to much words. Sorry for that.

[@swapnilwankhede3440]

Hi Steve Excellent Tutorials, Thanks a lot Steve for uploading this videos, it’s really helpful, never found such good videos.

[@SpectralInSpectre]

Is there an equivalent of this for combo boxes? I am getting the 'Cannot open any more databases' error and I think stopping all of my form's drop-down lists loading automatically might solve it.

[@greghalliday4226]

Thanks Steve, another great video. Could you cover the code for exporting to Excel templates and importing again. For example, situations where you set up a standard report and want to load the data into the template each time, then maybe pull the data back into Access again after it has been updated. I have worked out a way to do it, but you often show better ways that I haven't thought of.

[@hassankadhum4127]

thank you steve i love your video they are so marvelous they taught me alot

[@ProgrammingMadeEZ]

I am so glad to hear you like them!

[@Ziahasan222]

Thanks alot... It solves my problem.. Tanks a lot from my bottom of Heart..

[@abdullahquhtani4247]

I hope the time is not too late to post comment but I just saw the video and I wanna suggest a solution here. Why don’t you convert SQL command to a stored procedure and use command to run that stored procedure and get benefit of using variables of stored procedure and also speed Data loading? I really got tips from your videos that never knew before. Keep on such great tutorials. Thank you 🙏 so much.

[@aliahmed16314]

enjoyable video and awesome idea, thank you very much. Steve.

[@ProgrammingMadeEZ]

Ahmed Kassim Thank you Ahmed!

[@aqantassoftware3792]

Excellent Video.

[@sherazkhan4354]

a good video, i have one question how we can show the number record in tab caption for e.g; you have First Tab named as "ActiveOrder" in which you have sub form contain 9 records, how we can show Tab caption like this "ActiveOrder-[10]", it is possible, please help me Steve.

[@hassankadhum4127]

thank u steve your videos are so informative

[@ryanw1906]

I find it easier to set the subforms unbound and then set the source object to the subform name when selecting a tab.

[@havok_seeker6458]

Steve Thank you so much for your Videos. I have absolutely no background in access, coding or anything of the sort. but i am tasked with fixing an interface on Access. Currently the program is almost in-usable because it is so slow on a shared network but works fine on a stand alone. I am trying to follow this video with the coding but when i input my information my tabs are named differently for example where you put "select case me.tabOrders mine is Select case me.detail. you then put value after this. value is not an option for me where could i find this source for this? any help would be amazing thank you.

[@smbossco]

You the man, Steve!

[@ProgrammingMadeEZ]

Thank you!

[@rathpanhasarun310]

Hello again Steve! In my project I used listbox to retrieve data instead of subform. I found out it's a bit slow when the listbox retrieve data (in the client computer). Is it because of my network's speed or what?

[@ProgrammingMadeEZ]

Possibly. Also make sure that you are filtering the data as much as possible to just those columns you need and where clauses with as few "like" clauses as possible.

[@rathpanhasarun310]

+Programming Okay thank you.

[@abeibrahim5846]

Hi Steve, I have a comboBox in the header of my subForm, and when I embedded it in my NavForm, I get a dialog asking me to enter a value. The subForm by itself, the comboBox is bound and working fine.

[@ProgrammingMadeEZ]

There are a lot of reasons this could be happening, without actually seeing the form and debugging it, I have no way of telling you what's wrong.

[@hfarmanesh1]

Thanks Steve. You are wonderfull

[@MrShami2001]

Steve great job. Please if possible make tutorial on user registration, change or edit passwords on Login forms. Thanks and appreciated

[@CEDRICKKAU]

very helpful video, thank you very much

[@AbdulrahmanWaseem]

Thanks Steve

[@hassankadhum4127]

This is a great video thanks

[@A2theLEX2]

Hey Steve ! I really love your videos and great explanations. However I am having a problem to get this working with multiple subforms in one tab. or even "sub-sub forms". I tried it by defining SQL strings 1-5. Do you have any suggestions? Greetings from Germany!

[@ProgrammingMadeEZ]

If you have a subform inside of a subform and you're trying to set the SQL form the highest level form, you'll have to drill down to that subform and set it's sql string. Use this guide to help you. access.mvps.org/access/forms/frm0031.htm

[@A2theLEX2]

Programming Thank you! And how do I manage several subforms in one tab at the same level?

[@ProgrammingMadeEZ]

The way I show in the video.

[@nabilmek4008]

I can't thank you enough mr steve for your effort and your way of teaching things :) ... i was wondering if you could help me dealing with referencing controls in subforms in navigation form or controlsin tabs in a macro builder or vba code ... thanks a million

[@ProgrammingMadeEZ]

Best guide around: access.mvps.org/access/forms/frm0031.htm

[@nabilmek4008]

I am sorry but ... i have tried these on that page but sometimes it didn't work ... any suggestions mr steve ... thank you very much :)

[@ProgrammingMadeEZ]

Not really.. Those should work. Just make sure you're looking at it carefully.

[@johnclark851]

Great video Steve! Quick question, could you have populated the SQL variable and Recordsets after the Select Case block?

[@DasButterBoat]

Great video set sir! Just an observation... I would think, you have actually increased the data loading on the form, in this simple case (2013 may be different?). Remember, EVERY time you click on those tabs, the underlying recordset is repopulated AND requeried (I actually do not think you need the extra step of the requery, as this should be automagically done when updating the recordsource.) Using a simple "first-run" boolean flag in the code, when the form is first opened, will keep this re-update from occurring. Keep up the good work!

[@ProgrammingMadeEZ]

+Nayland Smith Yes, you could do that to increase the speed for tab loading. It would however also mean you don't get the latest information. If a change is made to the underlying data, you would not see it.

[@DasButterBoat]

+Programming Thanks for the reply. Yep, thought of it after I posted ;). just another case of, "Fix one, break two". I typically use buttons for manual refresh/requery only because I code smaller/personal applications where I like the manual control. Larger more user intensive setups would surely benefit from a blending of the two with a little creative coding.

[@ProgrammingMadeEZ]

Good idea for the 2 buttons. Offering a refresh option is a good idea for your users so they know they're getting the latest data.

[@AliTwaij]

excellent