Solved Filtering Subform based off carried over data (1 Viewer)

[bonzitre]

Hello again everyone.
Today, my issue is I am trying to filter a subform based on the data I am carrying over from another form.

I have a form "PatientForm" that has a button "cmdViewCalls" with the following code:

Private Sub cmdViewCalls_Click()

Private Sub cmdViewCalls_Click()

Dim strFrmName As String
strFrmName = "CallLogView"

    DoCmd.OpenForm strFrmName
        With Forms(strFrmName)
            .MRNcall = Me.MRN
            .LastNamecall = Me.LastName
            .FirstNamecall = Me.FirstName
            .DOBcall = Me.DOB
            .PatientIDcall = Me.ID
        End With
End Sub

It opens form "CallLogForm" and fills in unbound boxes with info from the PatientForm form. I then use that data to create a header with those fields so the user will know they are looking at the correct person's data as below while the PatientIDCall box is the primary key for the patient which is hidden on the form.

The subform "PTCallSub" is based on a form PtCallLogF which is just a datasheet form of PtCallLogQ (query) which is a query of the CallLog Table.

What I am trying to do, is make it to where when the form/subform loads, it will filter the PtCallLogQ to display only records related to the patient that the primary key PatientIDCall hidden box has filled in. I can't for the life of me get anything to work.
Those unbound boxes are the boxes I referred to.

If you need more information, let me know. Thanks!

 

[theDBguy]

Hi. Welcome to AWF!

Try setting the Master/Child Linked Fields of the subform to use the hidden textbox as the master and the foreign key in the query as the child.

 

[CJ_London]

, is make it to where when the form/subform load

for a form, use the where parameter of the openform method

for a subform, use the linkchild/master properties of the subform control

 

[bonzitre]

Hi. Welcome to AWF!

Try setting the Master/Child Linked Fields of the subform to use the hidden textbox as the master and the foreign key in the query as the child.

Edit:
I just typed PatientIDCall as the parent and PatientID as Child and viola. Thank you guys! stupid simple which I assumed it would be.

OLD: When I attempt to do that, I don't have the option to select PatientIDCall (the box with the ID I want to use to filter) which is housed on the main form?

 

[MajP]

The main form and subform should be linked by the patients PK, forget about the unbound textbox.
Then you simply use docmd open form to the correct ID. The subform will filter automatically.

 

[Pat Hartman]

You seem to be using an unbound main form with a bound subform. This is an unusual design pattern which is forced probably because the DataSheet view hides all headers so you have no place to display the related information in a header. If you were to change the form to use continuous view, the subform would become a main form but would have to be rebuild to be laid out in columns but that might be logically easier for you to manage. At least you'd have a spot for the "header" info.

Given what you have, if you want to stick with DS view for the subform, I would do a couple of things.
1. change the subform to use a query that has selection criteria. The criteria should point to the PK field on the form where you are opening the call data from. So, something like:
Select ..
From ..
Order By ..
Where PatientIDCall = Forms!frmPatientInfo!PatientID
2. In the BeforeInsert event of the subform, you MUST get the correct FK value, so you also reference the calling form.
Me.PatientIDCall = Forms!frmPatientInfo!PatientID
3. NEVER - and this is important - dirty a record as you open a form. Therefore, remove the code you have that populates the FK and let the subform do it naturally as the user enters a new record. This method that you are using only populates the FK for the very first record. If the user enters a second record, the FK does not get populated. That is yet another reason to not use this method. Populating the unbound header controls this way is no problem because they do not dirty the record. When you use the BeforeInsert event to populate the FK as I suggested, you are not dirtying the record, the user has started typing and therefore HE is dirtying the record, you are merely making the record complete by adding the FK. I'm sure we'll see at least three alternative methods to do this. It doesn't matter if you choose an alternate. What matters is you don't dirty the record before the user does and that every record gets the correct FK, not just the first one.
4. And finally. I hate the practice that some newbies have adopted to name all PKs "ID". This simply causes confusion and makes it difficult when looking at tables what PK the FK is pointing to.

When you use a bound main form with a bound subform, no code is required. You just set the master/child links to the correct values and Access takes charge of keeping the child records in sync with the parent PK.