Problem iterating through records

[liddlem]

Sorry for the long post
I have a complex 'New student enrolment' form that contains 3 subforms on it. I need to ensure that THREE conditions are met before the user is allowed to save the record.
Access 2016 front end and SQL 2016 back end.

The Parent form is the 'Enrolment Enquiry' form.

The SubForms are:
Students - Can add more than one student
UNDERLYING QUERY FOR THE STUDENTS

SELECT dbo_ENR_StudentDtl.ID_EnrDtl, dbo_ENR_StudentDtl.ID_EnrHdr, dbo_STU_Student.ID_Student
FROM dbo_ENR_StudentDtl INNER JOIN dbo_STU_Student ON dbo_ENR_StudentDtl.ID_Student = dbo_STU_Student.ID_Student
WHERE (((dbo_ENR_StudentDtl.ID_EnrHdr)=[Forms]![FRM_ENR_EnrolmentHdr]![ID_EnrHdr]));

Family - Can add more than one family member
UNDERLYING QUERY FOR THE FAMILY

SELECT dbo_CMTY_FamilyRelations.ID_Student, dbo_CMTY_FamilyRelations.ID_CMTY, Trim([dbo_CMTY_Member].[NAME_Surname]) & ", " & Trim([dbo_CMTY_Member].[NAME_First]) AS Member, dbo_CMTY_FamilyRelations.ID_CMTY_RelType, dbo_CMTY_FamilyRelations.FLAG_Responsible_Adult, dbo_CMTY_FamilyRelations.FLAG_StudentResidesWith, dbo_CMTY_FamilyRelations.FLAG_GUARDIAN, dbo_CMTY_FamilyRelations.ALT_GUARDIAN_SEQ, dbo_CMTY_FamilyRelations.FLAG_StuMayContact, dbo_CMTY_FamilyRelations.FLAG_StuAllowedAtAddress, dbo_CMTY_FamilyRelations.FamRelNotes, dbo_CMTY_FamilyRelations.Obsolete
FROM dbo_CMTY_FamilyRelations INNER JOIN dbo_CMTY_Member ON dbo_CMTY_FamilyRelations.ID_CMTY = dbo_CMTY_Member.ID_CMTY
WHERE (((dbo_CMTY_FamilyRelations.ID_Student) In (select ID_Student from [QRY_ENR_Students]
)))
ORDER BY dbo_CMTY_FamilyRelations.ID_Student, Trim([dbo_CMTY_Member].[NAME_Surname]) & ", " & Trim([dbo_CMTY_Member].[NAME_First]);

FamilyAddress - Can add more than one address
UNDERLYING QUERY FOR THE FAMILYADDRESS

SELECT dbo_CMTY_AddressLink.ID_CMTY_AddressLink, Trim([dbo_CMTY_Member].[NAME_Surname]) & ", " & Trim([dbo_CMTY_Member].[Name_First]) AS Member, dbo_CMTY_AddressLink.ID_CMTY, dbo_CMTY_AddressLink.ID_CMTY_Address, dbo_CMTY_AddressLink.CMTY_AddressType, dbo_CMTY_AddressLink.CorrespondenceAddr, dbo_CMTY_AddressLink.Care_Of, dbo_CMTY_AddressLink.PO_Box, dbo_CMTY_AddressLink.MailToPMB, dbo_CMTY_AddressLink.LinkNotes
FROM (dbo_CMTY_AddressLink INNER JOIN QRY_ENR_FamilyRelations ON dbo_CMTY_AddressLink.ID_CMTY = QRY_ENR_FamilyRelations.ID_CMTY) INNER JOIN dbo_CMTY_Member ON QRY_ENR_FamilyRelations.ID_CMTY = dbo_CMTY_Member.ID_CMTY;

When capturing the data, I need to ensure that
1. EACH student (on the STUDENTS subform) is linked (related) to at least one Family member (on the Family Subform) and
2. EACH Family Member must be linked to at least ONE address on the Family Address subform.

I have added a 'Btn_Save' for the user to press when they want to save the record -
I am getting stuck at the point that I need to cycle through the recordset
The two lines where I am having difficulty are:

' The following line throws a 3061 error (Too few paremeters. Expected 1)

Set rs = CurrentDb.OpenRecordset("QRY_ENR_Students", dbOpenDynaset, dbSeeChanges)

and
'Check that this first student has at least one family member

    MyFam = DCount("ID_CMTY", "QRY_ENR_FamilyRelations", "ID_Student = " & rs.Fields(0))

The full code is as follows:

Private Sub Btn_Save_Click()
Dim MyCmtyCount
Dim MyStudCount As Integer
Dim MyRelationCount As Integer
MyCmtyCount = 0
MyRelationCount = 0
MyStudCount = 0

'Check that at least ONE student exists
MyStudCount = DCount("ID_EnrHdr", "QRY_ENR_Students", "ID_EnrHdr = " & Me.ID_EnrHdr)
If MyStudCount < 1 Then
   MyMsg = MsgBox("You must connect at least ONE student to the enrolment process" & vbCr _
   & "The record will not be completed at this time", vbOKOnly, "Missing STUDENT information")
   Exit Sub
End If

'Count how many COMMUNITY MEMBERS (Relations) have been added - There must be at least ONE
MyCmtyCount = DCount("ID_Cmty", "QRY_ENR_CMTY_Members", "ID_EnrHdr = " & Me.ID_EnrHdr)
If MyCmtyCount < 1 Then
   MyMsg = MsgBox("You must connect at least ONE Community Member to the enrolment process" & vbCr _
   & "The record will not be completed at this time", vbOKOnly, "Missing COMMUNITY MEMBER information")
   Exit Sub
End If

''For each STUDENT in the enrolment application, check that they are connected to at least ONE Community Member
On Error GoTo Err_Proc

Dim rs As DAO.Recordset
' The following line throws a 3061 error (Too few paremeters. Expected 1)
Set rs = CurrentDb.OpenRecordset("QRY_ENR_Students", dbOpenDynaset, dbSeeChanges)
AllFam = True 'assume that BOF is connected to a maily member
If rs.RecordCount <> 0 Then
*rs.MoveFirst
    While Not rs.EOF
    'Check that this first student has at least one family member
    MyFam = DCount("ID_CMTY", "QRY_ENR_FamilyRelations", "ID_Student = " & rs.Fields(0))
    If MyFam > 0 And AllFam Then
        AllFam = True
    Else
        AllFam = False
        GoTo Exit_Proc
    End If
    rs.MoveNext
    Wend
End If

' Now check the addresses
' copy code from above and change accordingly
    
Exit_Proc:
On Error Resume Next
*rs.Close
    Set rs = Nothing
    
If Not AllFam Then
   MyMsg = MsgBox("You must connect at least ONE student to the enrolment process" & vbCr _
   & "The record will not be completed at this time", vbOKOnly, "Missing Student/Parent/Address information")
   Cancel = True
   Exit Sub
End If
Exit Sub

Err_Proc:
  MsgBox Err.Number & " " & Err.Description
  Err.Number = 0
  Resume Exit_Proc

End Sub

 

[Ranman256]

i dont see the need for any code. If you use subforms on the student, the user just enters the data for the parents, data for the family member and address.

No need for a Save button. Data is saved after you move off the record. Tho you can run a check to see if they DID enter some family,address, and parents.

 

[liddlem]

Sorry - I guess that I should have mentioned that there is a lot more going on under the hood.
In essence, the user clicks separate buttons to open (and thus add) seperate windows for Student, Family and Address. (This is because there is compulsory data that must be captured for each and there simply is not enough real estate on one screen to do this.
When they close the relevant window, code is writing that information back into the Enrolment Enquiry screen.
Hence the complexity.

 

[The_Doc_Man]

The normal way to assure linkage is through relational integrity (RI), but you are saying that you require "at least one" (but implies "not all") family records be related to the student. If "not all" is actually the case, then RI won't help. The same concept might apply for the Address form.

Can you clarify that relationship / requirement given my comments? Because what I said above is how I read what you told us and it seems somehow to not make complete sense without better explanation of that situation.

 

[Cronk]

I too am confused by your set up. It would help me at least to see your table structure as well as the sql for your query (QRY_ENR_FamilyRelations). Also, is your requirement to have multiple addresses for a family member that you need to record addresses in a separate table.

 

[Gasman]

Sorry - I guess that I should have mentioned that there is a lot more going on under the hood.
In essence, the user clicks separate buttons to open (and thus add) seperate windows for Student, Family and Address. (This is because there is compulsory data that must be captured for each and there simply is not enough real estate on one screen to do this.
When they close the relevant window, code is writing that information back into the Enrolment Enquiry screen.
Hence the complexity.

Have you considered Tab/Page controls?
I have a form with Client data on the first tab, Product data, related to the client on the second tab and Letter data related on the third?

 

[liddlem]

Hi All
I think that Gasman might be onto something here - I will need to investigate the practicalities of that.

For the sake of clarity (I hope that is what I might achieve) . . .
The_Doc_Man - You are 100% correct. This is in fact what I want to enforce.
So when a student is enrolled, he/ she is not allowed to be an orphan [from a DB perspective] (IE: MUST be linked to a parent/Guardian)
However - A person (community member,Donor, sponsor, suppier, police officer etc.) can be created without having to be linked to a student.

And added to that, I want to ensure that the parent/guardian/community member has an address. (So: No homeless people!)

Gasman : Assuming that I used a tabbed form, how would I enforce that
- a parent is added to EACH student and that
- Each parent is linked to an address

 

[Gasman]

Hi All
I think that Gasman might be onto something here - I will need to investigate the practicalities of that.

For the sake of clarity (I hope that is what I might achieve) . . .
The_Doc_Man - You are 100% correct. This is in fact what I want to enforce.
So when a student is enrolled, he/ she is not allowed to be an orphan [from a DB perspective] (IE: MUST be linked to a parent/Guardian)
However - A person (community member,Donor, sponsor, suppier, police officer etc.) can be created without having to be linked to a student.

And added to that, I want to ensure that the parent/guardian/community member has an address. (So: No homeless people!)

Gasman : Assuming that I used a tabbed form, how would I enforce that
- a parent is added to EACH student and that
- Each parent is linked to an address

AFAIAA you are just using the Tab controls for the same forms you use singly,, so whatever rules you have now will still be in effect?

If I am wrong about this, forum members, please correct me.

 

[liddlem]

Hi All
Just a thanks to all who took the time to read my long post and submit your ideas.
I think that I have found a completely different solution.
For those who are interested . . . this is the solution.
1. On the Enrolment screen (which contains the 3 sub forms) the user clicks button to add a student. This opens the 'Add Student' form which the user completes. Upon clicking a 'save' button (on the 'Add Student' form), the student ID is added to the Enrolment form. (This process occurs for each student that is on the application)
2. The user then adds a community member (on the 'Cmty_SubForm') where the afterUpdate event fires
A. an insert query that assigns a link between the community member that has just been added and ALL the students listed in step 1.
B. Using a Dcount, I also checks that there is a home address for the community members. If none exists, then open the appropriate form to link the member to and address.

Whew. What a mission

Thanks again to all who offered suggestions