Full Version: Using Filtered Data In Subform
UtterAccess Forums > Microsoft® Access > Access Forms

I have a datasheet subform whose Record Source is from a query.
The query is formed from two tables i.e LearnersOnCourse (LEFT JOIN) LearnersEndingCourse.
There would be a corresponding record match if every learner had ended their course. However, the query is used to list all "Learners still on Course" by checking the dteActualEndDate in the LearnersEndingCourse is null.
The mainform has unbound controls that allow to user to filter the subform further, i.e. cohorts of Learners on a particular course.
From the filtered subform I want to use the txtLearnerId, txtCourseId together with an unbound textbox tbxActEndDate from the main form to create new LearnersEndingCourse records by using the SQL syntax INSERT INTO through VBA code.
My table LearnerOnCourse has a checkbox which is included in the query record source and shown on the subform. (The subform Allows Edits but only the checkbox on the subform is enabled) I have successfully used this method to create new LearnersEndingCourse records when giving the user the option to select records because the selections are saved to the "LearnersOnCourse" table.
However, using filtered records in the subform as apposed to selected records I am finding it difficult to determine which records in the table "LearnersOnCourse" are now requiring a record to be created in the table "LearnersEndingCourse"
That is to say I don't want the user to have to select each of the records that are filtered in the subform.

Can I read the filtered subform like a recordset?

Any help would be much appreciated
I think I may have answered my question.
Cloning the subform as a recordset looks like it should do the trick

Set rstFltrLearnrs = Me.subFrmStillOnCourse.Form.RecordsetClone
Hi Roger

This link show another method.

This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.