ms access form to insert group of records

Question

I have a student database contains the following tables:

Student Table (Id, StudentName, ClassNum, ....)

Action Table (Id, ActionDate, studentID, Status, reason, note)

Status fields possible values are: attended(=1) absent(=2), permitted(=3). I want to build a form has the two unbound fields (Class Number and date) in the top of the form. A subform should contain a grid (list) of all the students of that class number; each row of that grid consists of: Student name (non editable), status (drop-down list for example), reason (text box). The form should show all the class students.

When the user enters data (status, reason and notes) the form should create a record in the action table with the student id, the date (entered in the top section) and the add data (i.e. status, reason made notes).

How can i build a form contains records from a table (with records not exist before adding them).

You have already described the overall design and behavior of the form. The final question seems to be asking for a complete tutorial of how Access forms and subforms work and how to implement the list of requirements. This question is too broad for Stack Overflow. Perhaps try the Form Wizard (on the Create ribbon) first. I suggest searching the web for Access form tutorials, or if you have a local library, look for a good how-to Access book. (And I don't not mean this to be snarky, rather this is exactly how I learned most of my programming knowledge. It's a great way to start.) — C Perkins
I know how to link teh main form to sub form. My problem, which i searched but could not find a solution, is: What is the sub form source of records? If I use the Student table, then student table doesn't contain the required fields (status , reason and note) If I use to the action table, then action table doesn't contain any row related to that date and class number. Hence, the form will contain no rows. — Expert Pe
The form needs to show all the student names (from student table filtered by class num: the unbound control at the top of the form), date (from the unbound field top of the from) , the rest of the fields will be null values (i.e status , reason and note) and filled bu the user. The records in action table will not be created until the data is entered and saved. Hence.they will not be shown. — Expert Pe

June7 June7 · Accepted Answer · 2018-10-25T20:11:13

You need an INSERT SELECT action SQL to create a batch of records in Action table.

CurrentDb.Execute "INSERT INTO Action(StudentID, ActionDate) SELECT ID, " & _
"#" & Me.tbxDate & "# FROM Students WHERE ClassNum = " & Me.tbxClassNum

If ClassNumber is a text field, will need apostrophe delimiters for filter criteria.

This assumes each student can have only one class. If they can have multiple classes, then need ClassNumber field in Action.

Then filter form for that batch of records to do data entry to other fields in Action.

ms access form to insert group of records

1 Answers