Bulk attendance list (1 Viewer)

[Mirihika]

I'm good at SQL, less so with VBA and I use Access 2003.

I have three tables, dbo_Members, dbo_Classes and dbo_CourseRegistrations and I want a form that creates a class and writes its attendance list into about 20 records in dbo_CourseRegistrations with minimum user work. Here's the plan:

Draft query already done:
SELECT retrieves a subset of records from dbo_Members using fixed eligibility selection criteria
Query input parameters supply class date etc. ready for writing to dbo_Classes and dbo_CourseRegistrations
That displays rows from dbo_Members who might have attended, entered parameters and an unbound yes/no check box

Here's the SQL which works fine:

SELECT DISTINCT dbo_Members.[Full Name], [Enter Date] AS expr1, [Enter Class Code] AS expr2 FROM dbo_Members INNER JOIN dbo_CourseRegistrations ON dbo_Members.MemberID=dbo_CourseRegistrations.MemberID WHERE (((dbo_CourseRegistrations.[Class Code]) Like "AT*") AND ((dbo_Members.FinancialCurrent)=Yes)) OR (((dbo_CourseRegistrations.[Class Code]) Like "IP*") AND ((dbo_Members.FinancialCurrent)=Yes))


What I need help with now is:
Binding the check box correctly for the user to select some rows but not others
VBA code for:
Writing ONE record only for the whole class to the dbo_Classes table containing parameter Date and Class Code values and some constants
Writing fields from all selected rows plus one or two extra constants to dbo_CourseRegistrations, one at a time on record exit

If someone can please give me draft code with the difficult bits done, I know enough to tweak it.

 

[Cronk]

This will give you a start and you can come back for more if needed

Dim db as database, rst as recordset

set db = currentdb
set rst = db.openrecordset("SELECT MemberID, ClassID FROM [your sql select query with ClassID included in the sql]")
rst.movefirst
do while not rst.eof
    db.execute "Insert into dbo_CourseRegistrations (MemberID, ClassID,  ClassDate) SELECT rst!MemberID & "," & rst!ClassID & ","  & format(rst![Enter Date],"mm,dd,yyyy")
   rst.movenext
loop
rst.Close:set rst = nothing
set db= nothing

 

[Mirihika]

Thank you, I'll work on that