change record source on a report

K

kitty77

Registered User.
Local time
Yesterday, 23:10
Joined
May 27, 2019
Messages
715
I have a form that opens up a report for that record number. How can I make that report use a different record source? I have a lot of reports and do not want to create new ones based on another record source. So, I would like to be able switch the record source without having to do it in design.

Thanks...
 
I'm using this... but format of the last line must not be correct.

Dim strDocName As String
strDocName = "ABC"
Dim strWhere As String
strWhere = "[Mrecordid]=" & Me!Mrecordid
DoCmd.OpenReport strDocName, acViewReport, , strWhere, "Masterhistory"
 
If "Masterhistory" is supposed to be a value passed via OpenArgs, it is in wrong argument.

Use Access intellisense popup tips as you type the command. It will tell what is expected in each argument.

Code in report Open event would have to change it's own RecordSource.
 
Assuming MasterHistory is an OpenArgs argument then you have a missing comma

Code: 
DoCmd.OpenReport strDocName, acViewReport, , strWhere,[COLOR="DarkRed"][B] , [/B][/COLOR]"Masterhistory"
 
I'm getting an error... You entered an expression that has no value.

DoCmd.OpenReport strDocName, acViewReport, , strWhere, , "Masterhistory"
 
kitty77 said:
I have a form that opens up a report for that record number. How can I make that report use a different record source? I have a lot of reports and do not want to create new ones based on another record source. So, I would like to be able switch the record source without having to do it in design.

Thanks...
Click to expand...
This sounds like you may have multiple tables with the same structure. Is that correct?
 
That is correct. I have a master table and a history table. Same structure.
 
kitty77 said:
That is correct. I have a master table and a history table. Same structure.
Click to expand...
How many records are in the master and how many in the history table? Why is it called "history?" Is it based on dates or status or something else?
 
Not many... 3000 records. Master is my active data and History is an achieve table. I like to keep them separate. But from time to time I need a report based on the history and I need a way to change the record source without having to go into the design.
 
kitty77 said:
Not many... 3000 records. Master is my active data and History is an achieve table. I like to keep them separate. But from time to time I need a report based on the history and I need a way to change the record source without having to go into the design.
Click to expand...
Hi. Sorry to pry, but we'll be curious to know why you "feel" that the records have to be separated? We would normally recommend against it. But if you have a valid reason, then maybe it's warranted.
 
They are completed records and keeping them in a separate table is best for me. When the history table get large, I would then move the table to another database. You're not prying...
 
Agree that it is usually best to 'flag' a record as being archived. IMHO a date field is best for that - if Null it isn't if a date, it is plus tells you when. You run the risk of dropping records if you move them around for no reason, or creating orphans, or perhaps even Access will bark at you because of relationships. Plus you don't have to do what you're trying to do that isn't working.

The code looks like it's converted from a macro, which is fine but somewhat limited as a learning tool. You end up with a lot of verbiage that isn't really necessary and would make for less than ideal practice going forward. Since the Where portion is small, it can be as simple as

Code: 
DoCmd.OpenReport "ABC", acViewReport,, "[Mrecordid]=" & Me!Mrecordid,, "Masterhistory"
As for why that doesn't work, put a break on your DoCmd line and when it stops there, check out the values of your variables. Something is likely missing.
 
kitty77 said:
They are completed records and keeping them in a separate table is best for me. When the history table get large, I would then move the table to another database. You're not prying...
Click to expand...
Okay. Thanks for clarifying that. Just as FYI, thousands of records is "nothing." Heck, even millions of records should not be a problem; but by then, other issues may be more important. So, if you have, say 5000, records in one table and 3000 of them are already completed, then we don't think there'll be any problems keeping the records together. The beauty of it is you can use the same report without changing the record source. All you have to do is add a filter to either include or exclude the completed records. You would do the same with your forms as well.
 
That me be so... but just something I've done for a long time, keeping it separate.
 
kitty77 said:
That me be so... but just something I've done for a long time, keeping it separate.
Click to expand...
And there's nothing really wrong with that. We're just letting you know there's really no need to do it that way. But if that's what you're comfortable with, then you should have it that way. Cheers!
 
I think I will take your advice and keep it together in one table. Might solve a few other issues going forward... Thanks!
 
kitty77 said:
I think I will take your advice and keep it together in one table. Might solve a few other issues going forward... Thanks!
Click to expand...
Hi. You're very welcome. If nothing else, you might find comfort in knowing that experienced Access (database) users/developers do it that way (use one table), so you'll be in good company. Good luck!
 
You must log in or register to reply here.

Similar threads

J
Creating ACCDE from ACCDB via GitHub or Azure DevOps Workflow – Any Experience?
Replies
14
Views
810
Josef P.
J
C
Solved No Current Record on Access Close
Replies
11
Views
798
Pat Hartman
P
Papa_Bear1
Form seems to have table locked even after closing.
2
Replies
27
Views
601
Gasman
Gasman
T
Solved Store data from text box to control source
Replies
16
Views
472
Gasman
Gasman
G
Enter parameter value of a field in Report.
Replies
6
Views
542
Georgios Eleftheriadis
G

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom