Computer Learning Zone CLZ Access Excel Word Windows

The less evidence we have for what we believe is certain, the more violently we defend beliefs against those who don't agree.

-Neil deGrasse Tyson
 
Home   Courses   Seminars   Templates   TechHelp   Fast Tips   Forums   Help   Contact   Join   Order   Logon  
 
Home > Courses > Access > Templates > ABCD > Part 4 < Part 3 | Part 5 >
 
ABCD Core Part 4

Access Business & Contact Database Part 4

Welcome to ABCD Part 4: Core. Total running time so far is 4 hours, 6 minutes.


 S  M  L  XL  FS  |  Slo  Reg  Fast  2x  |  Bookmark Buy Now

Lessons

Contents

  • Lesson 1 begins by building the sorting and filtering features on the EntityListF. We'll add filter boxes above the field columns, sort labels, and a clear filters button.
  • Lesson 2 starts the advanced filtering on the entity fields that aren't in the normal search listbox like middle name. Lesson 3 adds searching the helper data (prefix, gender, etc.).
  • Lesson 4 adds searching the related tables like email, address, etc.
  • Lesson 5 adds searching the Misc Data fields.
  • Lesson 6 we will remove duplicate entities from the list with a GROUP BY statement, and make it so the last column will show values from a Misc Data search.
  • Lesson 7 we handle some bug fixes, improvements in the code, add ALL and NONE options to the search combo boxes, and create the printable report.

Database Files

I'm only posting the 32-bit versions for now. That's what I use. If you are using 64-bit Access, just create a new blank database file and import all of my objects into yours. I'll post a 64-bit version when the Core is finished. If you experience any code that doesn't work under 64-bit let me know. Click here for more information about 32 v 64 bit.

Resources

Learn More

I need to start making a proper outline page for the ABCD parts like the Developer lessons. I'm posting this here for the time being:

2. Advanced Filter 1 (32:52)
Search Field Listbox
Value Text Box
Search Operator Combo
Search Field Table

3. Advanced Filter 2 (31:25)
Handling Date Searches
Searching Helper Data
Helper Search Combo

4. Advanced Filter 3 (32:38)
Email Address
Join Table in SQL Search
Phone Number Digits
FilterCharacters Function
Address Fields

5. Advanced Filter 4 (39:31)
Search Misc Data
List of Misc Types
Available Search Values

6. Advanced Filter 5 (26:25)
Don't Show Duplicates
Add a GROUP BY
Fix Entity List Column

7. Advanced Filter (24:58)
Bug Fixes
Add ALL and None
Printable Entity Report

Enroll Today!

Enroll now so that you can watch these lessons, learn with us, post questions, and more.

Pre-Requisites Required

In order to purchase any level of the ABCD, you MUST have purchased all previous levels. For example, you cannot buy only Level 5. You must first have Levels 1 through 4, then you can buy Level 5. Sorry, but this is a hard pre-requisite.

Videos

I am recording videos showing how the database is built, but mostly so that you can understand the construction and be better equipped to make any modifications or additions you may need on your own. This will be marketed as a finished database without the need for any enhancements, but, of course, one of the main reasons to use Access is so that you can make changes yourself. The videos will be a quick run-through of how each feature is built, as I'm building it. I will be using advanced features like SQL and VBA from the start, so you may want some SQL or VBA Developer Lessons under your belt if you're going to try to follow along. This is NOT a teaching database. Free Technical Support will NOT be offered.

Tech Support

There is NO free Technical Support provided on the ABCD if you have not taken ALL of my previous Developer lessons. There are a lot of very difficult, advanced programming concepts used in the construction of this database. I do not have the time to provide free support if you get stuck trying to make modifications. All I will do is point you to the appropriate Developer lesson(s) that cover the issue. There is a reason why (a) I say this is NOT a teaching database, and (b) there are pre-requisites. Feel free to ask questions, but I can't spend half an hour explaining concepts that are already covered in my other lessons.

Licensing

Unlike my other courses and seminars which provide you with royalty-free databases that you can modify and distribute yourself, the ABCD does NOT come with a royalty-free license. You may only customize the database for your business needs and for use within your organization. You may not resell or distribute it to others without express written permission. In addition, if you are using it in an organization with more than 10 users, you must purchase additional licenses. Contact me for additional information. I will post additional details when the Core database is completed.

Ordering

There are two purchase options available for the ABCD.

  • You can purchase a fully editable and customizable copy of the database in addition to the video tutorials that show how it was built. This is the option you should pick if you want to make changes to the database for your own business needs, and you want to learn how the database was constructed to assist you. As a reminder, these are not teaching videos. They are simply provided to show you how the database was built. Free support is not guaranteed if you have questions. I do my best to answer questions, but I can't promise anything. You must purchase any pre-requisite parts or modules required. For example, you may not purchase Core Part 4 without first having parts 1 thru 3.
  • Once the Core is finished, you can optionally purchase a use-only version of the database. This is the best option if you only plan on using the database for your personal or business needs, but do not intend on modifying the structure of the database. Free instructional videos will be provided to teach your staff how to use it.

Your Feedback

I will be releasing new Core features and modules on a regular basis. You can see the list of features that are scheduled for development on the Coming Soon page. I would very much like your feedback on what features you think should be available. If you have a specific interest for your business type, let me know. 

 

ABCD4 Detail Question Upload Images   Link  
Alexis Cao 
2 months ago
Hello, i have a big question about the ABCD 4 Parts

how should i do if i need to understant the below VBA Code which comes from the ABCD 4 filter part. it's too difficult for me to understand even if i copy and see these code again and again:


Private Sub RequeryForm()

    Dim WhereStr As String
    Dim SQLStr
    
    EntityCodeFilter.BackColor = RGB(200, 200, 200) ' lt gray
    FirstNameFilter.BackColor = RGB(200, 200, 200) ' lt gray
    LastNameFilter.BackColor = RGB(200, 200, 200) ' lt gray
    OrganizationNameFilter.BackColor = RGB(200, 200, 200) ' lt gray
    
    WhereStr = ""

    If Not IsNull(EntityCodeFilter) Then
        EntityCodeFilter.BackColor = RGB(200, 255, 200) ' green
        If WhereStr <> "" Then
        WhereStr = WhereStr & " AND "
        WhereStr = WhereStr & " EntityCode LIKE ""*" & EntityCodeFilter & "*"" "
    End If

    If Not IsNull(FirstNameFilter) Then
        FirstNameFilter.BackColor = RGB(200, 255, 200) ' green
        If WhereStr <> "" Then WhereStr = WhereStr & " AND "
        WhereStr = WhereStr & " FirstName LIKE ""*" & FirstNameFilter & "*"" "
    End If

    If Not IsNull(LastNameFilter) Then
        LastNameFilter.BackColor = RGB(200, 255, 200) ' green
        If WhereStr <> "" Then WhereStr = WhereStr & " AND "
        WhereStr = WhereStr & " LastName LIKE ""*" & LastNameFilter & "*"" "
    End If
    If Not IsNull(OrganizationNameFilter) Then
        OrganizationNameFilter.BackColor = RGB(200, 255, 200) ' green
        If WhereStr <> "" Then WhereStr = WhereStr & " AND "
        WhereStr = WhereStr & " OrganizationName LIKE ""*" & OrganizationNameFilter & "*"" "
    End If
    
    SQLStr = "SELECT EntityID, EntityCode, FirstName, LastName, OrganizationName FROM EntityT"
    
    If WhereStr <> "" Then
        FilterClearBtn.Visible = True
        WhereStr = " WHERE " & WhereStr
        SQLStr = SQLStr & WhereStr
    End If
    
    If OrderByStr <> "" Then
        SQLStr = SQLStr & " ORDER BY " & OrderByStr
    End If

    EntityList.RowSource = SQLStr


or What lessons should i learn if i get to understand
Alexis Cao
2 months ago
very confused about:

If Not IsNull(FirstNameFilter) Then
        If WhereStr <> "" Then WhereStr = WhereStr & " AND "
        WhereStr = WhereStr & " FirstName LIKE ""*" & FirstNameFilter & "*"" "
    End If

this sub has 4 wherestr, it hit my core brain directly, i lose myself: where am i , what happend. i know its effect is to filter with some conditions, but i really can't understand why these code can do these. who can help me to explain? thank you very much
Kevin Robertson
2 months ago
This sub is saying:

   - check to see if there is anything in the FirstNameFilter
   - if it is not empty, check to see if there is anything in WhereStr, if there is add " And " to the end of it
   - then you are setting WhereStr to whatever is already in the WhereStr AND setting FirstName to whatever the value of FirstNameFilter is
   - if WhereStr is empty then FirstName will only be set equal to the value of FirstNameFilter without the AND.
Scott Axton
2 months ago
Alexis
I understand completely the brain overload!  Believe me we've all been there at one point in time or another.  
You probably have a better understanding, since your mother language is Chinese, and yet you have learned English to communicate.

You are now learning another couple of languages.  VBA and SQL at the same time.

Here are some courses for you that I consider a MUST if you are going to continue to develop databases:

SQL  Part 1 and Part 2 most important.  (Part 3 -  not so much - as that deals with manipulating tables and such.)

The Search Seminar shows the "how to build the SQL" demonstrated in the code above. (Not this specific example but similar using the WhereStr)

All of the Developer Courses. Those teach much of the concepts shown in ABCD.
Richard has stated, multiple times in the ABCD, this is demonstration of what he did, it isn't a Teaching Course.
Scott Axton
2 months ago
Finally
Please don't post large amounts of the code from the courses.  Richard works hard on these classes and it takes away from him.  It is also not fair to the people that paid for the classes to have the information posted where the public can see it.
Alexis Cao
2 months ago
Dear Kevin, thank  you for your explain. i watched your answers again and again. even if i don't understand completely, but it's more easier for me to understand than Dear Richard orginal codes. i have copied your explain to my note, i checked it when i am free till to understand it completly

And Dear Scott, thank you, thank you for you still remember me who come from China. firstly, i apolpgize for to post the lesson code to the forum, i understand after you said, trust me, i will never do the similar things again. Secondly, thank you for your suggesting of lessons, that is really i want, but in fact, the lessons i need to learn is so more after you suggest. haha. but i agree with that it is the easier way to get understanding. i like Dear Richard access lesson very much, in fact, i plan to learn all if i mean if i have enough money, haha. and thank you for your understanding to tell me you have been overload before, it really gives me the consolation and convidence.
Alexis Cao
2 months ago
at last, thank you, thank you for everyone in our website forum who helped me. i found this forum is very high efficiency and professional, even if i only post few questions, but i felt it. in fact i love it. it gives me the way to slove the confusion about access. i never think i can solve my access problem such quick and porfessional, i feel happy and give more convidence to learn more access. in fact i use the access every day now, you can think how it is important for me. thank you, thank you everyone. God bless all of you
Scott Axton
2 months ago
Alex -
I understand the money and budgeting all to well.  Much of what is in those seminars is also covered in the TechHelp and also the regular courses.  There are lots of topics to keep you busy for a very long time.

Richard sent you an email when you became a Learning Connection Student.  In it was a special "discount link" for any of the courses you wish to take advantage of.  The LC discount is 50% the last time I checked so be sure, if you get those seminars, to use that link to take advantage of the reduced price.
Alexis Cao
2 months ago
Dear Scott,

yes, i know the discount price. and now i am a golden member, and i also purchased the developer 28 and 29 lesson, also the ABCD in the recent with 50% discount. thank you for your kindly reminder.

Best wihses
Alex
Alexis Cao
2 months ago
Dear Scott, how long time can be a good access user since 0, do you think. till now, i have known the access for about 5 or 6 months, but i found i need to learn more and more if i use the access smoothly. what time do you learn the access
Scott Axton
2 months ago
Wow that is a question that doesn't really have a finite answer.  Each person learns at a different pace.
For some it comes naturally for others it's more difficult.  
For me I'm still learning every day.  I have been using Access almost since the start.  I started getting lessons almost 6 years ago but really only became serious maybe 2 years ago with Access. I did other computer languages before that.

It sounds strange but I learned the most by my failures.  LOL I learn what not to first, before I have those moments of clarity that get saved into my tool box of knowledge.
Alexis Cao
2 months ago
yesterday i collected one saying from our website: We learn more by looking for the answers to a question and not finding it than we do from learning the answer itselft. -----Lloyd Alexander

haha, this saying meaning is like what you told me. but you learnt more than 6 years. wow!!! 666, i can't imagine what i become if i have 6 year experiences of access, you must be very professional

i keep learning everyday since 5-6 months ago. i discover the new world after knowing the access powerful founction, in fact it's Richard, let me interest in the access, in China, these is less professional teaching lesson like richard teaching video, maybe i didn't find before, but i like the teaching style of Richard.

So everyday i go into this website to learn and discover the thing i like. at last, i wish all the best for you, Dear Scott, hope to make the friendship of access learner though this website
Add a Reply

Show All Comments

Start a NEW Conversation
 
Only students may post right now. Click here for more information on how you can set up an account. If you are a student, please LOG ON first.
 
Subscribe
 
 

Learn
 
Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
Seminars
More...
Customers
 
Login
My Account
My Courses
Lost Password
Memberships
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Help
 
Customer Support
Web Site Tour
FAQs
TechHelp
Consulting Services
About
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
Memberships
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Contact
 
Contact Info
Support Policy
Email Richard
Mailing Address
Phone Number
Fax Number
Course Survey
Blog RSS Feed    Twitter

YouTube Channel    LinkedIn
Keywords: access ABCD 04 part 04 core  PermaLink