How to pass a parameter from a form to a report/query? (1 Viewer)

BadBoy House · Feb 12, 2019

I have a report in my database which uses a query as the record source.

The query has a parameter called [Manager Initials] - when the user types in the manager's initials the report runs and shows the details specific for that manager.

I would like to create a form (or most likely set up a switchboard screen) containing the names of the six managers as text boxes or buttons so that they can simply click on the name in order to run the report specific for that manager without having to enter their initials.

Is it possible to pass parameters to a report from a textbox/button on a form?

Minty · Feb 12, 2019

I would use a combo box to list the managers (If they change or you add or remove personnel you aren't redesigning your form) and use the where clause of the OpenReport command to filter to the correct Manager/ManagerID .

Blatently copied from here https://access-programmers.co.uk/forums/showthread.php?t=172715

When you are looking for values from

1. A Text Field - surround with quotes (single quotes, triple double quotes or Chr(34) will do).
DoCmd.OpenReport DocName, acViewPreview, , "[ExaminerID]='" & Arg2 & "'"
OR
DoCmd.OpenReport DocName, acViewPreview, , "[ExaminerID]=" & """ & Arg2 & """
OR
DoCmd.OpenReport DocName, acViewPreview, , "[ExaminerID]=" Chr(34) & Arg2 & Chr(34)

2. A Date Field - Surround with octothorpes (#)
DoCmd.OpenReport DocName, acViewPreview, , "[DateFieldName]=#" & Arg2 & "#"

3. A numeric Field - do not use anything.
DoCmd.OpenReport DocName, acViewPreview, , "[NumericFieldName]=" & Arg2

BadBoy House · Feb 12, 2019

Thanks Minty

I've had a play around with this using the On Click property of a text box.

I can get the report to run using this code:

DoCmd.OpenReport "All Open Jobs - Partner Select", acViewPreview

- This runs the report as normal and prompts for the initial's to be typed in.

But if I try this in order to supply the initials - in this case AT:

DoCmd.OpenReport "All Open Jobs - Partner Select", acViewPreview, , "[dbo_tblPartner.ReportField]='" & AT & "'"

It still prompts for the initials, and if I type the initials in it doesn't show any results on the form.

[dbo_tblPartner.ReportField]= is the field on the query that contains the parameter "Enter partner initials".

Hope I'm making sense!

I'm sure this is going to come down to using the correct code - can you see where I'm going wrong?

theDBguy · Feb 12, 2019

Hi. If you want to replace your parameter prompt with a textbox on the form, you will have to replace your query criteria from something like [Manager Initials] to something like [Forms]![FormName].[TextboxName]. However, if you use the approach Minty suggested above, then you'll have to remove the parameter from your query. Essentially, you want an unfiltered query for your report because the code to open it will apply the criteria based on user's selection.

Minty · Feb 12, 2019

What the DBguy said !

Remove the parameter from your reports query.

BadBoy House · Feb 12, 2019

Thanks both.

I removed the parameter from the query and used the code below to run the report however the report just runs without displaying any data.

If I can get the report to run and select all of the records where tblPartner.ReportField equals the initials AT then I think it will work.

Private Sub Text0_Click()
DoCmd.OpenReport "All Open Jobs - Partner Select", acViewPreview, , "[dbo_tblPartner.ReportField]='" & AT & "'"
End Sub

theDBguy · Feb 12, 2019

Hi. What happens if you use this criteria?

Code:

"[dbo_tblPartner.ReportField]='AT'"

If that doesn't work, try this one.

Code:

"[dbo_tblPartner].[ReportField]='AT'"

BadBoy House · Feb 12, 2019

The first one worked a treat:

"[dbo_tblPartner.ReportField]='AT'"

Thanks very much to you both.

theDBguy · Feb 12, 2019

BadBoy House said:
The first one worked a treat:

"[dbo_tblPartner.ReportField]='AT'"

Thanks very much to you both.

Hi. You're welcome. Minty and I were happy to assist. Good luck with your project.

BadBoy House · Feb 13, 2019

I agree a combobox would probably be better for this. As you rightly say if personel change it means editing the form.

I can add the combobox to display the correct source list of people, but what would be the right code to use?

This is the code that I have in place for the text box:

Private Sub Text0_Click()
DoCmd.OpenReport "All Open Jobs", acViewPreview, , "[dbo_tblPartner.ReportField]='AT'"
End Sub

Minty · Feb 13, 2019

Put your combobox value into a string variable to make sure you're getting what you want; (This could be shortened but is long-winded to help you see what is going on.)

Code:

Dim strManager as String

strManager = Me.YourCombo
Debug.Print strManager

DoCmd.OpenReport "All Open Jobs", acViewPreview, , "[dbo_tblPartner.ReportField]='" & strManager & "'"

BadBoy House · Feb 13, 2019

It would be column 1 from the combo box that contains the value to pass to the report - would I need to specify this somewhere in the code?

Also, would the code go on the After Update property of the combo box?

Minty · Feb 13, 2019

Combo Columns are numbered from 0 so the first column is

strManager = Me.YourCombo.Column(0)

And yes in the after update event rather than the click event.

BadBoy House · Feb 13, 2019

After referring back to some code with comboboxes which you previously helped me out with I figured it out:

DoCmd.OpenReport "All Open Jobs", acViewPreview, , "[dbo_tblPartner.ReportField]='" & Me.PartnerCombo.Column(1) & "'"

This seems to work great. I added this to the After Update property of the combo box.

Thanks again.

How to pass a parameter from a form to a report/query? (1 Viewer)

BadBoy House

Registered User.

Minty

AWF VIP

BadBoy House

Registered User.

theDBguy

I’m here to help

Minty

AWF VIP

BadBoy House

Registered User.

theDBguy

I’m here to help

BadBoy House

Registered User.

theDBguy

I’m here to help

BadBoy House

Registered User.

Minty

AWF VIP

BadBoy House

Registered User.

Minty

AWF VIP

BadBoy House

Registered User.

Similar threads

Users who are viewing this thread