VBA to change report recordsource is not changing recordsource property box contents

[jmark@stayintouch.us]

Hi, I have a sample DB that is a very simple example of a problem I need to solve. A table with color and size. A RED query that filters by red and a BLUE query that filers by blue. A single report has no set record source in design mode. When the report it opened, it is opened with a recordsourse of Red set by On Open Vba. Two buttons on the report header are RED and BLUE. When the buttons are pressed they simply execute one line of vba to change the record source. the problem is I need the recordsource PORPERTY BOX to be populated with the new recordsource query so that the "Sort By" in the "Group, Sort and Total" section will work. I know there are other ways to do this, but this is a VERY simplified example of a more complex DB that needs the RecordSource Property to SHOW the current recordsource.


Can anyone help with this?



Thanks! jmark@stayintouch.us

 

[pbaldy]

I'm not clear where you expect to see the recordsource while the report is open. As I would expect, this shows the blue query as the record source:

Me.RecordSource = "QueryBlue"
MsgBox Me.RecordSource

 

[jmark@stayintouch.us]

hi, thanks for the quick reply! the attaches screenshots are what I am trying to achieve. Similar to if i used me.filter="test" instead of me.recordsource = "Blue". does that help you understand my issue? Thanks for the help! jm

 

[pbaldy]

I don't know that you can save the change while the report is open. This works, though it seems clunky.

Me.RecordSource = "QueryBlue"
DoCmd.Close
DoCmd.OpenReport "report1", acViewDesign
Reports!report1.RecordSource = "QueryBlue"
DoCmd.Save
DoCmd.Close

 

[pbaldy]

I should add that the sort & group fields can be set dynamically just as easily as the record source.

Me.GroupLevel(0).ControlSource = "FieldName"

 

[jmark@stayintouch.us]

i tried the code but as I suspected, the 2nd line: DoCmd.Close seems to terminate the vba. were you able to make it work in my sample?

 

[pbaldy]

were you able to make it work in my sample?

Yes, that exact code.

 

[pbaldy]

Before and after:

 

[jmark@stayintouch.us]

this is my vba. do i have something incorrect:

Option Compare Database

Private Sub Command2_Click()
Me.RecordSource = "QueryRed"
DoCmd.Close
DoCmd.OpenReport "report1", acViewDesign
Reports!report1.RecordSource = "QueryRed"
DoCmd.Save
DoCmd.Close
End Sub

Private Sub Command3_Click()
Me.RecordSource = "QueryBlue"
DoCmd.Close
DoCmd.OpenReport "report1", acViewDesign
Reports!report1.RecordSource = "QueryBlue"
DoCmd.Save
DoCmd.Close
End Sub

Private Sub Report_Open(Cancel As Integer)

Me.RecordSource = "QueryRed"
End Sub

 

[pbaldy]

Looks okay. I've got Access 2016. Can I ask what you're really trying to achieve? It would seem to defeat the purpose to close the report anyway. As I mentioned you can change the sorting at the same time you change the record source.

 

[jmark@stayintouch.us]

im using 2019 access on win 10 pro. can you try this file and see if i have something wrong? I would be happy to show you what im looking to do , but it a huge db, so i would need to remove all kinds of stuff first to make it smaller and remove the customer sensative info. i can do that if you would like. i appreciate your fast responses. Much more than i expected! jm

 

[pbaldy]

Works fine for me. I opened the report in design view and cleared the record source (it was set to the blue query). I closed and saved the report. I opened it and clicked the blue button. The report closed as expected. I opened in design view and the record source was the blue query again.

I'm just curious what process would require the record source to be set and saved as you describe. As shown in the first response, testing it at runtime shows it set as desired. It just won't save that way unless the report is put into design view.

 

[jmark@stayintouch.us]

let me send you a new file that shows what Im looking to do. it will take me a while to prepare the file. thanks again. jm

 

[pbaldy]

A video of it working.

 

[jmark@stayintouch.us]

yep. i see it working. and its doing that on my end. but i need it to not close. let me play a bit and send you a better sample of what im trying to do. Im in chicago, USA, where are you?

 

[pbaldy]

Nevada.

 

[jmark@stayintouch.us]

ok. i will get back with you. thanks again!

 

[pbaldy]

No problem. You could obviously add a line to reopen the report, but you'd run into the open code. You could add a public variable (declared in a standard module and test it:

Private Sub Command3_Click()
    Me.RecordSource = "QueryBlue"
    DoCmd.Close
    DoCmd.OpenReport "report1", acViewDesign
    Reports!report1.RecordSource = "QueryBlue"
    booReopen = True
    DoCmd.Save
    DoCmd.Close
    DoCmd.OpenReport "report1", acViewReport
End Sub

Private Sub Report_Open(Cancel As Integer)
  If booReopen = False Then
    Me.RecordSource = "QueryRed"
  End If
End Sub

That said, I'm still curious about what depends on the record source being saved like this.

 

[jmark@stayintouch.us]

yes, that overrun of code was what i was running into before I solicited help on the forum. Im not sure where the code control transfers and if there would be errors as a result. I have tested it briefly and it appears to work, but Im not skilled enough to really KNOW. I have never used public variables and am hoping there might be a better way to do what I am doing. I will try to send something to see soon. Thanks!

 

[arnelgp]

see this Change.