Access 2007-2019
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
Home   Courses   Seminars   Templates   Help   TechHelp   Forums   Contact   Join   Order   Logon  
New Access Seminar Coming...
By Richard Rost   Richard Rost on Twitter Richard Rost on LinkedIn Email Richard Rost   12 years ago

OK, Access students... I mentioned a few days ago that I've got a new Access seminar coming. I've already got over FIVE HOURS of material recorded.

In a nutshell, this database is for tracking and recording service calls, but that's only the practical example. The concept is to show you how to have a database work realistically in an office with MULTIPLE USERS, and each has his/her own piece of the database they have to work with.

Joe enters a work order. Steve has to perform the work. Manager Sue has to approve it. Joe follows back up with the customer. Everyone needs their own screens and can't mess everyone else's data up.

I'll also show you how to SECURE your Access database (both the code and the data) as best as possible with JUST Access.

I expect to release this seminar in a day or two. It still needs a little more work. Here's what I've recorded so far. Let me know what you think, and if there's anything that you can think I need to add BEFORE it's finished...

1. Database Setup, Login Form (12:23)
Create a New Database
Trusted Locations
Set Overlapping Windows
Setup UserT Table
Access User Level Security
Why ULS is Not Very Secure
"Good Enough" Security
Create Login Form

2. Login Form Code (11:42)
Check for blank username/password
DLOOKUP Username and Password
NZ Function

3. Main Menu (9:43)
Exit Database Button
UserID, Username Fields on Main Menu

4. User Groups (14:35)
GroupT Table
Group to User Cross Reference Table
Junction Table
UserF Form
GroupF Subform
Adding Users to Groups

5. Checking Group Permissions (12:35)
Open User Form Button
Code to Check if User in Group
Is the User an Admin
Defining Your Own Function
Public Function
Return a Boolean
Global Modules

6. Service Process (9:06)
Define Service Processes
Create CustomerT Table
CustomerF Form
StatusT Track Work Order Status
ServiceT Detail on Service Call

7. Service Form (10:40)
Build Service Form
Create Combo Boxes

8. Service Listbox (11:23)
Listbox Service for Current Customer
OnCurrent Event
Modify SQL for Listbox to Format
Format Function in SQL

9. Sales Rep Security 1 (10:51)
Security: Who can Browse Customers
Assigning Customers to Sales Reps
Query to Show Only Sales Reps
Sales Rep Combo Box for Customers

10. Sales Rep Security 2 (10:43)
Setting Properties in VB Code
Change Properties Based on User Group
Change Properties Based on SalesRepID

11. Locking Fields on Form (8:56)
Locking All Fields on a Form
For Each Control Loop
RGB Color Function

12. Unlocking Needed Fields (7:08)
Unlock Fields for Sales Reps
Unlock Fields for Service Techs

13. Create Service Order 1 (10:08)
Create New Service Order Button
Enable/Disable the Button
Get Default Value from Another Form

14. Create Service Order 2 (14:01)
Requery Service Listbox OnGotFocus
Open a Specific Service Order
Set a Default Sales Rep
Security Opening Service Orders

15. Assign to Service Tech (10:27)
MsgBox vbYesNoCancel
Lock Fields Once Assigned to Tech

16. Unassigned Service List (12:47)
UnassignedServiceQ Query
service List Form

17. Accepting Service Call (7:33)
Accept Service Call Button
Perform Service

18. Unassigned or My Calls (7:58)
Show Unassigned Service Calls
Show My Service Calls Button
Show ALL Service Calls
Dynamic SQL in RowSource of ListBox

19. Service Completed (11:32)
Mark Service Completed
Show Status on Service List
Show New, Open Service Calls

20. Hide Buttons on Main Menu (14:30)
Show/Hide User Form Button
Show/Hide Browse Customers Button
Open Service List Show Different Things
Private v. Public Form Functions

21. Fixing Some Problems (8:01)
Fix Problem with Sales Reps
Fix Missing Button Problem
Force Service Details to be Entered

22. Manager Review (12:29)
Manager Review 1 Button
Show Service Calls to be Reviewed
Assign For Followup Button
Hiding All Buttons on ServiceF

23. Followup, Closing Call (11:38)
Sales Rep Followup Completed Button
Manager Closed Button

24. Sending Email Notices (13:00)
Sending Email using Microsoft Outlook

25. Admin User Combo (11:51)
Make the UserID a Combo Box
Quick Change to Another User

26. Locking the Database 1 (9:43)
Split the Database
Front End v. Back End Databases
Encrypt Database with Password
Open Exclusive
Linked Table Manager
Get External Data
Create Links to Backend Tables

27. Locking the Database 2 (13:28)
Backup Your Database
Turn off Navigation Pane
Turn off Database Window
Show Startup Form - LoginF
Application Title
Display Navigation Pane
Allow Full Menus
Allow Shortcut Menus
Hold SHIFT Key to Bypass Security
VBA Code to Shut Off Bypass Key
Database CreateProperty
Properties Collection

New Access Seminar Upload Images   Link 
Richard Rost 
12 years ago
Rupert, this is already in there. Remember those Prego spaghetti sauce commercials... "It's in there."

Each step of the way, the database tracks who did what and when. For example:

- Joe the sales rep entered the work order on 1/1/09 Read More...
Add a Reply
New Access seminar Upload Images   Link 
Richard Rost 
12 years ago
Chris, I believe you're talking about the Office Developer Tools which can package your Access database into a distributable format. Yes, I've used them. They work pretty good.

In Access 2007, those tools are a FREE download. Also, you're not going to like this, but Access 2007 actually has gotten rid of the User-Level Security Wizard, BUT it has much better encryption built in. So I think that if security is your concern, 2007 might be the way to go. Read More...
Add a Reply
New Access Seminar Upload Images   Link 
12 years ago
Hi Richard,
If it will not cause too much work, could you also include a method for tracking changes?
This would be a table that shows what fields were changed, original value, new value, who made the change and the date the change was made. This would be really cool. Read More...
Add a Reply
New Access seminar Upload Images   Link 
Chris Bezant 
12 years ago
WOW that sounds great.
I have been thinking about protecting a distributed database and I am playing with a security key that has to be entered that will only work on a specific machine. I am going to couple that with using the method of distributing that doesn't need Access installed. Can't remember what it's called exactly but it's an add-on for 2003 that creates an install package. Read More...
Add a Reply

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.

You may want to read these articles from the 599CD News:

9/22/2021Access DevNet Testimonials
9/20/2021Letter Writer
9/19/2021Multi Field Combo
9/19/2021Project Management
9/17/2021Overlapping Windows
9/10/2021Benefits of Access
9/10/2021Access on a Phone
9/5/2021Prevent Deletion
9/4/2021Booklet Template

Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
Account Login
Online Theater
Lost Password
Student Databases
Change Email
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Customer Support
Web Site Tour
Consulting Services
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Video Tutorials
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Terms of Sale
Contact Info
Support Policy
Email Richard
Mailing Address
Phone Number
Fax Number
Course Survey
Blog RSS Feed    Twitter

YouTube Channel    LinkedIn
Keywords: new access seminar security uls  PermaLink