Problem passing a variable to a report textbox

[mpicco-RCS]

I have a form with 5 buttons on it. Each button is meant to select a warehouse location, so a query can be run to give an inventory report for that location. There is a separate query for each button and the OnClick event does properly modify the recordsource to give the appropriate data to the report for the location selected.

To this functionality I want the OnClick event VBA to pass the warehouse location to a textbox on the report, so the title of the report reflects that inventory location.

My code thus far is:

Private Sub Command5_Click()
Dim mySQL As String
Dim WHSE As String

mySQL = "SELECT [Master Part List].[Part Number], [Master Part List].Category, [Master Part List].Description, [Master Part List].MaterialCost, [Master Part List].Inventory, [Master Part List].Update, [MaterialCost]*[Inventory] AS [Total Cost], [Master Part List].Warehouse"
mySQL = mySQL & " FROM [Master Part List]"
mySQL = mySQL & " WHERE ((([Master Part List].Warehouse) = 'ZTE') And ((Left$([Part Number], 3)) <> '000') And (([Master Part List].Status) = 'A'))"
mySQL = mySQL & " ORDER BY [Master Part List].[Part Number];"
WHSE = "Z-Tech"

DoCmd.OpenReport "rptMain PARTS INVENTORY", acViewDesign
Reports![rptMain PARTS INVENTORY].Report.RecordSource = mySQL

DoCmd.Close acReport, "rptMain PARTS INVENTORY", acSaveYes
DoCmd.OpenReport "rptMain PARTS INVENTORY", acViewPreview

DoCmd.OpenReport "rptMain PARTS INVENTORY", acViewPreview
Reports![rptMain PARTS INVENTORY]!tbxWhseTitle = WHSE
DoCmd.Close acReport, "rptMain PARTS INVENTORY", acSaveYes
End Sub


When I get the report, the textbox is empty, instead of containing the text value for the warehouse location.

Is this a syntax problem, or is my approach wrong? Any guidance will be very much appreciated.

Thanks!

 

[Uncle Gizmo]

There's a trick I use which is handy because you don't need to open the report in design view.

You use the reports open event to extract the record source and any other variable data you require out of your form.

First of all you need to add some custom properties to your forn to hold the variables.

Next:-
In the Do.Cmd open report statement add the form name to the open args portion. This isn't strictly necessary, however it's a good habit to get into because it means you can use the report with any other form without changing the reports design.

Now in the report open event you access the forms custom properties. (The custom properties that you have just created) Shift the data that's stored them into the Record Source and any text boxes that you want displayed on the report like title, date.

If that sounds like something you would like to do I can show you the code.

 

[mpicco-RCS]

Thank you! I'd be very interested in seeing the code!

 

[Uncle Gizmo]

This set of slides was for a slighty different question:-

Generate Multiple Reports Presentation

However slide 20 shows a Custom property for the Form, have your command buttons pass the SQL into the property prpSQL_ForRpt. You will need another custom property to pass the textbox text. You want warehouse location, so create a custom property prpWareHouse, exactly like the property prpSQL_ForRpt code.

Slide 22 has the code for opening the report, you will need to create a string variable to hold the report name, and you might like to change the type of report opened to acViewPreview. (Notice "Me.Name" passes the name of your form through)

Slide 24 shows the code in the report, code remmed out hints at how to get other info displayed on your report.

 

[Uncle Gizmo]

The thread where I answered the question the presentation was for is HERE:-

Form saving several reports in one go

I did a video explaining the slides here:-
MS Access - Generating Multiple Reports

 

[mpicco-RCS]

Thank you!

I'll look this over and see if I can make it work for me.