differences betw subforms and linked forms creating many-to-many link

[Joe Clark]

I'm developing an Access application for my use as a teacher, in which
there's a table of Students and a table of Classes. (A class itself
is defined by a Course, a Semester, and a unique ID.) Classes and
Students have a many-to-many relationship with a linking table
imaginatively named Class_Student. I have a form that allows a
teacher to see and modify the "roster" of the class. It is a typical
form-subform deal where the main form is based on Class and the
subform on Class_Student with fields pulled in from Student. The
subform works great. I can add new students, and the Class_Student
linking record is made automatically. If I "delete" the record, it
deletes the link but doesn't delete the student from Students.

At the beginning of the semester, I may invite students to type in
their personal information, so I'm adding a pop-out "linked form" that
lets them enter their info one-by-one instead of in the subform's
datasheet view. I EXPECTED this would work the same as the subform,
but it doesn't. In the "linked form" I'll type a new student's name
and ID, and the Student will be created, but the Class_Student linking
record won't be created.

The SQL for the pop-out linked form is the same as that for the
subform and the filter is set properly. (I just ran the form wizard
to make a version of the mainform with a linked form, then copied the
code etc to my existing main form.) So... what can I do to get the
same effects as above? i.e. When new students are entered they should
be linked to the Class.

 

[Mark A. Sam]

Hello Joe,

If I understand, you want a popup form to be established just like a subform
establishes a record when you enter data into the subform? That won't
happen. In a subform control, Access handles the syncing of the records.
With a popup form you have to enter the data into the link field which isn't
difficult. When you open the popup form from the main form, pass the
linking data through the openargs property. Then on the Open or Load event
of the popup form, assign the value of openargs to the linking field.

So if the link field is [id] then the openform method would look like this:


DoCmd.OpenForm "myForm", , , , , , [id]

On the Open or Load even of myForm the code would be:

[id] = OpenArgs

Id could be invisible. To avoid creating a new record you should check for
an existing record in myForm. If it exists, just open the form to that
record. If not then assign the value. There are different ways to do that,
and I'll assume you know how.

God Bless,

Mark A. Sam

 

[Joe Clark]

Hello Joe, Hi

If I understand, you want a popup form to be established just like a subform
establishes a record when you enter data into the subform? That won't
happen. In a subform control, Access handles the syncing of the records.
With a popup form you have to enter the data into the link field which isn't
difficult.

Thanks. When the wizard creates a linked form, it creates a bunch of
code which I thought would have the same effect of syncing the
records. In fact what it does is apply a filter to the linked form.
I'll try passing the value as an argument.

Id could be invisible. To avoid creating a new record you should check for
an existing record in myForm. If it exists, just open the form to that
record. If not then assign the value. There are different ways to do that,
and I'll assume you know how.

I don't know how. Just quickly, what method do I call to find out if
the record in the form already exists?

 

[Mark A. Sam]

I don't know how. Just quickly, what method do I call to find out if
the record in the form already exists?

It would be something like this:

If not isnull(OpenArgs) then
Dim rst as Recordset
Set rst = Me.RecordsetClone
rst.FindFirst "[id] = " & Openargs
If Not rst.NoMatch then
Me.Bookmark = rst.Bookmark
Else
[id] = Openargs
End If
rst.Close
Set rst=nothing
End If



End If

 

[Joe Clark]

I don't know how. Just quickly, what method do I call to find out if

the record in the form already exists?

It would be something like this:

If not isnull(OpenArgs) then
Dim rst as Recordset
Set rst = Me.RecordsetClone
rst.FindFirst "[id] = " & Openargs
If Not rst.NoMatch then
Me.Bookmark = rst.Bookmark
Else
[id] = Openargs
End If
rst.Close
Set rst=nothing
End If

End If

OK, I see what you're doing here. I have used the fact that the
linked-form code (generated by the form wizard) applies a filter to
the new form, so I don't have to FindFirst. What I did instead is to
use the Form_BeforeInsert event to set my [id] to the Openargs (if not
null). BeforeInsert runs only when the user starts typing a new
record, so I don't have to worry about over-writing existing records.
Now it works!