Select a specific report

[rclarke]

Hi folks,

I'm struggling to get my Access 2003 database to work the way I imagined it might. Whether this is a limitation of access or maybe I'm coming at it from the wrong angle, I'm not sure, I am hoping one of you may help.

I am collecting data about scientific protocols and have several tables containing all the data I require. I have created a query to bring some of this information together in one table and want to use this to create a printable report.

I want to print the specific report that is being viewed in the form from the query table. I have been able to filter to specific reports when creating a report directly from a single table, pointing to the primary key "[ID]". However, this doesn't seem to work when I point my form to the report where data is taken from the query table.

I get a popup asking to enter the parameter value, can't quite understand why it works fine for one report, but not the other.

I did add the ID field to the query and also I have swapped the report name to reflect the report's name.

I have a button containing the following code;

Private Sub cmdPrint_Click()
    Dim strWhere As String

    If Me.Dirty Then    'Save any edits.
        Me.Dirty = False
    End If

    If Me.NewRecord Then 'Check there is a record to print
        MsgBox "Select a record to print"
    Else
        strWhere = "[ID] = " & Me.[ID]
        DoCmd.OpenReport "Pharmacy form", acViewPreview, , strWhere
    End If
End Sub

Any idea what might be going wrong here?

 

[AccessMSSQL]

Please post the recordsource of your Pharmacy Form

 

[rclarke]

Please post the recordsource of your Pharmacy Form

Forgive me! How do I do that?

 

[AccessMSSQL]

oops...you need to go to your Pharmacy Report in design view and get to the report properties box. There is a property called RecordSource. This will show either the Query you have the report bound to or the table. If you click on the ".." next to that property it will bring up a query window where you can see the source query.

 

[rclarke]

Thanks, did you mean this?

SELECT [Trial Setup].[Study Acronym], [Trial Setup].[Study Title], [Trial Setup].[Protocol number], [Trial Setup].[EudraCT number], [Trial Setup].[R&D number], [Trial Setup].Sponsor, [Trial Setup.SponContact] & ", " & [Trial Setup.SponAddress] & ", " & [Trial Setup.sponTelephone] & ", " & [Trial Setup.spone-mail] AS ContactDetails, [Trial Setup].PharmProject, [trial setup.QS] & " " & [trial setup.YS] AS startdate, [Trial Setup].PharmTotalPts, [Trial Setup].PharmSite, [QInvestigator.FirstName] & " " & [QInvestigator.LastName] AS PIName, QInvestigator.[Job Title], QInvestigator.Hospital, [QInvestigator.Level] & ", " & [QInvestigator.Building] & ", " & [QInvestigator.Hospital] & ", " & [QInvestigator.Street] & ", " & [QInvestigator.City] & ", " & [QInvestigator.County] & ", " & [QInvestigator.Postcode] AS PIAddress, QInvestigator.Telephone, QInvestigator.[E-mail], [QNurse.FirstName] & " " & [QNurse.LastName] AS NurseName, QNurse.[Job Title], QNurse.Hospital, [QNurse.Level] & ", " & [QNurse.Building] & ", " & [QNurse.Hospital] & ", " & [QNurse.Street] & ", " & [QNurse.City] & ", " & [QNurse.County] & ", " & [QNurse.Postcode] AS NurseAddress, QNurse.Telephone, QNurse.[E-mail], [QStudyCoordinator.FirstName] & " " & [QStudyCoordinator.lastName] AS PrimaryContact, QStudyCoordinator.[Job Title], QStudyCoordinator.Hospital, [QStudyCoordinator.Level] & ", " & [QStudyCoordinator.Building] & ", " & [QStudyCoordinator.Hospital] & ", " & [QStudyCoordinator.Street] & ", " & [QStudyCoordinator.City] & ", " & [QStudyCoordinator.County] & ", " & [QStudyCoordinator.Postcode] AS PrimaryConAddress, QStudyCoordinator.Telephone, QStudyCoordinator.[E-mail]
FROM QStudyCoordinator INNER JOIN (QInvestigator INNER JOIN (QNurse INNER JOIN [Trial Setup] ON QNurse.NurseName = [Trial Setup].[Research Nurse]) ON QInvestigator.[PI Name] = [Trial Setup].[PI Name]) ON QStudyCoordinator.CoordName = [Trial Setup].[Data Contact];

Thanks.

 

[AccessMSSQL]

Yes...and the problem I see is you don't have the [ID] field selected in this query. Add ID to this query and save it back to the report recordsource.

 

[rclarke]

How foolish! I added it to the report but not the query! That puts an end to about 2 hours of frustration! Thanks so much!

 

[AccessMSSQL]

Ha! glad you got it worked out.