Subforms Record Source

[nath]

Hi,

I have a subform within a form that i would like to use
based upon two tables. To put you in the picture, i wish
to select a month, then alter the subform to use the
relevant table for that month. I have written the VB
behind the combo to create the correct table name, but
dont know how to feed the SQL created :

tbl_name = "TBL_" & combo6.value
sql = "SELECT * FROM " & tbl_Name & ";"

How do i now make the recor source of the subform this
SQL??

TIA

Nath.

 

[Marshall Barton]

I have a subform within a form that i would like to use
based upon two tables. To put you in the picture, i wish
to select a month, then alter the subform to use the
relevant table for that month. I have written the VB
behind the combo to create the correct table name, but
dont know how to feed the SQL created :

tbl_name = "TBL_" & combo6.value
sql = "SELECT * FROM " & tbl_Name & ";"

How do i now make the recor source of the subform this
SQL??

Me.subform.Form.RecordSource = sql

There is something funny going on when you have the same
data fields in multiple tables. If possible, you would be
much better off if you used a single table with a month (or
Date) field.

 

[nath]

-----Original Message-----

I have a subform within a form that i would like to use
based upon two tables. To put you in the picture, i wish
to select a month, then alter the subform to use the
relevant table for that month. I have written the VB
behind the combo to create the correct table name, but
dont know how to feed the SQL created :

tbl_name = "TBL_" & combo6.value
sql = "SELECT * FROM " & tbl_Name & ";"

How do i now make the recor source of the subform this
SQL??

Me.subform.Form.RecordSource = sql

There is something funny going on when you have the same
data fields in multiple tables. If possible, you would be
much better off if you used a single table with a month (or
Date) field.

--
Marsh
MVP [MS Access]
.
Hi.

Is that the case for Access 97? can you advise of the
correct syntax. The form is V_Form1, the sub is VS_Form2.

The reason the tables have similar data, is cause the
hierarchy changes from month to month. I.e. someone may
have a new manager in june, than they did in May.

 

[Marshall Barton]

-----Original Message-----

Is that the case for Access 97? can you advise of the
correct syntax. The form is V_Form1, the sub is VS_Form2.

It's the same in all versions.

As long as the code is in the main form, you should use Me
Me.[VS_Form2].Form.RecordSource = sql

instead of a full reference
Forms![V_Form1].[VS_Form2].Form.RecordSource = sql

Note that the VS_Form2 must be the name of the subformform it's displaying.

The reason the tables have similar data, is cause the
hierarchy changes from month to month. I.e. someone may
have a new manager in june, than they did in May.

??? That sounds like another normalization flaw?