Linked Forms Code

  • Thread starter Thread starter JohnB
  • Start date Start date
J

JohnB

Hi. Some time ago, with help from this Newsgroup, I
produced some On Click code, as below, for a Forms
Command Button. Ive now realised that it is doing some
odd things and I wonder if anyone can spot why. Sorry for
this long explanation.

I have two forms, one called Schools and Offers, the
other called frmMentorsAndSBTs. The frmMentorsAndSBTs
form has a subform called frmMentorSBTSchoolSubform. The
subforms source is tblMentorSBTSchool which links records
from the Mentors table and Schools and Offers table.

The idea is that, with a particular school record being
viewed in the Schools and Offers form, clicking on the
button opens frmMentors AndSBTs to show those Mentor
records which have a subform record showing that school.
Most mentors have just one subform record, because they
remain at that one school for life. Others have more than
one, because they can move schools.

What Ive noticed is that, if a mentor has two subform
records, clicking on the button opens two identical
frmMentorsAndSBTs forms, each showing the same Mentor
details and the two subform records. Im pretty sure that
if a mentor had three subform records, this would result
in three identical frmMentorsAndSBTs records being shown.
It works fine if the mentor has just one subform record.

Can anyone see why these multiple forms are opening?

Thanks for any help you can give.

Cheers, JohnB


Private Sub Mentors_Click()
On Error GoTo Err_Mentors_Click
Dim varCheck As Variant
Dim strMsg As String
varCheck = DLookup
("[MentorID]", "[tblMentorSBTSchool]", "[tblMentorSBTSchoo
l].SchoolID=" & SchoolID)
If Not IsNull(varCheck) Then


DoCmd.OpenForm "frmMentorsAndSBTs", , , "MentorID IN
(SELECT MentorID FROM tblMentorSBTSchool WHERE
tblMentorSBTSchool.SchoolID=" & SchoolID & ")"
Else
strMsg = "There is no Mentor or School Based Tutor
associated with This School."
MsgBox (strMsg)
' Cancel = True
End If
Exit_Mentors_Click:
Exit Sub

Err_Mentors_Click:
MsgBox Err.Description
Resume Exit_Mentors_Click

End Sub
 
Hi. No need to reply to this. I sorted it by setting up
Group By in the query feeding the form opened by the
Command Button. Thanks anyway. JohnB
-----Original Message-----
Hi. Some time ago, with help from this Newsgroup, I
produced some On Click code, as below, for a Forms
Command Button. Ive now realised that it is doing some
odd things and I wonder if anyone can spot why. Sorry for
this long explanation.

I have two forms, one called Schools and Offers, the
other called frmMentorsAndSBTs. The frmMentorsAndSBTs
form has a subform called frmMentorSBTSchoolSubform. The
subforms source is tblMentorSBTSchool which links records
from the Mentors table and Schools and Offers table.

The idea is that, with a particular school record being
viewed in the Schools and Offers form, clicking on the
button opens frmMentors AndSBTs to show those Mentor
records which have a subform record showing that school.
Most mentors have just one subform record, because they
remain at that one school for life. Others have more than
one, because they can move schools.

What Ive noticed is that, if a mentor has two subform
records, clicking on the button opens two identical
frmMentorsAndSBTs forms, each showing the same Mentor
details and the two subform records. Im pretty sure that
if a mentor had three subform records, this would result
in three identical frmMentorsAndSBTs records being shown.
It works fine if the mentor has just one subform record.

Can anyone see why these multiple forms are opening?

Thanks for any help you can give.

Cheers, JohnB


Private Sub Mentors_Click()
On Error GoTo Err_Mentors_Click
Dim varCheck As Variant
Dim strMsg As String
varCheck = DLookup
("[MentorID]", "[tblMentorSBTSchool]", "[tblMentorSBTSchoo
l].SchoolID=" & SchoolID)
If Not IsNull(varCheck) Then


DoCmd.OpenForm "frmMentorsAndSBTs", , , "MentorID IN
(SELECT MentorID FROM tblMentorSBTSchool WHERE
tblMentorSBTSchool.SchoolID=" & SchoolID & ")"
Else
strMsg = "There is no Mentor or School Based Tutor
associated with This School."
MsgBox (strMsg)
' Cancel = True
End If
Exit_Mentors_Click:
Exit Sub

Err_Mentors_Click:
MsgBox Err.Description
Resume Exit_Mentors_Click

End Sub

.
 
Back
Top