Alternate Record Sources

[DCinFRANCE]

Okay, I have a form, working great, and it's record source is:
SELECT [tblI) Training].*, [tblLU Role Codes].luRCfunc FROM [tblLU Role Codes] INNER JOIN [tblI) Training] ON [tblLU Role Codes].luRCcode = [tblI) Training].T_ROLE;

What I want to do is when the form loads, be able to substitute a different source for [tblI) Training].*

The other sources (tables) have an identical structure, so it's all that has to change?

Any suggestions how I might go about it? Any and all help, as always, will be greatly appreciated. Thanks in advance.

 

[pbaldy]

Why have multiple tables with the same structure? Normally you'd have a single table with an additional field to denote whatever differentiates them. Then all you do is filter this form, not change its source. What you ask can be done, but it's probably not the correct way to go.

Also, objects with symbols and/or spaces in the names are not worth the bother in the long run.

 

[DCinFRANCE]

Thanks for asking.

The contents are different, structure is the same.

What it is is that there are quarterly meetings which present the current state of training. The file is then copied (archived) to another name as historical. If we look at previous quarterly meetings, we want to see the state of training at the time of that meeting...say two years ago. So, I want to be able to decide which historical file to present.

Make sense?

 

[pbaldy]

Makes sense but I'd probably have a single table with a meeting date field. If you want to stay with your method, you'll need to set the record source property of the form in its open event. Since its SQL rather than just the table, you'll have to build it. Presuming the desire table is selected on another form, this type of thing:

Dim strSQL As String
strSQL = "SELECT... FROM " & FormReference & " Rest of string"
Me.RecordSource = strSQL

 

[DCinFRANCE]

Thanks, Paul. The rest of the story is slightly more complicated because building the "today" file is very complicated...like bringing 3 cross tab queries together to create the file.

Yes, I see what you're doing. However, I'm doing this whole thing without resorting to VBA. So, it would take a macro on open, but the macro is blind to access variables. I can't pass to it a string.
:banghead:

 

[Mark_]

DCinFRANCE,

Why not use VBA? Is this an attempt at making it difficult for yourself?

 

[pbaldy]

I don't use macros, I'm not sure you can create an SQL string in one. If you just used the table you could probably do that. I'd still have it in one file. You can create a temp table with your existing process, then append to a combined table, adding a date field.

 

[DCinFRANCE]

Aside from being a masochist you mean, Marc_? LOL. I wanted this data base built and the IT department whined and whined about how much coding it would take (read that money). I proclaimed that nonsense and said I could do it myself without any programming aside from a macro or two. They said, go ahead--you'll fail. I had the head of my IT in the other day and showed it to him all working and looking glorious. He was furious and left red faced. I told him I'm not quite done...there were a few things I wanted to do (like what I'm taking about), but it was a very satisfying day.

Pbaldy, you can create a string within the macro that will end up in the form...I've done it with filters and sure as hell the filter string ends up in the form...but that's about it. What it can't do is see the values of anything in Access--I can't test or use the contents of a string from the form to the macro.

I think in the end you are right and I'll have to back track to rethink this through.

 

[pbaldy]

Well, if you can build a string you can build one that includes a table name pulled from a form and assign it to the record source.

 

[DCinFRANCE]

I'm not sure, but that almost sounds like what I'm looking for. Every time I've tried to use something from a form in macro, it doesn't know what it is. I appreciate your time, but could I ask you to elaborate?

 

[pbaldy]

I don't use macros, but you probably need the full form reference:

http://www.theaccessweb.com/forms/frm0031.htm