| |
| |
|
Courses - Microsoft
Access 325 |
| Description: |
Advanced Access
Recordsets |
| Running Time: |
87 minutes |
| Pre-Requisites: |
Access 324 very strongly recommended |
| Versions: |
|
We use Access 2003 in this course,
but most of the lessons are valid for all versions of Access from 95 to 2003.
There are cosmetic changes in Access 2007. Order before 2/11/2010 to
get a FREE upgrade to our 2007 version when released!
|
|
|
|
|
| |
|
Full course details with screenshots coming soon.
Access 325 - Course Outline
1. Compare Two Tables, Part 1
Synchronizing Two Tables
Discuss Replication
Detecting Changed Records
Exit For
2. Compare Two Tables, Part 2
ListBox to See Different Records
Customer SubForms
Change SubForm Data in VBA
Subform.SourceObject
Subform.Form.RecordSource
3. Compare Two Tables, Part 3
Importing Changes
Detecting New Records
Find Unmatched Query Wizard
What Does Unmatched Wizard Do?
Change RowSource of ListBox
Importing New Records
4. Student Attendence, Part 1
Tables to Track Students & Days Absent
Multi-Select Listbox to Pick Students
Recordset to Add Absent Days
Table to Track Days Off (Holidays, etc.)
Aggregate Query to Count Absent Days
Global Module
Public Function DaysInSchoolYear
5. Student Attendence, Part 2
DLOOKUP Start / End of School Year
Loop Through All Days
Count School Days
Exclude Weekends, Days Off
View > Immediate Window
Checking Variables
Testing Function Return Values
6. Student Attendence, Part 3
Count of Absent Days Query
Show Days In Class (Subtract From Total)
Show Percentage
Placing Start / End School Year in Table
Showing Days Attended up to Any Date
Form Cycle: Current Record Only
Saving Data To Table (Refresh) AfterUpdate
Button to Display Student Query
|
|
 |
Huge Discounts Available
When you purchase multiple classes together
Huge savings up to 50% off! Order Now. |
|
| |
Student Interaction:
Microsoft Access 325
|
Richard on 1/1/2008:
Compare two tables, replication, Student Attendance |
|
Harry Mullin on 3/27/2009: You mention that START OF SCHOOL YEAR is in UC because "we are going to look for it in code". Does setting the case help the speed of DLookup? Case doesn't seem to make a difference in the where clause of dlookup. Have I missed something? |
|
Harry Mullin on 3/28/2009: Note on the DaysAbsent query: the nz function has an additional parameter which can be used to specify zero. Left off, it returns nothing.
Hence,
nz(CountOfAbsentDate,0)
works to avoid the isnull workaround. |
|
Harry Mullin on 3/28/2009: Strange result: In the query
StudentWithAbsentQ
I entered
[Forms]![AbsentReportF]![SchoolDays]
for DaysInSchool just to see what would return.
With AbsentReportF showing 188 days, I get 1/4 (?!)
Any idea why this happens? |
|
Harry Mullin on 3/28/2009: What is the distinction among Refresh, Requery, and Recalc?
I tend to find Requery is the only one which reliably does what I want, but you were able to use Refresh on the AbsentreportF (though verbally you said "requery") |
|
Richard Rost on 4/1/2009: Harry, what's the time index? Nothing in Access is case sensitive. I might have just done that to make it easier for YOU to see it in the code? |
|
Richard Rost on 4/1/2009: Harry, you are correct. I'm used to working with NZ in VBA code, not queries. In VBA code, the DEFAULT value of that second parameter is ZERO if you're working with a number type (such as an ID) otherwise it's a zero-length string. MOST of the time I'm working with NZ it's for looking up IDs to see if records exist, so you don't need to specify it.
From Microsoft's Help: If the value of the variant argument is Null, the Nz function [more...] |
|
Richard Rost on 4/2/2009: Harry, excellent question. I've added the answer to the Tips & Tricks section here:
http://www.599cd.com/tips/access/requery-v-refresh |
|
|
| |
| |
|
You may want to read these articles from the 599CD Blog: |
|
| |
|
 |
| |
| Do you have
questions about
Word, Excel, Access, Web Design, or computers in general? Just
ask us anything you'd like.
Click here
for assistance. |
|
|
|
|
|
| |
|
CLICK HERE for a FREE
lesson |
Order
your first 599CD course now.
Your Satisfaction is Guaranteed!
|
|
|
|
|
|
| |
|
|
|
|
