After Update with Many to Many Relationship - VBA or Query or Both?

[alpinegroove]

I posted this in a couple other places but haven't been able to resolve it.
I am not sure if I need an update query, VBA code, or a combination of both.

I use a form (linked to tblCourses) and a subform (linked to the junction table tblCoursePeopleRoles).

I am trying to update the field CourseHasTA2 in tblCourses when fkRoleID in the junction table equals 3. If fkRoleID=3, update CourseHasTA2 to "-1," else update it to "0."

I came up with the following After Update code for the combo box used to select the role in the subform / junction table:

DoCmd.RunSQL "UPDATE tblCoursePeopleRoles LEFT JOIN tblCourses ON tblCoursePeopleRoles.CourseIDFK = tblCourses.CourseID SET tblCourses.CourseHasTA2 = IIf([tblCoursePeopleRoles].[RoleIDFK]=3,-1,0)"

But this has one major limitation: if Role 3 is selected last (after all the other roles had been designated), everything is fine. But since different people can have different roles in the same course, if a different role is selected in the form after Role 3, the flag reverts back to "0."

So this method doesn't achieve the goal if Role 3 is not the last one to be assigned, so I am thinking that either the code needs to be changed or that perhaps this should be some kind of query that looks at the junction table, but I am running into the same problem: The query first changes the flag to "-1" when it finds Role 3 but then when it continues to work, it also find a different role for the same course, and changes the flag back to "0."

Does anyone have any suggestions for how to handle this?

Sorry for the long explanation. I hope this makes sense. Thank you.

 

[Rank Am]

This sounds to be more of a table design issue, probably need to have a look at normalising the tables - you shouldn't normally be updating data with VBA, SQL, or even a magic wand on the 1 side of a relationship from a value selected on a form bound to the many side. I'm not sure what the purpose of CourseHasTA2 is but it doesn't appear to belong in tblCourses if its affected by roles - that's what the junction table is for.
If you are looking for courses where there is a value for FkRoleID = 3 why not just run an aggregate query against the junction table "SELECT First ([CourseId]) AS Coursewith3, ([FkRoleId]) From tblCoursePeopleRoles Where (FkRoleID = 3);" or something along those lines. Syntax may be incorrect - apologies
There are times when it is useful to break the normalising rules and store aggregate data on the one side - on one of my db's I do it to store the sum of all job task costs aginst the job as there can be hundreds of tasks against a job in a table with tens of thousands of records so it makes sense. Even so you should run the UPDATE Query in the forms AfterUpdate event based upon the aggregate query (either global or just for the CourseID) and not each individual record in the junction table

 

[alpinegroove]

Thank you for your answer, Rank Am. It is helpful, and I will try what you suggested.
The root of the problem is definitely normalization. Let me try to explain:

The database is used to store instructional staff and course information, and the data is used to generate contracts. Instructional staff can have different roles in a course: Instructor (teaches the lecture), Teaching Assistant (teaches labs or discussion sections), and/or Reader (corrects homework and exams).

Some courses have labs or discussion sections, and some do not.
Sometimes the same person who teaches the lecture (as Instructor) also teaches the labs or discussion sections (as "Teaching Assistant") in the same course.
Other times, the lecture is taught by one person, and the labs or discussion sections by another.
A person can also be associated with more than one course and have different roles in each course. This amounts to the many-to-many relationship.

This is my current table structure:
tblPeople
tblRoles
tblCourses
All of these have a foreign key in the junction table:
tblCoursePeopleRoles

tblCourses includes the meeting times for the lectures and their associated labs / discussion sections.

Here is the problem: the current structure does not allow associating Teaching Assistants with specific labs or discussion sections. This in 99% of the cases is not required, because the TA simply teaches all of the discussion sections in the course.

However, I rarely have a case where the Teaching Assistant cannot teach all of the labs or discussion sections and a second TA is hired for the same course.

Since the contracts need to list the labs or discussion section taught by the person listed in the contract, there is now a problem because the database doesn't know who teaches which section.

Another layer of normalization would be required, and together with the forms/subform/subsubform structure, building and maintaining such a system would be quite difficult for people with our skill level.

So the workaround we came up with is to add a fourth role (TA2) and to add fields to tblCourses that indicate who teaches each lab or discussion section, TA or TA2. True, that violates normalization rules, but it does work.

The code that generates the contracts uses logic to determine how to draft the contracts. The user needs to remember to indicate using a combo box whether TA or TA2 teaches a given lab or discussion section.

The goal of the procedure mentioned above was to flag when there is a TA2 in the course. If there is one, the database knows to perform certain contract related procedures. I would also want the fields that indicate who teaches which lab or discussion section to be required if there is a TA2 in the course, but that's another issue.

I hope this helps explain the problem and why I have it.
Does the aggregate query still sounds like a good direction?
Thanks

 

[Rank Am]

As long as the the TA2 is assigned in tblCoursePeopleRoles and the subform is only returning records for the course selected in the parent form (doesn't have the word filtered in the navigation buttons) A really crude but easy way to set the value would be to place a checkbox on the parent form (bound to tblCourses.Coursehasta2)
In the sub forms afterupdate event add the following:

Dim rs as dao.recordset
Me.requery
set rs = me.recordsetclone
rs.movefirst
do while rs.eof = false
if rs!("fkRoleID") = 3 Then
me.parent.Coursehasta2 = -1
End If
rs.movenext
loop
rs.close
set rs = nothing

 

[alpinegroove]

Thank you for your reply.

I have a form and subform. The form is linked to tblCourses and the subform to the junction table, with CourseID (tblCourses) as the Link Master and fkCourseID (junction table) as the Link Child.

Roles, including TA2, are assigned using a combo box in the subform. I already have a check box on the parent form that works with the code I posted originally. My only issue with that code is that it relied on the user assigning the role TA2 last.

I am pretty new to VBA. How the code you posted avoid the issue I mention above about the TA2 needing to be assigned last?

Thanks again!

 

[alpinegroove]

I get the following compile error: "Type-declaration character does not match declared data type."

"rs!" is highlighted in the code.

Is there supposed to be a space between rs! and ("fkRoleID")? The VBA editor in Access 2010 deletes the space even if I try to add it.

Dim rs as dao.recordset
Me.requery
set rs = me.recordsetclone
rs.movefirst
do while rs.eof = false
if [COLOR=Red]rs![/COLOR] ("fkRoleID") = 3 Then
me.parent.Coursehasta2 = -1
End If
rs.movenext
loop
rs.close
set rs = nothing

 

[alpinegroove]

I changed ("fkRoleID") to [fkRoleID] but now I get a runtime error 3625: Item not found in this collection.

 

[alpinegroove]

I had to make a couple of minor changes (including a typo in the field name), but now it's working, partially.
It changes the field in the parent form to Yes, but if I delete the record, it doesn't change it back to No. I tried adding an Else clause, but it didn't help:

Private Sub Form_AfterUpdate()

Dim rs As dao.Recordset

Me.Requery
Set rs = Me.RecordsetClone
rs.MoveFirst

Do While rs.EOF = False
If rs![RoleIDFK] = 3 Then
    Me.Parent.CourseHasTA2 = "Yes"
Else
    Me.Parent.CourseHasTA2 = "No"
End If
rs.MoveNext
Loop

rs.Close
Set rs = Nothing

End Sub

Any ideas?

 

[alpinegroove]

Sorry for the monolog...

This is now working:

Dim rs As dao.Recordset

Me.Requery
Set rs = Me.RecordsetClone
rs.MoveFirst

Do While rs.EOF = False
If rs![RoleIDFK] = 3 Then
    Me.Parent.CourseHasTA2 = "Yes"
Else
    Me.Parent.CourseHasTA2 = "No"
End If
rs.MoveNext
Loop

rs.Close
Set rs = Nothing

I have also added it to the VBA code of the Delete Record button, so now it reverts back to "No" if the TA2 record is deleted:

Private Sub DeleteRole_Click()

On Error GoTo Err_DeleteRole_Click

    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
    
Exit_DeleteRole_Click:


Dim rs As dao.Recordset

Me.Requery
Set rs = Me.RecordsetClone
rs.MoveFirst

Do While rs.EOF = False
If rs![RoleIDFK] = 3 Then
    Me.Parent.CourseHasTA2 = "Yes"
Else
    Me.Parent.CourseHasTA2 = "No"
End If
rs.MoveNext
Loop

rs.Close
Set rs = Nothing

Exit Sub

Err_DeleteRole_Click:
    MsgBox Err.Description
    Resume Exit_DeleteRole_Click
    

End Sub

Thanks a lot for your help. I have been working on this for days. I have copied/pasted recordset code before, but creating it from scratch is still beyond me and clearly that was necessary here.

 

[Rank Am]

Glad you got it working - apologies, getting Syntax right first off is not my strongest attribute
However I can see one potential pitfall. If the TA2 is not the last entry the form it will still revert to no on the main form. A simple work around would be to declare an extra variable and update this when looping through the recordset and only if it finds a true value- only update the main form after looping through the recordset. As follows:

Dim rs As dao.Recordset
Dim boolTA2 as boolean
boolTA2 = 0
Me.Requery

Set rs = Me.RecordsetClone
rs.MoveFirst
Do While rs.EOF = False
If rs![RoleIDFK] = 3 Then
   boolTA2 = -1
End If
rs.MoveNext
Loop

rs.Close
Set rs = Nothing

If boolTA2 = -1 Then
Me.Parent.CourseHasTA2 = "Yes"
Else
Me.Parent.CourseHasTA2 = "No"
End if

If this is pasted in the subforms after update event no further code will be need to handle deletion as this event is triggered after data is changed and the underlying table updated - that includes adding / editing and deleting.

As I said before this is a rough and ready fix and not recommended on large recordsets (looping is pretty slow and using SQL Aggregate or DISTINCT keywords is better functions

 

[alpinegroove]

However I can see one potential pitfall. If the TA2 is not the last entry the form it will still revert to no on the main form.

Actually, the original code works even if TA2 is not the last entry.
I added TA2 first and updated the form - flag switched to Yes.
I added TA second and updated the form - flag remains Yes (as it should).
I deleted TA2 and updated the form - flag switches to No.

So it seems to be working. Why did you think it should work?

As I said before this is a rough and ready fix and not recommended on large recordsets (looping is pretty slow and using SQL Aggregate or DISTINCT keywords is better functions

The recordset is pretty small, so speed isn't an issue, but I am interested to learn how aggregate can be used to achieve the same result as the VBA code.

Thanks again.

 

[alpinegroove]

I tried the new code and removed my original event from the Delete Button.
Now when I delete a TA2 record, the flag remains Yes. Something doesn't work.

 

[Rank Am]

Is the code in the subform afterupdate event or the afterupdate event on a control in the subform. If it's in the subform it should work - unless your delete button is running SQL or something and deleting it directly from the control source (table or query) rather than the form - add the code to the delete button if so after the code to delete the record.
I thought the original would fail because it updates the mainform on each loop so if the recordset rows was RoleIDFK was 1,3,2 for example the overall value will be false as the last value would not be 3.
Using an SQL Aggregate query or DISTINCT keyword would negate the need to add / update data on the one side of the join because the query would be run against the junction table the form or report etc to show contracts which courses have a TA2 would have this query as it's record source. You can read about SQL Aggregate functions here
http://msdn.microsoft.com/en-us/library/bb177686(v=office.12).aspx

Access VBA has some built in aggregate functionality called Domain Aggregate functions that do the same, such as DLookup, DCount, DSum etc but I don't use them. Firstly they are much slower to work than SQL as the db gets more records. I find they are usually only required as a hack to get round poor database design - rather than one query that is used by all forms reports etc that require that information you may find 6 hardcoded Domain aggregates in 6 separate modules all of which need updating, widespread use generally makes a database impossible to maintain and you are best off starting again unless you like reading code - others may disagree

 

[alpinegroove]

Is the code in the subform afterupdate event or the afterupdate event on a control in the subform. If it's in the subform it should work - unless your delete button is running SQL or something and deleting it directly from the control source (table or query) rather than the form - add the code to the delete button if so after the code to delete the record.

This is the Delete Record code. I created this using the wizard in Access:

Private Sub DeleteRole_Click()

On Error GoTo Err_DeleteRole_Click

    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
    
Exit_DeleteRole_Click:
Exit Sub

Err_DeleteRole_Click:
    MsgBox Err.Description
    Resume Exit_DeleteRole_Click
    
End Sub

So I think it does delete the record from the junction table.

I thought the original would fail because it updates the mainform on each loop so if the recordset rows was RoleIDFK was 1,3,2 for example the overall value will be false as the last value would not be 3.

This is the problem I was having when I first tried to address this using either VBA or an update query. But I have tested the original code you provided multiple times and in different scenarios and it does seem to work correctly.

Using an SQL Aggregate query or DISTINCT keyword would negate the need to add / update data on the one side of the join because the query would be run against the junction table the form or report etc to show contracts which courses have a TA2 would have this query as it's record source.

This is interesting, thanks. Thanks again for all the help. I understand that this is an inelegant way to deal with the situation, but I until I add some normalization layers to the database, this will do the trick.