Selecting a specific record on a tabbed subform

[dgmdvm]

I have a form with multiple subforms arranged as tabs. The main form is named Client_Intake_form, and contains a last name field for the head of the household as well as address information. The subform on a tab is called persons_subform. This tab contains information about individuals such as first and last name, age and other demographics. In the data it is common for the head of household last name to be different from the individuals that make up "the family" so the primary form is opened to head of household. The persons_subform may have multiple records on the tab and arrow keys are present to allow you to scroll through the family members.

I have a Name_search_form that I use to allow the user to search for persons in the database. It works fine to locate the correct family and populate the subform with the persons that make up the family but the person that shows up in the subform is the one with the lowest primary key ID rather than family member selected. I would like the search form to locate the correct family (on the main form) AND open the correct family member on the subform. The table relationship is as follows (disregard the extraneous relationship lines I have tried to hide; it's a one to many relationship of CLID to CL_ID):




My original idea was so do the primary search in the Name_search_Form on the primary key for the family name- 'CLID' and pass the primary key for the individual selected-'ID' as an openarg. If I set the WHERE statement to a fixed ID number, it works but limits the subform to that particular individual (you can't use the arrow keys to move to other family members).

Now I have tried to use a recordset to find the appropriate person but my code isn't working here either. I'm out of ideas.



This code works fine- it selects the appropriate family name and passes the individual's ID number as an openarg.

Private Sub CmboSearch_AfterUpdate()
' code from the name search form that grabs the CLID and ID from a combobox

 Dim rs As Object
   
On Error GoTo Err_CmboSearch_AfterUpdate


DoCmd.OpenForm "Client_intake_form", acNormal, , "ClID=" & Me.CmboSearch.Column(0), acFormEdit, acWindowNormal, "ID=" & Me.CmboSearch.Column(1)
Forms!Client_intake_Form.SetFocus

Exit_CmboSearch_AfterUpdate:
    Exit Sub


Err_CmboSearch_AfterUpdate:
    Msgbox Err.Description
    Resume Exit_CmboSearch_AfterUpdate
        
End Sub

Code to find the correct person- this doesn't work

Private Sub loadtab()

'delays loading of information until the user selects the tab
Dim sql As String
Dim sqlOpen As String
Dim rs  As Object
Dim db As Database


Set db = CurrentDb


Select Case Me.DetailsTabs.Value
Case Is = Me.DetailsTabs.Pages("persons_Page").PageIndex
 
 
 sqlOpen = "SELECT tblClients.ClID, tblPersons.Cl_ID, tblpersons.ID, tblPersons.Cl_Lastname, tblPersons.Cl_FirstName, tblPersons.HeadHousehold, " & _
 "tblPersons.Cl_Gender, tblPersons.Cl_DOB, tblPersons.Cl_AgeType, tblPersons.Grdn_ID, tblPersons.Cl_Veteran, tblPersons.Ethnic_ID, tblPersons.Race_ID, " & _
 "tblPersons.Cl_Employer, tblPersons.Cl_EmploymentStatus, tblPersons.Inactive, tblPersons.Cl_incarcerated, tblPersons.Cl_incarcerationType " & _
 "FROM tblClients INNER JOIN tblPersons ON tblClients.ClID = tblPersons.Cl_ID;"


  Me.Persons_subform.Form.RecordSource = sqlOpen


 Set rs = db.OpenRecordset(sqlOpen)
  
  If Not rs.BOF And rs.EOF Then
    rs.FindFirst "[ID] = " & Me.OpenArgs
    rs.Bookmark = Me.Bookmark
   
  End If


 Me.Persons_subform.Form.Requery

..there are more case options here



End Select

End Sub

Thanks in advance for looking at this.

[ssanfu]

I'm not following....well, I'm totally confused.
Why do you have code to search? And I don't understand what you mean by "... open a sub form"..

It would really help to see your dB. Only need a few "Client" records with a few related "Persons" record.



In the mean time, take a look at this example. Maybe it will help a little. Just your basic main form/sub form arraignment.

[dgmdvm]

I'm not following....well, I'm totally confused.
Why do you have code to search? And I don't understand what you mean by "... open a sub form"..

It would really help to see your dB. Only need a few "Client" records with a few related "Persons" record.

Sorry I couldn't explain it better. It always amazes me that you can provide such good advice just looking at the code! This hopefully will make more sense now.

I have attached a stripped down version of my database. The main form will operate but I deleted anything that didn't pertain to my issue. The database is split so I sent both the backend and front end. NPFE.accdb is the front end. You will want to hold the shift key down to open it. If you go to the 'name_search_form' and check the drop down box, you will see 2 families. Select any of the people and you will note that when the main form opens the main tabbed subform (persons_subform) always shows the member of the family with the lowest ID number. I would like the persons_subform to show the family member selected, even if it isn't the lowest ID number of the family. For example, if you select BamBam Rubble, the persons_subform opens with Fred Flintstone's info displayed. You have to use the arrow keys in the subform to find BamBam.


db.zip

I do appreciate you looking at this for me. Its not critical to the function of the database but it will cause less confusion for a user if I can get this right.

[June7]

Note to others: tables File_Settings and Printers not included in the backend.

Why do I get error message "Distributed_Furniture_subform is misspelled …"?

Suggest you name foreign key ID field something more informative - such as the ID field in tblPersonalInfo.

The combobox RowSource has the same ClID foreign key for Flintstone and Rubble in tblPersons. In tblClients ClID has name Duck instead of Rubble. Fix data in tblClients and tblPersons.

Suggest don't have separate tables for Clients and Persons. Clients are persons.

Can simplify references to combobox the OpenForm code:

DoCmd.OpenForm "Client_intake_form", acNormal, , "ClID=" & Me.CmboSearch, acFormEdit, acWindowNormal, Me.CmboSearch

[dgmdvm]

Note to others: tables File_Settings and Printers not included in the backend.

Sorry about that but those tables have nothing to do with my question. I deleted tablets that weren't related to the question but missed those 2 in the front end.

Why do I get error message "Distributed_Furniture_subform is misspelled …"?

The form was deleted for this database copy; has nothing to do with my question.

Suggest you name foreign key ID field something more informative - such as the ID field in tblPersonalInfo.

Noted.

The combobox RowSource has the same ClID foreign key for Flintstone and Rubble in tblPersons. In tblClients ClID has name Duck instead of Rubble. Fix data in tblClients and tblPersons.

Suggest don't have separate tables for Clients and Persons. Clients are persons.

As I stated in my initial post, many of the families in the database are made up of persons with multiple last names. This was not an error. The organization I am creating this database for does treat clients and persons differently. That is why the database is set up this way.

Can simplify references to combobox the OpenForm code:

DoCmd.OpenForm "Client_intake_form", acNormal, , "ClID=" & Me.CmboSearch, acFormEdit, acWindowNormal, Me.CmboSearch

Noted

[June7]

Okay. So are Flintstone and Rubble supposed to have the same ClID foreign key in tblPersons? Rubble is not supposed to be in tblClient?

You want main form open to Flintstone but subform move to Rubble record? And that's what the OpenArgs data is used for? Go back to referencing column index.

DoCmd.OpenForm "Client_intake_form", acNormal, , "ClID=" & Me.CmboSearch, acFormEdit, acWindowNormal, Me.CmboSearch.Column(3)

What code have you attempted for using OpenArgs?
Maybe just add WHERE clause to the SQL you build in code:

"WHERE tblPersons.Cl_LastName = '" & Me.OpenArgs & "';"

[dgmdvm]

Okay. So are Flintstone and Rubble supposed to have the same ClID foreign key in tblPersons? Rubble is not supposed to be in tblClient?

Exactly. BamBam Rubble is a child that lives with the Flintstones. I recently imported all the actual data for this organization from a huge excel spreadsheet. It had 4000 lines and 40 columns covering the past 18 months of activity. I was able to distill it down to so we have just under 600 families with 1700 family members and 1650 services provided during that time. Its not my place to judge but there were multiple instances of children and spouses changing families over that time period. It was an eye-opener! That is likely why they set this all up with clients and family names separate.

You want main form open to Flintstone but subform move to Rubble record? And that's what the OpenArgs data is used for?

Correct.

Go back to referencing column index.

DoCmd.OpenForm "Client_intake_form", acNormal, , "ClID=" & Me.CmboSearch, acFormEdit, acWindowNormal, Me.CmboSearch.Column(3)

What code have you attempted for using OpenArgs?

Private Sub loadtab()'delays loading of information until the user selects the tab
Dim sql As String
Dim sqlOpen As String
Dim rs  As Object
Dim db As Database
Dim varbookmark As Variant


Set db = CurrentDb


Select Case Me.DetailsTabs.Value
Case Is = Me.DetailsTabs.Pages("persons_Page").PageIndex
 
 'syntax error on next line
 'sql = "SELECT tblPersons.* FROM tblPersons; "
 sqlOpen = "SELECT tblClients.ClID, tblPersons.Cl_ID, tblpersons.ID, tblPersons.Cl_Lastname, tblPersons.Cl_FirstName, tblPersons.HeadHousehold, " & _
 "tblPersons.Cl_Gender, tblPersons.Cl_DOB, tblPersons.Cl_AgeType, tblPersons.Grdn_ID, tblPersons.Cl_Veteran, tblPersons.Ethnic_ID, tblPersons.Race_ID, " & _
 "tblPersons.Cl_Employer, tblPersons.Cl_EmploymentStatus, tblPersons.Inactive, tblPersons.Cl_incarcerated, tblPersons.Cl_incarcerationType " & _
 "FROM tblClients INNER JOIN tblPersons ON tblClients.ClID = tblPersons.Cl_ID;"


'Me.Persons_subform.Form.RecordSource = sqlOpen
Set rs = db.OpenRecordset(sqlOpen)
  
  If Not (rs.BOF And rs.EOF) Then
    rs.FindFirst "[Id]= " & Nz(Val(Me.OpenArgs), 0)
    
   varbookmark = rs.Bookmark
   Debug.Print varbookmark
  End If


   ...there are other case statements here.

End Select
End Sub

I have tried a variety of versions of the rs.findfirst line of code. The line listed above is just one of them.

I even tried just placing the [ID] number of BamBam in the line:

if not (rs.BOF and rs.EOF) then
  rs.findfirst "[ID]= 1740"
  varbookmark=rs.bookmark
End if

For some reason, the bookmark doesn't get set. When I place a break on the line varbookmark=rs.bookmark I find that varbookmark="empty". I don't have a good enough understanding of bookmarks to know why this is the case.

I also tried

rs=me.recordsetclone
if not (rs.BOF and rs.EOF) then
  rs.findfirst "[ID]= """ & Me.openargs & """"
  varbookmark=rs.bookmark
End if

In this instance I get an error that [ID] is not identified.

Maybe just add WHERE clause to the SQL you build in code:

"WHERE tblPersons.Cl_LastName = '" & Me.OpenArgs & "';"

When I do this the persons_subform is filtered to ONLY let you see BamBam's record. You can no longer use the arrow keys to move from family member to family member.

I think using recordsets is the way to solve this problem but I'm not sure where to go next.

[June7]

Following will find the particular person record in the subform. I always name subform container different from the object it holds, such as ctrPersons.

Private Sub Form_Load()
' loads the main tab whenever the form is open
loadtab
With Me.ctrPersons.Form
    .RecordsetClone.FindFirst "Cl_LastName = '" & Me.OpenArgs & "'"
    If Not .RecordsetClone.NoMatch Then .Bookmark = .RecordsetClone.Bookmark
End With
End Sub

[dgmdvm]

Excellent, thanks so much. I did modify this in one way however. In my sample database the last name of BamBam Rubble was unique but the field Cl_LastName will have multiple duplicates since some families will have the same last name. So I modified it to search based on the primary key of the persons table.

With Me.ctrpersons.form
  .RecordsetClone.FindFirst "ID =" & Val(Me.Openargs)  ' I changed the parameter passed as an openarg in the openform command to 'ID'  from 'CL_Last Name'
  If Not .RecordSetClone.noMatch then
      .Bookmark= .recordsetclone.bookmark
 End if
End With

All the late binding code in the select case statement is superfluous as well, at least for this tab, so it was removed.

I would love to get my hands on a good reference that describes bookmarks and recordsetclone in more detail. I have searched on line but the microsoft info. just doesn't seem to help me much.

Thanks again.