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