Text box AfterUpdate event need to populate a subform with records

[Manos39]

Hello, i have difficulty to produce proper code on an AfterUpate event

In my Db in order to keep Attendance of athletes i have a main form called Fr_CourseSessions
it has subforms to select Academic year Tennis Cource, Instructor etc

All is working except: to make easier to track attendance i have inserted on that form, a subform "Frmsub_CourseAttendances", set to continuous, having as source

PARAMETERS [Forms]![Fr_CourseSessions]![cboGoToCourse] Long, [Forms]![Fr_CourseSessions]![Frmsub_CourseAttendances].[Form]![txtDate] DateTime;
SELECT Tbl_CourseAttendances.courseID, Tbl_CourseAttendances.CourseDate, Tbl_CourseAttendances.athleteID, Tbl_CourseAttendances.Attended
FROM Tbl_CourseAttendances INNER JOIN Tbl_Athletes ON Tbl_CourseAttendances.athleteID = Tbl_Athletes.athleteID
WHERE (((Tbl_CourseAttendances.courseID)=[Forms]![Fr_CourseSessions]![cboGoToCourse]) AND ((Tbl_CourseAttendances.CourseDate)=[Forms]![Fr_CourseSessions]![Frmsub_CourseAttendances].[Form]![txtDate]))
ORDER BY Tbl_Athletes.aSurname, Tbl_Athletes.aName;

and what i am after is

a) Opening my main form "Fr_CourseSessions", and selecting year from cboGotoYear, (eg 2021/22), then selecting course from cboGotoCourse,
then in my subform, Frmsub_CourseAttendances which has a txtDate, typing eg 1/5/2022 (or 01/05/2022 a date with records) hit enter,
if attendances are already recorded for that date, these will show in the form, so i can check Attendance (this is working )
or if not, clicking the Add Athletes button , i would like to add all Athletes in that course (currently marked as Active in Tbl_Athletes) so if someone is/was absent, to uncheck Attended
vba behind Add Athletes button is

Private Sub Add_Athletes_Click()
Dim dbs As DAO.Database
Dim strSQL As String

If Not IsNull(Me.txtDate) Then
    strSQL = "INSERT INTO Tbl_CourseAttendances(athleteID,CourseDate) " & _
        "SELECT athleteID, #" & Format(Me.txtDate, "yyyy-mm-dd") & "# " & _
        "FROM Tbl_Athletes WHERE aStatus=0;"

    CurrentDb.Execute strSQL, dbFailOnError
    Me.Requery
End If
End Sub

which gives errors and does not produce the list of Athletes currently in that Course

Could you please help me?

 

[Eugene-LS]

Could you please help me?

You are hindered by your relationships between tables

 

[Manos39]

You are hindered by your relationships between tables

i think that is not a problem (indexing is proper so not book same court same time in a day etc.)

 

[bastanu]

@ Manos39

You need to go back to the drawing board. You are using lookup fields in your tables and those will hinder your future development (read more here:http://access.mvps.org/access/lookupfields.htm). The main form is bound to the Courses table yet you are not displaying any fields from it so any further data entry\editing will happen on the first record. You need to also properly link the subforms to the main form using the child\master fields to automatically add those when adding records to the subforms. And finally your subforms are currently based on non-editable queries; you need to remove the extra tables and use those in combos' row sources instead (for example to display Athlete's name you remove the tblAthletes table from the record source and use a combo bound to the AthleteID field in the Tbl_CourseAttendances table).

Cheers,

 

[Manos39]

@ Manos39

You need to go back to the drawing board. You are using lookup fields in your tables and those will hinder your future development (read more here:http://access.mvps.org/access/lookupfields.htm). The main form is bound to the Courses table yet you are not displaying any fields from it so any further data entry\editing will happen on the first record. You need to also properly link the subforms to the main form using the child\master fields to automatically add those when adding records to the subforms. And finally your subforms are currently based on non-editable queries; you need to remove the extra tables and use those in combos' row sources instead (for example to display Athlete's name you remove the tblAthletes table from the record source and use a combo bound to the AthleteID field in the Tbl_CourseAttendances table).

Cheers,

I am trying to understand what to do. I dont understand how to build it from now on. In last sentence do you suggest it or purpose not to. Also if i wasnt using lookup fields in my tables... When seeing recordsourses i would try to understand what is in there (whome of instrucrors which course which athlete) is it something.. So i thought i should.. I didn't realise it would mess things. Finaly do i need to do more eg my relationships (structure)?

 

[bastanu]

Starting from the last question I think you should have a second look at the relationships and simply them not make them more complicated. Think of the data flow, and remove any duplicate paths (for example if a course has a year and an instructor no need to have another field for year in the CourseInstructor table).

The lookup fields in the tables are hiding the actual values making it difficult to create queries and write VBA; users should not interact with tables or queries so replace them with combos in the forms and reports.

What I meant for the subforms is to bind each to their main table only and use combos to display related info from the other tables (that you currently have included in the subform's recordsources).

Cheers,

 

[Manos39]

Starting from the last question I think you should have a second look at the relationships and simply them not make them more complicated. Think of the data flow, and remove any duplicate paths (for example if a course has a year and an instructor no need to have another field for year in the CourseInstructor table).

The lookup fields in the tables are hiding the actual values making it difficult to create queries and write VBA; users should not interact with tables or queries so replace them with combos in the forms and reports.

What I meant for the subforms is to bind each to their main table only and use combos to display related info from the other tables (that you currently have included in the subform's recordsources).

Cheers,

Thank you. I shall try to accomplish your suggestions. Also i saw what you warn me about when eg in a course { mini tennis) with an id from Tbl_CourseYears, it is a different course from another year's course with same name (different ID) so when in a query, if pull up athletes from courses by the name of course (field from LookUp) that info might or propably be wrong as an athlete in that year or course (might not exist).
If you wouldn't warn me about that, even thought i was looking at the problem, i couldnt realise what was going wrong as i got used to do lookups in my tables.
So thank you a lot!! Happy Easter!