Passing data from one query to another (1 Viewer)

[RCheesley]

Hi all,

I've got a form (frmPatientDetailsWithTabs) which is used to display a patient's record. This is based on a query called qrypatientrecord, and on this form is a command button cmdViewReport.

When this button is pressed, a form opens (frmViewReport) and on this form is a dropdown box which uses a query to display all reports for that patient - well this is the intended use. When selected the user then clicks on a button to preview the report.

I use the form in another location where _all_ reports are viewed in the dropdown box (this works fine), with the following columns:

Patient ID Surname First Name Report ID

and the query for this is as follows:

SELECT qryPtdetailsRCAforlistbox.PatientDetailsID, qryPtdetailsRCAforlistbox.PatientDetailsSurname, qryPtdetailsRCAforlistbox.PatientDetailsFirstName, qryPtdetailsRCAforlistbox.RCAID 

FROM qryPtdetailsRCAforlistbox 

WHERE (((qryPtdetailsRCAforlistbox.PatientDetailsID)=[txtPatientDetailsID])) 

ORDER BY qryPtdetailsRCAforlistbox.PatientDetailsSurname;

I have copied the form and renamed it so that I can make modifications to it without affecting the original form (which works fine).

If I want to use this form to display in the dropdown box only the reports for the patient currently active on the original form (frmPatientDetailsWithTabs), how would I do this?

The VBA associated with cmdViewReport is as follows (not sure if it's relevant or not)

Private Sub cmdViewReport_Click()
On Error GoTo Err_cmdViewReport_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmViewReport"
       
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdViewRCA_Click:
    Exit Sub

Err_cmdViewRCA_Click:
    MsgBox Err.Description
    Resume Exit_cmdViewRCA_Click
    
End Sub

I have tried the following:

• Using LinkCriteria to set PatientDetailsID in the qryPtdetailsRCAforlistbox to the value displayed on frmPatientDetailsWithTabs
• Using the criteria on the query qryPtdetailsRCAforlistbox to try to pick up the data from the form
• Google search & forums (but not sure what to search for really!)

I think perhaps I have the syntax wrong or I am totally barking up the wrong tree :|

Ruth

 

[Uncle Gizmo]

It looks to me what you’ve got so far is just right.

For clarity:
You are sending the patient ID through to the form within the “LinkCriteria”, and getting it to appear successfully on the opening form in the text box “txtPatientDetailsID”?

I would suggest you then need to refresh the combobox,

Me.cboMyCombo.Refresh
Me.cboMyCombo.Dropdown

 

[RCheesley]

Hiya,

It's not actually working - if I put something in the link criteria of the combo button it says it can't find the form (I checked that I hadn't made a typo).

If I remove the link criteria it opens the form with the combo box showing all the reports in the drop down.

Ruth