1 form with 2 ComboBoxes pointing to the same source table. How to select diff. values in each Combo

[Guest]

I have a Student and a School table (School is liked to Student). The Student form has 2 Combo Boxes called: School current year AND School last year. These Combo Boxes are linked to the School table. When I select a school from one ComboBox, the second ComboBox automatically displays the same school as well. I need to be able to select different schools. Any idea how to do iT?

 

[John Vinson]

I have a Student and a School table (School is liked to Student). The Student form has 2 Combo Boxes called: School current year AND School last year. These Combo Boxes are linked to the School table. When I select a school from one ComboBox, the second ComboBox automatically displays the same school as well. I need to be able to select different schools. Any idea how to do iT?

What is the Control Source of each combo box? If it's the SchoolID
field in the Student table then yes, it will show whatever school is
in that field in the current student's record; and having a
LastYearSchoolID field in the Student table would be non-normalized
and bad design.

What do you intend to do with this field?

 

[Guest]

Hi John

Thank you for your reply
Correction: School last year should read School first year. It is static information
I need to be able to do the following: Select a School in the School curreent year column And select a different school in the School first year column. School current year and School first year both are attributes in the Student table
Both of these columns are linked to the SchoolID of the School table. Any ideas

Regards
A

================

 

[John Vinson]

Hi John,

Thank you for your reply.
Correction: School last year should read School first year. It is static information.
I need to be able to do the following: Select a School in the School curreent year column And select a different school in the School first year column. School current year and School first year both are attributes in the Student table.
Both of these columns are linked to the SchoolID of the School table. Any ideas?

I don't see the problem, then. Create two Combo Boxes based on the
Schools table. Bind one of them to the SchoolFirstYear field; the
other to the SchoolCurrentYear field. They will be independent (if
they have different Control Source properties).

 

[John Vinson]

Now, I must create the STUDENT- REGISTRATION relationship. I don't know how to "hook" the REGISTRATION to the STUDENT. Could you help with this question?

There should be a field in the REGISTRATION table of the same datatype
and size as the Primary Key of the STUDENT table (Long Integer if that
Primary Key is an Autonumber). This field (which I'd just name
StudentID, or the same as the name of the STUDENT primary key field)
is called a "Foreign Key" - the primary key/foreign key link is the
basis of all relational database design.

 

[John Vinson]

Hi,

I don't know the mechanics of linking the REGISTRATION to the STUDENT table.

Ummm... that's what I just TOLD you. You put a StudentID foreign key
into the Registration table. That is "the mechanics of linking" - it's
how it's done.

How do I ensure, that after the last column on the STUDENT Subform, the control automatically goes to the REGISTRATION Subform.

Ideally the Registration subform should be a subform *OF* the Student
subform. The alternative (two side by side subforms) can be done but
involves some trickiness and some VBA code.

 

[Guest]

Hi

The forms/suforms "line up" as they should: Family, Student, Registration
....How do I ensure, that after the last column on the STUDENT Subform, the control automatically goes to the REGISTRATION Subform

Right now, after the last STUDENT column (ALLERGIES), the control goes to the first column of the next STUDENT (a sibling of the previous STUDENT). I would like to automatically go from the last column of the STUDENT Subform to the first column of the REGISTRATION Subform. Do you know how to do it

Regards
A

=================


Ideally the Registration subform should be a subform *OF* the Studen
subform. The alternative (two side by side subforms) can be done bu
involves some trickiness and some VBA

 

[John Vinson]

Hi,

The forms/suforms "line up" as they should: Family, Student, Registration.

It *sounds* like the Student subform is a Datasheet or Continuous
subform, with the Registration subform physically beside it on the
Family form - i.e., the Student form is a subform of the Family form,
and the Registration form is also a subform of the Family form.

What I'm suggesting is that you change the default view of the Student
subform from Datasheet (or Continuous) to Single Form, and to put the
Registration form onto it as a sub-subform.

Am I misunderstanding how you have the form set up?

 

[John Vinson]

What I need now is to be able to go from a STUDENT line (row) (in the STUDENT Subform) to the first column of the REGISTRATION Subform by pressing a function key or PageDown, etc. ( By pressing PageDown, the control would jump from the STUDENT Subform to the REGISTRATION Subform.) This is what I don’t know how to do. Can you help?

If the Tab Order is set up correctly - with the Registration subform
control being next after the last other control in the Student subform
- just pressing the <tab> or <enter> key should do so. The first
control in the Registration subform would get the focus.

Right mouseclick the little square at the upper left of the Student
subform in form design view and move the controls around in the Tab
Order pane, if it's not set correctly.

 

[Guest]

Hello John

Let's say, that there are 3 STUDENTS and I want to change the REGISTRATION info of the first one

If I use the "TAB approach", I would have to TAB through the attributes of the 3 STUDENTS in order to get to the REGISTRATION info

What I need is to be able to go from a STUDENT line (row) (in the STUDENT Subform) to the first column of the REGISTRATION Subform by pressing a function key or PageDown, etc. ( By pressing PageDown, the control would jump from the STUDENT Subform to the REGISTRATION Subform.) This is what I don’t know how to do. Can you help

Regards
A
================================

 

[John Vinson]

Hello John,

Let's say, that there are 3 STUDENTS and I want to change the REGISTRATION info of the first one.

If I use the "TAB approach", I would have to TAB through the attributes of the 3 STUDENTS in order to get to the REGISTRATION info.

What I need is to be able to go from a STUDENT line (row) (in the STUDENT Subform) to the first column of the REGISTRATION Subform by pressing a function key or PageDown, etc. ( By pressing PageDown, the control would jump from the STUDENT Subform to the REGISTRATION Subform.) This is what I don’t know how to do. Can you help?

We are not communicating, AG. I think I'm misunderstanding your forms.

You said that you have the REGISTRATION subform as a sub-subform of
the Student form. Now you imply that the STUDENT subform is either a
datasheet or a continuous subform, with "rows".

It is impossible to put a Subform on a datasheet or a continuous
subform. So there's a contradiction.

My suggestion does not involving "tabbing through the attributes of
the 3 students" - it would just mean displaying the desired student on
the Students form, using a combo box or other technique to locate the
student.

Could you explain again how your Subforms are arranged? Are they
really Subforms (inside a Subform control)?

 

[John Vinson]

The STUDENT Subform1 and the REGISTRATION Subform1 ARE in Datasheet format.

I don't know how to "jump" from a STUDENT row to the corresponding REGISTRATION Subform (in an "elegant" way).
So, this is my predicament.

Just to clarify:

You understand that the most efficient way to do this is to set the
STUDENT subform to Single view, and put the REGISTRATION form onto
the STUDENT form as a sub-subform.

You have consciously decided to reject this way of doing things.

Correct?

If so, you'll need some VBA code. Put a textbox on the mainform, named
txtRelay. (It can be invisible). Set the Master Link Field property of
the REGISTRATION subform to

[txtRelay]

This won't be one of the options in the dropdown box but you can type
it in.

Then, open the STUDENT form in design view; view its properties.
Select the Events tab and click the ... icon by the Current event;
choose "Code Builder". Access will put [Event Procedure] on the line,
and put you into the code editor with a Sub and End Sub line. Add just
two lines of code:

Private Sub STUDENT_Current()
Parent!txtRelay = Me!STUDENT_ID
Parent!subREGISTRATION.Requery
End Sub

using the name of the linking field where I have STUDENT_ID, and the
Name property of the second subform control where I have
subREGISTRATION.