How to pass a parameter from a form to a report/query?

[BadBoy House]

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]

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]

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]

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]

What the DBguy said !

Remove the parameter from your reports query.

 

[BadBoy House]

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]

Hi. What happens if you use this criteria?

"[dbo_tblPartner.ReportField]='AT'"

If that doesn't work, try this one.

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

 

[BadBoy House]

The first one worked a treat:

"[dbo_tblPartner.ReportField]='AT'"

Thanks very much to you both.

 

[theDBguy]

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]

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]

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.)

Dim strManager as String

strManager = Me.YourCombo
Debug.Print strManager

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

 

[BadBoy House]

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]

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]

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.