Continuous Form - adding a record

[leahf via AccessMonster.com]

In our system we have students and the classes that they have attended (along
with other details). There will now be an opportunity for students to audit
classes after they have finished their studies but these students are not to
appear in the existing reports. In other words, except for one report
listing these students and the auditing classes, there should be no change in
the present status of these students (graduated) or the last class attended.

I suggested defining a new table consisting of the student_id, the class
being audited, the start and end date of the auditing. In this way the rest
of the system will remain untouched.

I then want a continuous form with a list of these students (linked to the
student table for the student's name and social security number).

I want to add new students on the same continuous form since there is so
little information to be added I figure that having a separate data entry
form will be more of a "burden" on the user.

The user should be able to just pick the social security number from a combo
box which will then fill in the name of the student, and then the user just
adds the class (from a list of classes) and puts in the start date.

Right now I am not getting the student name when I choose the social security
number. The combo box has the social security field as the control source,
and the row source as a query from the student table and the new studentAudit
table. The relationship is all student records and only those from
studentAudit that are equal.

Thank you.
Leah

 

[leahf via AccessMonster.com]

I think that I am now in the right direction using vba code. I will reply to
this if I continue to have problems.

Thanks to anyone who began checking out my problem. I somehow thought that
the fields could be filled with some automatic command rather than selecting
the fields from an sql statement and then updating them.

Leah

 

[John W. Vinson]

The user should be able to just pick the social security number from a combo
box which will then fill in the name of the student, and then the user just
adds the class (from a list of classes) and puts in the start date.

Right now I am not getting the student name when I choose the social security
number. The combo box has the social security field as the control source,
and the row source as a query from the student table and the new studentAudit
table. The relationship is all student records and only those from
studentAudit that are equal.

If you're trying to copy the student name from the Students table into the
Audits table... DON'T!

That would be redundant. The *only* field you should have from Students in the
Audit table would be the social security number (since it seems that you are
unwisely, and illegally, using that number as a unique ID). If you need to see
the student's name (or phone number, or any other biographical data) in
conjunction, you would just use a Query linking the two tables.

If I'm misunderstanding please clarify the structure of this audit table, and
perhaps post the SQL of the subform's Recordsource and the combo's Rowsource.

 

[leahf via AccessMonster.com]

Thanks for your response. No, the audit table does not have the name, only
the student id (not even the social security number).

What I want, though, is for the name to appear on the screen when the social
security number is chosen. In other words, a social security number is
chosen, then the line on the screen has the name of the student. The key
field is the student id (not the social security number) in both tables.

In either case, it is not working out and I think that I will have to have an
"insert" button with a subform on the screen for entering new information.

If you have any simpler suggestions, I would appreciate it. I really thought
that there would be a simple way to set it up.

Thanks again for your response.
Leah

The user should be able to just pick the social security number from a combo
box which will then fill in the name of the student, and then the user just

[quoted text clipped - 5 lines]

table. The relationship is all student records and only those from
studentAudit that are equal.

If you're trying to copy the student name from the Students table into the
Audits table... DON'T!

That would be redundant. The *only* field you should have from Students in the
Audit table would be the social security number (since it seems that you are
unwisely, and illegally, using that number as a unique ID). If you need to see
the student's name (or phone number, or any other biographical data) in
conjunction, you would just use a Query linking the two tables.

If I'm misunderstanding please clarify the structure of this audit table, and
perhaps post the SQL of the subform's Recordsource and the combo's Rowsource.

 

[John W. Vinson]

The user should be able to just pick the social security number from a combo
box which will then fill in the name of the student, and then the user just
adds the class (from a list of classes) and puts in the start date.

In that case, you can just use a Combo Box storing the StudentID and
displaying the SSN. You can include the student's name in the combo's
RowSource - set the width of that field to 0 in the combo's ColumnWidths
property. Put another textbox on the form with a control source

=comboboxname.Column(n)

using the name of the combo box, where n is the *zero based* index of the
field you want to display - e.g. if the name is in the fourth column use (3).

 

[leahf via AccessMonster.com]

Thank you very much.

Leah

In that case, you can just use a Combo Box storing the StudentID and
displaying the SSN. You can include the student's name in the combo's
RowSource - set the width of that field to 0 in the combo's ColumnWidths
property. Put another textbox on the form with a control source

=comboboxname.Column(n)

using the name of the combo box, where n is the *zero based* index of the
field you want to display - e.g. if the name is in the fourth column use (3).