Larry R Harrison Jr, Access Knowledge Base

Friday, May 09, 2003 18:21:23

Tables Queries Forms Reports Basic Coding Filters Security

NEW!!!!!Beginner's Access Page

Template For my Databases:Contains Commonly Used Toolbars & Modules(2000 format)
E-mail Me

Some of the links below are based on replies to postings (most of them initiated by me) I found at Google.Com (formerly Deja), though some of the answers I found from OLD postings there. Some of the subjects I got the answers to by experimentiation.

"Quick Clicks"

A tutorial on how to create a customized search form for your database (updated May 2010)
A quick overview of how to make an Access database (needs rework, but oh well for now)
Create a Popup Calendar on a Form
Handling Combo Box "Not in List" Situations
Quick Code Samples, and Proper Syntax For Certain Tasks
Date Functions

Tables

Check to See if Table Exists
Temporary Tables:How to Create "Local" Temp Tables Which Then Updates the Network
Dealing with Changing Path to Linked Tables: SubSt method, AutoRefresh Method
Linked Tables: Giving Users Permissions to Change Path to Linked Tables
Microsoft Can't Change The Data Type, Not Enough Free Memory or Disk Space (Microsoft Knowledge Base Article Q209940)
DLookup: Looking Up Data From Other Tables And Inserting That Value Into Your Form's Field

Queries

Combine Multiple Child Records into one (e.g.--"Larry with children Fenton-Helen-Adrian" showing as 1 line item of the contents of parents table)
Does Query Exist?
Complicated Totals Query (Sales Rep % of Sales Example)
Improve Performance
Update Queries Within Visual Basic
Joining Multiple Queries. Ex---you want to show records from different, unrelated tables which were modified on a certain date
Hiding Duplicate Values
Total Queries, Showing Values that Have No Count
microsoft access help, query won't allow you to add records
multiple relationships between 2 tables in 1 query
How to Filter a Form/Search Records By More Than 1 Criteria Using SQL, and Reduce "Query Clutter"
The Above applies criteria as a RECORDSOURCE, while this one applies it as a FILTER
Total Query Examples
If...Then Used in a Query ("Physical-Billing Address" Example)

Visual Basic Coding

Special: Scramble Data, Code for "Scrambling" Sensitive Data for "Demo" Usage
Does Table Exist?
How to Quickly Close All Forms at Once
Reddick Naming Conventions (PDF Format), Highly Respected Guidelines for Naming VBA Objects
InStr, a great function for string manipulation
MsgBox Format
Lock or Unlock A Form from Allowing Edits; Add Record On Current Form, On Newly Opened Form
RECORDSOUCE of a Combo Box, Subform
Pluck Values From a Subform;Specify Filters on Forms/Subforms
Executing Update Queries Within Visual Basic
Input Mask Within Visual Basic

Modules

Some of these are repeated; since they are known as modules, I repeated them here in a modules section
Disenable Access Close Button
Is Form Loaded?
Last Day of the Month
Which Group Does the Current User Belong To?
Prevent Shift-Key ByPass of Startup Options
Make Access Skip Labels, Or Print More than 1 Copy of a Label

Reports

Is Null/Nz scenarios, (skip # in #3121 if no value in that field)
(New: 6-16-2010) Highlight Alternate Rows
Print Totals on a Page
Detect Which Report is Open
Setup a Report to Count How Many Records are Printed
Reports, Eliminating White Space in a Report
Page Breaks on a Report
How to Skip Used Mailing Labels and Print Duplicates (From Microsoft Knowledge Base Article Q95806)
microsoft access, report based on filter of a form
How to Make a Report Show the Same Records as Your Current Form is Showing

Forms

Make Continuous Behave as Spreadsheet (Arrow Keys Behave Same as in Spreadsheet)
Is Form Loaded as Subform?
Disable Form Closing (Prevent Users from Using CTRL-F4)
Fill New Record w/Data from Previous Record
How to Quickly Close All Forms at Once
Trapping FORM Errors, "Input Mask" Example
Creating Totals in a Form and how to reference a subform's total box within the parent form
Force Access to NOT Select the Pre-exisiting Text When Entering a Text Box
Enabling Controls, and Using a Loop to Do this Quickly. Also: using tags to do this to a specified group of controls
Creating Customized Toolbars and Drop-Down Menus
From the MVPs.Org Site: How to Highlight Rows on a Continuous Form
Easier Way to Evaluate Multiple Conditions ("Multiple Users" Example)
Sensing if a Form is Open
Convert Upper Case to Lower Case
Print Current Record on a Form
Delete Record on a Subform
How to Check for Duplicate Records after user enters the information in the field
Subform Issue, Having 2nd-Level Subform on Parent Form
Increase/Decrease Values By Pressing + or - Key
NumLock Problem. Derived from Microsoft Knowlege Base Articles Q179987 & Q177674
How to make forms resize for different monitors on a network
How to Create a Customized Search Form
Event Procedures on Subform Won't Work From Within Main Form I also explain the fix in detail
Applying Subform Filter from the Main Form. Included is an explanation of how I fixed it
Microsoft Access, Report based on Filter of a Form (Duplicate Entry)
Inserting User Name in a Secured Database to log who's entering what in your database
Limiting Entries on a Subform
How to Make a Report Show the Same Records as Your Current Form is Showing (Duplicate Entry)
Combo Box Articles Q88148 Q161007 (handling "Not in List" issues)
Combo Box: My Suggestion on How to Error Trap When Someone Tries to Select A Value Not in List

Security

General Overview of Security
Password Administration:

Adding/Deleting Users On a Form Using Code. I believe this solution uses DAO & only works with Access 2000 & Up; not sure
Changing Password In Code With a Form Using DAO (Includes link to Another Solution)
Even Better: A Custom Form Which Handles Adding/Deleting Users AND Passwords on One Form (Includes a Downloadable File)

Record(s) Can't Be Read, No Read Permissions on MSysModules2: How to Fix it
Create your Own Customized Login Screen
How to Detect the GROUP The Current Logged-in User Belongs To
Preventing Users from Copying Backend of Database: Microsoft Knowledge Base Q123483
Checking Who's Logged Into a Database: Microsoft Knowledge Base Q198755
Prevent Shift-Key ByPass of Startup Options
Disenable Access Close Button
Unable to Make MDE File 254372 - ACC2000: Overview of How to Secure a Microsoft Access Database
235961 - ACC2000: Access Security Manager Add-In Available
305541 - ACC2000: The Role of the Workgroup Information File in Security
208778 - ACC2000: Introduction to .ldb Files
207793 - ACC2000: Access Security FAQ Available in Download Center
210329 - ACC2000: How to Prevent Users from Creating New Databases

Specific Topics

Find Current Directory Where Database is Located
Timer Events, "Browse Data" Example
Obtain Windows Login Name
Show the Day of the Week for a Certain Date, or the Date Range for that Week
Disenable the Close Button in Access' Titlebar
Changing the Caption in the Titlebar of the Database
Access 2000 Performance Issues (derived from newsgroup threads doing a "Google" Search)
How to Disable Shift Key Bypass (Prevent Users from Bypassing Startup Options by Holding down the SHIFT Key)
External Link: Handling Images as Data Records in a Database
Microsoft Link Regarding Data Access Pages (my own information here)
Access 97:Active X Can't Create Object;(Microsoft Knowledge Base Q177264)
Select Case, A Wonderful Loop Which Simplifies "If...Then" Evaluations
Creating a "QBat" File for Running A Network Database Locally
Web-page Based Databases
Access XP--Can't Make MDE File (Q278376)
Running Access 97 & XP on Same Machine
Linking/Programming Outlook Within Access
Error Trapping Coding
Access 2000: There Isn't Enough Free Memory When Working on a Form (KB: Q236977 )
"There is No License On This Machine" Error: Microsoft Knowledge Base Q141373 and Q191224
Making Database Shutdown Automatically if it's Inactive
resetting autonumbers
Exporting to Excel
for true/false filters on click button
One to Many, Showing All the ONES if no MANYs
Filtering by a Yes/No Field, PopUp Form
Converting macros to modules, then refering them
2nd instance of DB opens during Word MailMerge
Popup Calendars on a Form
Relationships, Enforcing referencial integrity
Charts In Access
More Help With Charts In Access
The prior 2 links have charting information, but this one was FAR more useful
Macro Works on One Computer, But Not on Another
Date() Function Works On My Computer, But Not on Another
Regarding the 2 Related Above Issues, here is how I fixed it
Unable to Install Importing Features
How to Automatically Generate The Next Number In a Series without using "AutoId"
Number Sequencing Problems With Multi-user Situations

Non-Access Topics

Web Page Design: Color/Code Chart, Match up Colors with Applicable #
Excel: Sort by Row Colors
How to View Your Hotmail Messages with Outlook or Outlook Express: Microsoft Knowledge Base Q220852
From PCWorld.Com:  Ultimate PC Troubleshooting Guide -- Free up RAM, tweak your hardware, and remove remnants of programs when you uninstall
From PCWorld.Com:  How to Make CDs from MP3s on your computer
From PCWorld.Com:  Useful Napster Alternatives
Excel 97: How to Create Gantt "Time-Line" Charts
Excel 97: Copy & Paste Problem--Paste Formulas Between Sheets WITHOUT linking to original
Excel 97: How to Remove Links from Sheets (NOT hyperlinks, links to "original" sheets when you copy formulas from them)

Other ways to get help on Access (or any other computer topic, really):

Search Newsgroups, using NewsOne.Net, Google (formerly Deja), or Etin.Com. Also, try using a newsgroup program if you have one (Outlook Express can handle newsgroups). A list of Microsoft Access newsgroups are shown later on.

Search Microsoft's "Knowledge Base." The link is http://search.support.microsoft.com/kb/c.asp?fr=0&SD=GN&LN=EN-US.

Search Under the "Other Helpful Links" Heading (bottom of page--also, on top-left), they have MUCH more information than I do

Newsgroups

comp.databases.ms-access

microsoft.public.access.queries

microsoft.public.access.forms

microsoft.public.access.tablesdbdesign