Get record source property for closed form

[Movian]

Hey,

So just migrated from access runtime 2007 to runtime 2010 due to end of life coming later in the year and our clients having to worry about security.

mostly done with that however I am now working through the many kinks that have come up due to hacks over the years as well as changes in workflow from 2007 to 2010.

The one I am trying to resolve at the moment, is in 2007 we had a function call on each form that when you open the form checked if there was a record for a patient and added it if not (As Part of this is we also set a number of user defined default values as part of this record creation).

After the migration to 2010 it appears that access creates records for subforms at a different time/process now as our old code that worked fine in 2007, in 2010 however it creates a primary key violation when attempting to leave the sub form.

As such I removed the reference to our sub on each sub form which has resolved the primary key violation problem.

However, this also means that the user definable default system is no longer working....

As such I am thinking of two stages to this fix, the first is to go through the list of sub forms that a client uses (the list of forms used is dynamic, and speed is a factor so I would rather add records for only the forms they use). And create records for the record source of each of those forms at the time of the main record so that each form already has a record when it's opened the first time... The problem being that I would need to reference the record source for each of these forms and most of them will be closed.

So with all the pre amble out of the way the crux of my question is

Q) Is there a way to access the record source property of a form without opening the form? (note this is a .adp project)

This is the short term fix to ensure that users can continue using this feature with all the settings they have already setup with minimal time investment to get this out ASAP.

I will be reworking the user definable default system after this to actually work with the SQL table defaults... (as much as I hate the TSQL for working with that it seems like the correct way to implement this). However this will take more research on my part as well as a complete re work of certain systems so I would like to get the first iteration out to start and then take my time with this version of the fix to ensure it is done correctly.


Thanks in advance!

[OBP]

Hello Movian, long time since I conversed with you.
It will depend on how the forms have been set up, I always used Queries to supply the recordset for the my forms and they are always availabe for manipulation whether the form is open or closed.
If you can identify the record sources you should be able to open them for Edit or Addition, you could even add queries to do so, I suppose it depends on how many forms you need to work with.

[HiTechCoach]

For VBA code to get the record source property of a form or report it must be opened in either design or view mode. Or you could save the object to a text file and parse the text file.

YI have helped client's update their database(s) that they .created with Access 2000/2002/2003 (mdb) to work with Acess 2007 and later. Some were left in the MDB format. Some were converted to the newer ACCDB format. It has been my observation that Acess 2007 is less lenient with improper use of VBA and events. Access 2010/2013 is even more strict with the newer version of VBA.

Stuff that worked in Access 2000 just did not work in 2007 and later until I corrected the code to use the proper events.

Also note:

Access 2007 uses VBA version: 6.5

Acess 2010 uses VBA version 7. This new version also supports 64-Bit Office.

[Movian]

the forms record source is just a link to the table.

.ADP files don't like have queries in them.

I guess I will just bite the bullet and try and setup the TSQL so that when they modify the default for a particular field it actually sets it directly in the database.

was just trying to see if there was a shortcut temporary system as there are some other items involved with that (Ensuring that the custom defaults stored in the table are set on the DB after that update).

At some point I need to investigate migrating the .adp to a .accdb as office 2013 doesn't support .adp but that will be a largish project and I may just investigate re writing the front end in C# at that point.