Object doesn't support this property or method

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Emma - 07 Jul 2006 00:56 GMT

I need help with the following select case statement.

I am using an option box on the main form to filter records in the subform
and for some reason I am getting the above referenced error.

Here is the select case code:

Private Sub Comm_Grp_Frame_AfterUpdate()
'update Account Compliance History subform with selection

Dim strSQL As String

Select Case Me!Comm_Grp_Frame

Case 1 'PTA selection
strSQL = "SELECT Qry_Archive.[Data Quarter], "
strSQL = strSQL & "Qry_Archive.Terr, "
strSQL = strSQL & "Qry_Archive.[Account No] , "
strSQL = strSQL & "Qry_Archive.[Comm Gp], "
strSQL = strSQL & "Qry_Archive.GPO, "
strSQL = strSQL & "Qry_Archive.Level, "
strSQL = strSQL & "Qry_Archive.Desc, "
strSQL = strSQL & "Qry_Archive.[12 Mo Units], "
strSQL = strSQL & "Qry_Archive.[Ann Mkt Pot], "
strSQL = strSQL & "Qry_Archive.Untapped, "
strSQL = strSQL & "Qry_Archive.[80% Units], "
strSQL = strSQL & "Qry_Archive.Compliant, "
strSQL = strSQL & "Qry_Archive.Action, "
strSQL = strSQL & "Qry_Archive.New_Tier"
strSQL = strSQL & "FROM Qry_Archive"
strSQL = strSQL & "WHERE (((Qry_Archive.[Comm_Gp])= 'PTA'))"
strSQL = strSQL & "ORDER BY Qry_Archive.[Data Quarter];"
Me!Sfrm_Qry_Archive!Comm_Gp.RowSourceType = "Table/Query"
Me!Sfrm_Qry_Archive!Comm_Gp.RowSource = strSQL

Case 2 'Stents selection
strSQL = "SELECT Qry_Archive.[Data Quarter], "
strSQL = strSQL & "Qry_Archive.Terr, "
strSQL = strSQL & "Qry_Archive.[Account No] , "
strSQL = strSQL & "Qry_Archive.[Comm Gp], "
strSQL = strSQL & "Qry_Archive.GPO, "
strSQL = strSQL & "Qry_Archive.Level, "
strSQL = strSQL & "Qry_Archive.Desc, "
strSQL = strSQL & "Qry_Archive.[12 Mo Units], "
strSQL = strSQL & "Qry_Archive.[Ann Mkt Pot], "
strSQL = strSQL & "Qry_Archive.Untapped, "
strSQL = strSQL & "Qry_Archive.[80% Units], "
strSQL = strSQL & "Qry_Archive.Compliant, "
strSQL = strSQL & "Qry_Archive.Action, "
strSQL = strSQL & "Qry_Archive.New_Tier"
strSQL = strSQL & "FROM Qry_Archive"
strSQL = strSQL & "WHERE (((Qry_Archive.[Comm_Gp])= 'Stents'))"
strSQL = strSQL & "ORDER BY Qry_Archive.[Data Quarter];"
Me!Sfrm_Qry_Archive!Comm_Gp.RowSourceType = "Table/Query"
Me!Sfrm_Qry_Archive!Comm_Gp.RowSource = strSQL

End select

End sub

Thank you!

Reply to this Message

Marshall Barton - 07 Jul 2006 05:28 GMT

>I need help with the following select case statement.
>
[quoted text clipped - 12 lines]
> Case 1 'PTA selection
> strSQL = "SELECT Qry_Archive.[Data Quarter], "

[ . . . ]

> strSQL = strSQL & "WHERE (((Qry_Archive.[Comm_Gp])= 'PTA'))"
> strSQL = strSQL & "ORDER BY Qry_Archive.[Data Quarter];"
[quoted text clipped - 3 lines]
> Case 2 'Stents selection
> strSQL = "SELECT Qry_Archive.[Data Quarter], "

[ . . . ]

> strSQL = strSQL & "WHERE (((Qry_Archive.[Comm_Gp])= 'Stents'))"
> strSQL = strSQL & "ORDER BY Qry_Archive.[Data Quarter];"
[quoted text clipped - 4 lines]
>
>End sub

That reference is incomplete. It usually worked in older
versions, but A2003 is more careful about checking it. Try
using:

Me!Sfrm_Qry_Archive.FORM!Comm_Gp.RowSourceType="Table/Query"
Me!Sfrm_Qry_Archive.FORM!Comm_Gp.RowSource = strSQL

Signature

Marsh
MVP [MS Access]

Reply to this Message