Form: Populate primary key from combobox value

[mtblankus]

I want to create a database to track attendance (once weekly class). I have a student table with data in it with fields of ID (primary key) name, address, etc. I created another table with 2 columns: ID and attendance date. The tables are related on the ID field.

I want to create a data entry form that populates the table with the attendance dates. I want it to look up and populate the data in the ID field based on the name in a combobox. I can get a combobox set up that gives a drop-down of the names in the student table, however, I can’t figure out how to have it populate the ID field. Do I need a query set up for that?

Thanks in advance!

[Ratslinger]

When using a list box based on the student records, the attendance table doesn’t know what item was selected from the list box. This is where a filter comes in handy. The sample attached has a list box containing names from the student table. I’ve add the student ID in the view to make the selection unique in case of duplicate names. After selecting an item, press the select button to display the attendance records for that student. If a `“New” record is to be entered, the StudentID field is pre-filled with the ID from the selected list box student .

This is done by using a “Filter” table. A single record table to store the selection so it may be retrieved by the subForm. The retrieval is done by refreshing the subForm - the job of the push button.

Sample: StudentAttendance.odb

[mtblankus]

Thank you. That helps.

[EasyTrieve]

First, to sort out the combo box vs list box question. A Combo box operates on only one field at a time, so it can’t update an index using a name, at least not without some Basic programming. But a LO list box can operate on two fields, like for example, a name field, and an ID field. So as Ratslinger has suggested, you need to be using a list box, not a combo box.

Next, I suggest you rename your ID in the student form, to Student_ID, to make it clear what it is. Then in the Attendance form, you probably want two ID’s. An ID for the record itself, that is auto-generted, and the ID that points to the Student table (a foreign key). I suggest you call the first ID, the auto-generated ID in the Attendance table, “Attendence_ID”, and give the 2nd id in this table, the exact same name as for the primary key in the student table, i.e. “Student_ID”, as follows:

Students table fields: Student_ID (auto-valued primary key), student_name, address, …
Attendances table fields: Attendence_ID (auto-valued primary key), Student_ID (foreign id), and Attendance_date

(I have found that good names for indexes, just makes it so much easier to see what is going on. I always name the table in the plural, i.e. Students, and the index in the singular, Student_ID, as it refers to just one student.)

Then update your relationships w/ Tools/Relationships, making sure that Students Student_ID is related to Attendance Student_ID.

In your attendance form which edits your attendance table you want two fields: a list box to select the student, and a field to set the attendance date. The data of the list box should be for the Student_ID. The list box should use an SQL query which returns two fields, in this order, the student’s name, and the Student_ID.

If you need any more specific details, just ask here with a follow-up comment.