Solved Use a subform to change data on the 'one' side of one-to-many relationship? (1 Viewer)

Local time
Today, 12:23
Joined
Sep 23, 2021
Messages
32
Hi all, quick brief: three tables with respective forms, Customers, Case, Events. One customer can have many cases, one case can have many events.

My issue is how to reflect cases as closed once the last event stage is entered. I had a wonderfully functional update query which was running perfectly, but I'm not able to run this at work due to IT settings in Microsoft Trust Centre which I cant change.

In the meantime, I have tried to set up a subform in the Events form that expands out on clicking 'Update case status'. The problem is that this is trying to change the tblCase.CaseID (autonumber PK). I dont want it to change any data other than tblCase.Casestatus_status ("open" or "close"), tblCase.Casestatus_closedate (date) and tblCase.Casestatus_closereason ("completed request", "unable to assist", "declined-agency" and "declined-customer").

I would also be happy to open another form, if I can call the CaseID from the previous form window and link it automatically?

Any suggestions would be great, I'm a smidge past my understanding here!
 
Local time
Today, 12:23
Joined
Sep 23, 2021
Messages
32
there is something wrong, tblCase wouldn't be involved in this since you are on another level of table, Events.
The values that I was updating through the Update Query are in tblCase. Hence my annoyance that I can't run that at work, because it was perfect!
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:23
Joined
May 7, 2009
Messages
19,169
use the Case Form/subform and set the status there manually.
 
Local time
Today, 12:23
Joined
Sep 23, 2021
Messages
32
The subform I added was back to the case form, but it wasn't linking the Event FK to the Case PK, and as such is creating new records instead of updating existing. Any way to link them or is it just a select-and-search via combobox option.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:23
Joined
May 7, 2009
Messages
19,169
it's hard to imagine on my part.
each Case has many events (am i right?)
so just changing the status on the higher level, in your case This particular Case,
will invalidate all of its events, since we are closing the case, so events are now
irrelevant, since Case Closed? or do you need another event signifying that it is closing?
sorry got lost.
 
Local time
Today, 12:23
Joined
Sep 23, 2021
Messages
32
Correct, but those events are linear, so each case can only have one *chain* of events attached to it. Each event has outcome of 'active at end of event' (can be many) or 'closed at end of event' (singular, end of event chain). Once singular 'closed at end of event' occurs, no further action is needed by staff, so case status needs to be changed to 'closed' so this isn't included in any operational reports about current workloads/focus etc.

So, lower level 'closed at event end' = higher level 'closed'.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:23
Joined
May 7, 2009
Messages
19,169
on the Event subform, BeforeUpdate Event:


Code:
Private Sub Form_BeforeUpdate()
If Me.EventStatus ="Closed" Then
   Currentdb.Execute "Update tblCase Set [CaseStatus_status]='Close', [CaseStatus_Date]=Date(), [Casestatus_closereason] = '" & Me!EventReason] " & _
   "Where [CaseID] = " & Me!CaseID & ";"
End If
End Sub
 
Local time
Today, 12:23
Joined
Sep 23, 2021
Messages
32
Hi Arnel, sorry for the delayed reply! I couldn't get this code to work as it kept throwing up errors. I ended up using a bound popup form to allow users to manually close a case, which is working a treat. I really appreciate the time you took to reply, and am learning a lot from your responses!
 

Users who are viewing this thread

Top Bottom