Prev: checking duplicate values on data entry
Next: A2002 SP3: Exception when using Listview control
From: leahf via AccessMonster.com on 1 Feb 2010 05:15
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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201002/1

From: leahf via AccessMonster.com on 1 Feb 2010 08:00
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

leahf wrote:
>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

--
Message posted via http://www.accessmonster.com

From: John W. Vinson on 1 Feb 2010 12:33
On Mon, 01 Feb 2010 10:15:20 GMT, "leahf via AccessMonster.com" <u13396(a)uwe>
wrote:

>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.
--

John W. Vinson [MVP]
From: leahf via AccessMonster.com on 2 Feb 2010 01:47
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

John W. Vinson wrote:
>>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.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201002/1

From: John W. Vinson on 2 Feb 2010 23:51
On Mon, 01 Feb 2010 10:15:20 GMT, "leahf via AccessMonster.com" <u13396(a)uwe>
wrote:

>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).
--

John W. Vinson [MVP]
 |  Next  |  Last
Pages: 1 2
Prev: checking duplicate values on data entry
Next: A2002 SP3: Exception when using Listview control