Problems saving recordsource property

[one2three]

Hey people. I'm having some problems understanding a behviour I'm experiecing in Access 2003. I've written some VBA code to adjust the open a particular form and I then change recordsource property to a new table. Looking at the open form I can see that the recordsource has indeed been updated as the fields are displaying the appropriate values. However, if I close the form, the recordsource value goes back to what it was before. What gives? Is there something else I should be doing or can I only temporarily change the recordsource of a form programmatically?

Any thoughts would be appreciated.

 

[WayneRyan]

One,

When you change the form's recordsource, it is changing
one of the form's properties. The normal save actions
on the form apply to your data only.

I think you can do something like:

DoCmd.Save acForm

But, if use this technique in versions of Access > A97
then you will run into problems modifying forms in a
multi-user environment.

I'd create a default SQL string and assign it using the
form's OnOpen event (then requery the form).

Wayne

 

[JeremyNYC]

Well, if you're changing the recordsource to a different table, your data structure is almost certainly incorrect. Put all of those data in one table with an extra field to distinguish between them and then just filter for a particular value in that field.

Next, if you need to store something like this, store the recordsource or filter parameter in a table. There's a page on my site that details how I store constants. That's one way you could go about it.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[one2three]

I never designed this file to being with, i just need to modify it. Otherwise everything would have been in one table to begin with...

I'll use an onopen event to set the form's recordsource when it's launched to the table I need.

Thanks for your thoughts!