Hello! I am currently building a database for Care Coaches to manage appointment scheduling with their clients. I currently have a "tbl_Participant Info" which has all the main client-specific information (each client is their own record). In addition, there is a linked table ("tbl_Session Tracking") which contains all of the sessions (past and present) for all clients. It is linked to the Participant Info table by "DB ID". In the forms, I have one (frm_Client Info) which shows detailed information by client. The user would get to this via (frm_client List) which shows a summary for all clients. In the Client Info form, I have queries generating subforms which split the session table between Upcoming and Past sessions. Now, I am attempting to have the database recognize each client's "Next Session" from the Upcoming Sessions and to autofill the date, time, and location of that session record into the appropriate fields on the form ("Next Session Date", "Next Session Time", and "Next Session Location"). I was able to set this up (just as the next session listed for anyone), but I cannot seem to make this specific to the client who's information the form ("frm_Client Info") is showing. When I added that criteria into the recordset code (shown below), it just came up with no match for any client. This code is currently in both the Form Load and After Update events of the Upcoming Sessions subform:
'Call next session record
Dim Care_Coach_Database As DAO.Database
Dim rcdNextSession As DAO.Recordset
Set Care_Coach_Database = CurrentDb
Set rcdNextSession = Care_Coach_Database.OpenRecordset("Upcoming Sessions")
rcdNextSession.FindFirst ("Session_Date" >= Date And "DB ID" = [Forms]![frm_Client Info]![DB ID])
'Fill alert field
If rcdNextSession.NoMatch = True Then
[Form_frm_Client Info].Next_Session_Alert = "This patient has no upcoming sessions scheduled"
ElseIf rcdNextSession("Type of Session") = "In Person" Then
[Form_frm_Client Info].Next_Session_Alert = "Next Session: " & rcdNextSession("Session Date") & " At " & rcdNextSession("Session Time")
[Form_frm_Client Info].Call_to_Confirm_Alert = "Call client to confirm on: " & rcdNextSession("Call To Confirm")
Else
[Form_frm_Client Info].Next_Session_Alert = "Next Session: " & rcdNextSession("Session Date") & " At " & rcdNextSession("Session Time")
[Form_frm_Client Info].Call_to_Confirm_Alert = "Session will be over the phone"
End If
rcdNextSession.Close
Care_Coach_Database.Close
I am really lost and I am not sure if this is even the best way to do this. I am not sure if I am making any sense... Please let me know if I can clarify...Thank you so much! I am well past the limit of my Access understanding right now...