Changing Subform RecordSource Programmatically

[DarkWolff]

I'm having issues changing a subform's record source programmatically. I have a subform with a list of projects that a staff member is allocated to, but I've gotten a request to also have a view of all a project list.



I thought I could just have a command button to change the record source to another query, but every time I do that I get either an error or a table open error. Their are also master/child links, but the fields have the same names in both queries (not sure if it matters).

Edit:
Side question: Is it possible to make a form into a class that can be invoked programmatically?

[June7]

Not sure what you mean by 'view of all a project list'. You want to show all projects not just that staff member's? Yes, the Master/Child links will prevent that if the links are on StaffID.

[DarkWolff]

Not sure what you mean by 'view of all a project list'. You want to show all projects not just that staff member's? Yes, the Master/Child links will prevent that if the links are on StaffID.

Basically I have two queries. One that shows a list of the projects a staff is allocated for, and another that just shows the whole project list. Whenever I try to change the subform's source I get errors.

Here is what I'm trying to do currently:

    Me!fsubTSResAlloc.Form.RecordSource = "QRY_TS_All"
    Me!fsubTSResAlloc.Form.LinkChildFields = ""
    Me!fsubTSResAlloc.Form.LinkMasterFields = ""
    Me.fsubTSResAlloc.Form.Requery

I'm not really sure how to progress, and feel like I'm screwing up the syntax somehow.

[June7]

The Master/Child links are properties of the subform container, not the form object. I always name subform containers different from the object they hold, like ctrAlloc. Then code:

Me.ctrAlloc.LinkChildFields = ""
Me.ctrAlloc.LinkMasterFields = ""
Me.ctrAlloc.Form.RecordSource = "QRY_TS_All"
Me.ctrAlloc.Requery

[DarkWolff]

Thanks for your reply. Running the code I get a "table is already opened exclusively by another user" error. The only user in the system is me and I have no other components open. This is happening when I just try to run the link codes as well, so I'm not sure what's going on here.

[June7]

I have never tried programatically setting links and recordsource of subform. Might not be possible unless in Design view. If you want to provide project I will analyze.

[DarkWolff]

I have never tried programatically setting links and recordsource of subform. Might not be possible unless in Design view. If you want to provide project I will analyze.

Unfortunately I can't provide the DB as there is too much data in it that can't be shared. I tried to strip down and make a barebones version but it was clear pretty quick I'd have to change a lot of data to be able to do that while still having the form functional.

Perhaps I should try another methodology; maybe a hidden linked field would be a better way?

[June7]

I just did a quick test of code resetting Links and RecordSource and it did work. I did not use a saved Access query object as the RecordSource. I built the SQL in the code.
Me.ctrSections.Form.RecordSource = "SELECT * FROM Strength;"

Have you step debugged?

[DarkWolff]

I have step debugged and I get the open table error when trying to change the child or master links; not sure why.

Edit: Found the issue; I forgot to disable record locks on the project list (since there is no need to edit it).

Edit 2: Now everything is working perfect. Thanks for your help!