VBA to change report recordsource is not changing recordsource property box contents (1 Viewer)

Local time
Today, 11:49
Joined
Apr 25, 2019
Messages
35
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
 

Attachments

  • RecordSource Problem.accdb
    976 KB · Views: 319

pbaldy

Wino Moderator
Staff member
Local time
Today, 09:49
Joined
Aug 30, 2003
Messages
35,709
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
 
Local time
Today, 11:49
Joined
Apr 25, 2019
Messages
35
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
 

Attachments

  • screenshots.pdf
    141.9 KB · Views: 331

pbaldy

Wino Moderator
Staff member
Local time
Today, 09:49
Joined
Aug 30, 2003
Messages
35,709
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

Wino Moderator
Staff member
Local time
Today, 09:49
Joined
Aug 30, 2003
Messages
35,709
I should add that the sort & group fields can be set dynamically just as easily as the record source.

Me.GroupLevel(0).ControlSource = "FieldName"
 
Local time
Today, 11:49
Joined
Apr 25, 2019
Messages
35
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

Wino Moderator
Staff member
Local time
Today, 09:49
Joined
Aug 30, 2003
Messages
35,709
Before and after:

1621815271053.png


1621815283674.png
 
Local time
Today, 11:49
Joined
Apr 25, 2019
Messages
35
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

Wino Moderator
Staff member
Local time
Today, 09:49
Joined
Aug 30, 2003
Messages
35,709
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.
 
Local time
Today, 11:49
Joined
Apr 25, 2019
Messages
35
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
 

Attachments

  • RecordSource Problem.accdb
    976 KB · Views: 325

pbaldy

Wino Moderator
Staff member
Local time
Today, 09:49
Joined
Aug 30, 2003
Messages
35,709
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.
 
Local time
Today, 11:49
Joined
Apr 25, 2019
Messages
35
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

Wino Moderator
Staff member
Local time
Today, 09:49
Joined
Aug 30, 2003
Messages
35,709
A video of it working.
 

Attachments

  • 2021-05-23_17-43-46.zip
    384.1 KB · Views: 164
Local time
Today, 11:49
Joined
Apr 25, 2019
Messages
35
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

Wino Moderator
Staff member
Local time
Today, 09:49
Joined
Aug 30, 2003
Messages
35,709
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:

Code:
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.
 
Local time
Today, 11:49
Joined
Apr 25, 2019
Messages
35
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

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:49
Joined
May 7, 2009
Messages
16,394
see this Change.
 

Attachments

  • RecordSource Problem.accdb
    976 KB · Views: 281

Users who are viewing this thread

Top Bottom