Attendance Form trouble RRS feed

  • Question

  • I am making an attendance database to keep track of various activities and people who attend them. I create an append query to add new records for every current resident we have. When I create the form/subform strickly for data entry. 

    My main form will allow me to pick the activity, category, and date

    the subform I have set to display all records in my attendance table. However I want it to only display new records created when form is open that can still be edited for participation or not.  Having trouble, I can either get it to display all or nothing and then I can't get my selection from my main form to populate the activity category and date to those same new records. 

    Hope you can help me, pulling my hair out. 

    Wednesday, October 16, 2019 6:01 PM

All replies

  • Please post 2 screenshots: your Relationships diagram, and the form as it stands right now. A picture is a thousand words.

    -Tom. Microsoft Access MVP

    Wednesday, October 16, 2019 6:29 PM
  • Website won't let me post images until my account is verified....

    Wednesday, October 16, 2019 9:41 PM
  • well there's some points to your post which are difficult to be sure what is meant..... out-of-the-box I think MS offers a free attendance template db which you might look for online....not 100% sure on that but I seem to remember one...

    in regard to this: the subform I have set to display all records in my attendance table. However I want it to only display new records created when form is open 

    so that is contradictory, but in any case a form (sub form) can be optioned to be only new records; it is the 'Data Entry' property in the Data tab of the form's property.

    you do not need to append records for every current resident as a batch thing...the sub form in Data Entry mode will offer a record for entry.....

    Wednesday, October 16, 2019 10:42 PM
  • that does not have to stop you. THink about it: what if you put those images in a free OneDrive account, and put the links to them here...

    -Tom. Microsoft Access MVP

    Thursday, October 17, 2019 2:44 AM
  • If you set the subform's DataEntry property to True (Yes) it will be empty when you move to each activity record in the parent form, and you'll be able to insert new rows in the subform.  However, if you then move to another activity record in the parent form and back again to the original activity record, the subform will again be empty.

    To retain the visibility of those rows entered in the current session of the parent form, set the subform's DataEntry property to False (No) and add a Boolean (Yes/No) column, named Completed say, to the Attendances table.  Set the subform's RecordSource property to the following:

        SELECT * FROM Attendances WHERE NOT Completed;

    In the parent form's Close event procedure update the Attendances table as follows:

       Const conSQL = "UPDATE Attendances SET Completed = TRUE"
       CurrentDb.Execute conSQL, dbFailOnError

    You should then find that when you first open the parent form the subform will show all rows, so immediately close it again to update the table.  When you open it subsequently the subform should be empty, but you'll be able to insert new rows and these will remain visible and editable until you close the parent form.  You don't need to include a control bound to the Completed column in the subform of course; it will do its work in the background.

    As regards linking the parent form and subform, it sounds like the primary key (or a candidate key at least) of the activities table, and the corresponding foreign key in the attendances table, is a composite one of the ActivityCategory and ActivityDate columns, in which case the LinkMasterFields and LinkChildFields properties of the subform control should both be set to:


    Ken Sheridan, Stafford, England

    Thursday, October 17, 2019 1:56 PM