0

I have a form that opens a report based on a combo box selection. Which looks like. enter image description here

The invoice shipment button opens the report via

DoCmd.OpenReport "ItemList4", acViewReport, , "ShipRef = " & Me.SRCB

SRCB is the combo box next to the shipment label.

When clicking the invoice shipment button I always get asked what the parameter value for S100018 is, so obviously it knows what the value is but isn't applying it to the filter when opening the report like so

enter image description here

How do I prevent this from happening?

Rynoc
  • 45
  • 1
  • 1
  • 9

3 Answers3

1

I always create a query with a where clause that refers back to the textbox in the form. Then I build the report on the query, which selects 1 record, and I get my report.

I'l show a quick demo, here is a table named Persons: enter image description here

Then create a form with a textbox, I named the form PersonForm: enter image description here

Now creat a query that selects everything from the Persons table. In the where clause, open the builder, browse to the created form and select the textbox. enter image description here

Then we create a very simple report with the report wizard, based on the query. enter image description here

Now we go back to our created form and add a button. In the button, select for the option to open a report.

enter image description here

Now if you open the form in Form view, enter the name Ivo in my instance. And then click the button.

enter image description here

Of course you have to adjust the example to your context.

Create a query with the data that you need to generate the report. Then add a where clause in the query to the list box where you display your shipment id. Then let the report get the data from the query.

rotgers
  • 1,992
  • 1
  • 15
  • 25
  • This sort of works, however I am not pulling the right information on the report. For example the query pulls every single record for "Item" going to play around with it some more. – Rynoc Sep 11 '15 at 14:35
0

The problem seems to be the report. It looks like it's accesing a variable called S100018. Is this intended behaviour?

Domi
  • 26
  • 2
  • S100018 is a field on the report. It's the field I want the report to be filtered by. So in theory you select S100018 or perhaps s100019 from t he combo box then click invoice shipment then the report will be filtered by that "Shipment" – Rynoc Sep 11 '15 at 14:07
0

The problem was that it was not sending the filter argument as a string

it should be as such DoCmd.OpenReport "ItemList4", acViewReport, , "ShipRef = '" & Me.SRCB & "'"

Rynoc
  • 45
  • 1
  • 1
  • 9