RecordSet Criteria to fill field in form

[amkp711]

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...

[June7]

Is [DB ID] a number type field?

Try:

rcdNextSession.FindFirst "Session_Date >= Date() And [DB ID] = " & Parent![DB ID]

or instead of the FindFirst, open the recordset to the single desired record:

Set rcdNextSession = CurrentDb.OpenRecordset("SELECT [Next Session Date], [Next Session Time], [Next Session Location] FROM [Upcoming Sessions] WHERE Session_Date >= Date() And [DB ID] = " & Parent.[DB ID])

[amkp711]

Thanks so much for the fast reply! DB ID is a number field that is autonumbered and serves as the primary key for tbl_Participant Info. I tried putting in the sample code you gave to open the recordset as desired in the first place, and it came up with an error for that line of code:

Error 3061: Too few parameters. Expected 4.

Any idea what that means? Thanks!!!

[June7]

Is [Upcoming Sessions] a query object and does it have dynamic parameters?

I never use dynamic parameterized query objects.

[amkp711]

Is [Upcoming Sessions] a query object and does it have dynamic parameters?

I never use dynamic parameterized query objects.

I think it is just a query object. The only criteria on it is that the [session date] >= date ()

Thanks!!

[June7]

Did I use correct field names in my suggestion?

[Upcoming Sessions] is a query that includes only tables no queries?

[amkp711]

Thank you so much June7. I seem to have gotten it working as long as there is a "next session" for the recordset to find. Issue now seems to be that the .NoMatch if then is not working. Whenever I run the code and there is no upcoming session for the specified DB ID, I get error 3021: "No current record". The code as it stands now is as follows:

Private Sub Form_Load()


Dim Care_Coach_Database As DAO.Database
Dim rcdNextSession As DAO.Recordset


Set Care_Coach_Database = CurrentDb
Set rcdNextSession = CurrentDb.OpenRecordset("SELECT [Session Date], [Session Time], [Location], [Type of Session], [Call To Confirm] FROM [Upcoming Sessions] WHERE [Session Date] >= Date() And [DB ID] = " & Parent.[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


End Sub

[amkp711]

Nevermind! I seem to have fixed my problem with an empty recordset. I added a .FindFirst line after setting the recordset and it seems to work! Not sure if there is a better way to do this, so let me know if there is. But, for now, thank you so much for your help!

[June7]

Since you are opening a filtered recordset, the FindFirst and NoMatch should not be needed.

If Not rcdNextSession.EOF Then

Might find this of interest http://allenbrowne.com/ser-29.html