Indirect file or field references

[KenB]

Instead of using Control Source = tablename, how do I refer to a table whose
name is contained in a variable?
Similarly, rather than Object Source = objname, how do I refer to a field
whose name is contained in a variable?

 

[Jim Burke in Novi]

In a form or control event (I know nothing about your application, so I'm not
sure where the appropriate place would be), just code:

controlName.ControlSource = varName

This assumes that varName is a variable that has the table name in it. This
might be done in a form's Open Event, it could be in the Current Event if you
want it to change each time you navigate to a new record - it all depends on
when you know what the table name is.

 

[Marshall Barton]

Instead of using Control Source = tablename, how do I refer to a table whose
name is contained in a variable?
Similarly, rather than Object Source = objname, how do I refer to a field
whose name is contained in a variable?

Then ControlSource property doesn't usually use a table name
and I don't know what you are referring to by "Object Source
= objname"

If you are trying to refer to a member of a collection via a
string variable then use the syntax:
collectionname(stringvariablename)
For example a field in a recordset might be referenced this
way

Dim strName As String
Dim rs As Recordset
. . .
x = rs.Fields(strName)

 

[KenB]

In a form or control event (I know nothing about your application, so I'm not
sure where the appropriate place would be), just code:

controlName.ControlSource = varName

This assumes that varName is a variable that has the table name in it. This
might be done in a form's Open Event, it could be in the Current Event if you
want it to change each time you navigate to a new record - it all depends on
when you know what the table name is.

That sounds very promising, but please lead me a little further. On my main
form (called PrMain and from which other forms are called) I left the
RecordSource blank and in the On Open event put PrMain.RecordSource =
TempVars!TName This gives me a 424 "Object required" error. My
apologies for not using standard naming conventions.

 

[KenB]

Then ControlSource property doesn't usually use a table name
and I don't know what you are referring to by "Object Source
= objname"

If you are trying to refer to a member of a collection via a
string variable then use the syntax:
collectionname(stringvariablename)
For example a field in a recordset might be referenced this
way

Dim strName As String
Dim rs As Recordset
. . .
x = rs.Fields(strName)

The Property Sheet for my form currently has 'Jour2008' (which is a table
name) as the Record Source and that works fine. But next year I shall need
to use a table called 'Jour2009' instead. I just want to make the switch
easily in a whole set of forms and reports.

 

[Marshall Barton]

The Property Sheet for my form currently has 'Jour2008' (which is a table
name) as the Record Source and that works fine. But next year I shall need
to use a table called 'Jour2009' instead. I just want to make the switch
easily in a whole set of forms and reports.

Ahhh, you meant the form's RecordSource. In that case you
can use:

Dim strvar As String
. . .
strvar = "Jour2009"
Me.RecordSource = strvar

Obviously, the real trick is to figure out how to
specify/calculate the table name.

Your question reveals a lack of database normalization that
will cause more problems than just this one we're dealing
with here. Using a spreadsheet approach for a database will
cause no end of troubles.

 

[KenB]

Ahhh, you meant the form's RecordSource. In that case you
can use:

Dim strvar As String
. . .
strvar = "Jour2009"
Me.RecordSource = strvar

Obviously, the real trick is to figure out how to
specify/calculate the table name.

Your question reveals a lack of database normalization that
will cause more problems than just this one we're dealing
with here. Using a spreadsheet approach for a database will
cause no end of troubles.

Your advice to set Me!RecordSource to a variable was good and did help. But
I am puzzled that you inferred normalization problems and 'a spreadsheet
approach'. (As a matter of interest, I haven't used a spreadsheet since I
wrote my own version in the 1970's - in Fortran!)

 

[Marshall Barton]

Your advice to set Me!RecordSource to a variable was good and did help. But
I am puzzled that you inferred normalization problems and 'a spreadsheet
approach'. (As a matter of interest, I haven't used a spreadsheet since I
wrote my own version in the 1970's - in Fortran!)

That's Me DOT RecordSource. Bang is used with members of a
collection.

Having a separate table for each year is not normalized, but
having a separate sheet for each year is a common practice
with spreadsheets. The problem you are currently trying to
deal with is a consequence of the unnormalized structure.

A normalized design would have a single table with a field
for the year. This way, you can select the desired data
just by using a criteria on the year field. It would be no
more difficult to create multi-year reports, etc, which your
current design would have a lot of trouble doing. You would
also have a far easier time changing the table's structure
because the change would only need to be done once.