DoCmd OpenForm - filter

[HGCanada]

My database has a number of reports, and I've programmed it such that people can click on the last name of a patient on any of the reports, to open that patient's record on the form. However, the form is then filtered such that they cannot search for other patients, or move to other records (unless they click on the button at the bottom to ulfilter).

How do I change the code on the report, such that the command opens the form to the correct patient, but then allows the user to navigate to or search for other records, without having to manually unfilter once in the form?

Here's my current code. Thanks in advance!

Private Sub FirstName_Click()
DoCmd.OpenForm "PatientsMainForm", WhereCondition:="[PtCaseID]=" & Me!PtCaseID
End Sub

 

[mike60smart]

Hi

I would recommend that you close the Report before Opening the Gorm

Something like this:-

Private Sub FirstName_Click()
DoCmd.Close , ""
DoCmd.Close acReport, "NameOfReportToClose"


DoCmd.OpenForm "PatientsMainForm", WhereCondition:="[PtCaseID]=" & Me!PtCaseID
End Sub

 

[arnelgp]

use the OpenArgs parameter of the DoCmd.OpenForm:

DoCmd.OpenForm FormName:="PatientsMainForm", OpenArgs:=Me!PtCaseID

next catch the passed parameter on the Load Event of PatientsMainForm Form:

Private Sub Form_Load()
    If Me.OpenArgs <> "" Then
        With Me.Recordset
            .FindFirst "PtCaseID=" & Me.OpenArgs
        End With
    End If
End Sub

 

[HGCanada]

Thanks so much, Mike. I'm curious, though - why is it best to close the report before opening the form?

At this time, the database has many records with incomplete data, so it will actually be really helpful for users to toggle between the report and the form. The report will show them which records are incomplete, and they can then just jump to the form to update the record. Then they can just refresh the report when they're done, and view the revised info.

 

[mike60smart]

Hi

Usually Reports are the finished article.

Why Toggle between a Report and a Form??

 

[HGCanada]

Thanks Mike. The report is currently being used to help identify which records are not updated. So, it helps to just update the records as users scan through the reports.

 

[mike60smart]

Hi

I would tend to use 2 Forms rather than a Form & a Report

arnelgp answer will give you what you need though

 

[HGCanada]

Thank you arnelgp! This works, if the form is not already open. However, if the form is already open, and the user clicks on a name on the report, then the command navigates the user to the form, but it doesn't refresh to the record that was just selected from the report. It still displays the last record that was viewed on the form. Is there a way to update this?

 

[arnelgp]

Move the code to the firm's Activate event

 

[HGCanada]

Thanks arnelgp. I tried it. I don't see any difference though - if the form is already open, and I navigate from the record, the form still only opens to the previous record I was viewing. Any other commands? I also tied on open, and that didn't work.


Private Sub Form_Activate() ' when user clicks on a name on a report, the form opens up to that client, and then the user can navigate to other records
If Me.OpenArgs <> "" Then
With Me.Recordset
.FindFirst "PtCaseID=" & Me.OpenArgs
End With
End If
End Sub

 

[arnelgp]

if it doesn't work, add extra code
to the way you open the form.
add Tempvars:

    If IsNull(TempVars("tvarPTCaseID")) Then
        TempVars.Add "tvarPTCaseID", 0
    End If
    TempVars("tvarPTCaseID").Value = Me!PTCaseID
    DoCmd.OpenForm FormName:="PatientsMainForm"

next modify the Form's Activate Event:

Private Sub Form_Activate()
    If Not IsNull(TempVars("tvarPTCaseID")) Then
        With Me.Recordset
            .FindFirst "ID=" & TempVars("tvarPTCaseID").Value
        End With
    End If

End Sub

you don't want Temvars tvarPTCaseID hanging around for
the rest of the program. Remove it when the Report
Closes:

Private Sub Report_Close()
	Tempvars.Remove "tvarPTCaseID"
End Sub

 

[HGCanada]

Thanks arnelgp. I will try that later.

What event does the tempvars code go under?

 

[arnelgp]

It's the code where you open the Form, see the code.