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

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

"Allen_N" <(E-Mail Removed)> wrote in message
news:B5F88E1F-79DD-41AA-9072-(E-Mail Removed)...
>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

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

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

"Allen_N" <(E-Mail Removed)> wrote in message
news:A2815EF2-4AD8-47B0-AFEE-(E-Mail Removed)...
> 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".
>
>