Can't refresh report in code.

[Guest]

I am changing the RecordSource and unbound text boxes in a report using code,
but i can't get the unbound controls to refresh automatically. I can manually
switch to Design Mode and back to Preview, but closing and opening the report
in code doesn't work.

The only sequence that doesn't throw errors is as follows:

DoCmd.OpenReport strKillKitReport, acViewDesign, , , acHidden
Set repHost = Reports(strKillKitReport)
strSQL = "SELECT ID, KitGroup1, KitItem, Descr, Franchise, " & _
"Item, DescrItem, Per, Cost, Sell, NatRankKit, " & _
"Avg_Demand FROM [Dead Kit Components] " & _
"WHERE [KitItem] = '9W1453RP2AA' AND [KitGroup1] = 'LP';"
repHost.RecordSource = strSQL
DoCmd.OpenReport strKillKitReport, acViewPreview, , , acWindowNormal
Set repHost = Reports(strKillKitReport) ': necessary (but dumb?!)
With repHost
!txtKilledKit = strKit
!txtNoItems = strNoItems
!txtNoPieces = strNoPieces
!txtKitCost = strCost
End With

After the last bit, nothing I do in code will refresh the report, even
closing (with acSaveYes) & reopening it.

What am I not doing?

-- Al

 

[Alex Dybenko]

hi,
I think you need to move this code to report's open event, then you also do
not need to open it in design mode

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

 

[Guest]

Hi Alex,

This is no good. If I try the statement:

Me !txtKilledKit = rs![KitGroup1] & " " & rs![KitItem]

inside the Report_Open routine I get:

"You can't assign a value to this object".

 

[Alex Dybenko]

Hi,
Ok, to set report's recordsource - you have to use open event
But for setting values to controls - you have to use Format event of section
of that control

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com