Subform filetring error.....

[Atlas]

A2003 + ms sql server 2000 + adp

I have a continuos form whose contents are populated dinamically (whenever
two comboxes on the form change values) with a "select " statement assigned
to the recordsource property. The form also has a subform (datasheet view)
linked by child/master fileds properties.

It all works perefectly.

Now trying to set a filter on the subform programmatically, I've put the
following code in the form's Sub dealing with "select" update:

With Me![subformcontrolname].Form
.Filter = "[Data] >= '" & value '******** it's a date..............
.FilterOn = True
End With


Unfortunatelly I get errors like:

The column prefix 'subformcontrolname'
does not match with a table name or alias name used in the query

or

runtime error '7874'
[sqlserver databasename] cannot find the object "[selectstatement]"

I've tried to disable the link child/master property, with same
results......

Any hint?

 

[Atlas]

While waiting for useful help, I've done a test on another form (+ subform)
whose record source is simply a table, and the subform filter works
flawlessly.

So I'm really starting to think that Access gets confused when it must apply
the filter on a subform, linked to a master whose contents are dinamically
set by the recordsource property....

Could it be that?

 

[Atlas]

Another test made:

I've opened only the subform (as a form, obviously). Then I've placed a
button that applies the filter on the form, and it works.

 

[Atlas]

Nothing to do.
I keep on getting a runtime error 107 "The column prefix 'nameOfMySubform'
does not match with a table name or alias name used in the query".
What bloody query is he talking about?
The recordsource one?

Is there a way to check active columns either in the form or subform?

 

[Atlas]

Desperatelly trying to find a solution, I've palced a breakpoint in the code
at runtime.
Adding a watch to the Me var and expanding the values, it looks like the
subformname control is missing. Only the label appears in the list.

Whuhu?

Help!

 

[Atlas]

Desperatelly trying to find a solution, I've palced a breakpoint in the
code

at runtime.
Adding a watch to the Me var and expanding the values, it looks like the
subformname control is missing. Only the label appears in the list.

Digging I've found it in

Me
->controls
-->item 37
--->controlname

 

[Atlas]

Reading the sql server books online it shows it is a server error
(107)......help!

 

[Atlas]

Bloody access......he is generating a wrong query.......
what shall'I do?

Read this:


Error 107
Severity Level 15
Message Text
The column prefix '%.*ls' does not match with a table name or alias name
used in the query.

Explanation
A column prefix was specified that does not correspond to any table name
specified in the query. Match the column prefixes against the table names
and alias names in the FROM clause.

One common cause of this error is the use of a table name when an alias name
for the table is also supplied. When working with a table alias (a
correlation name in ANSI terminology), the syntax checking in Microsoft® SQL
ServerT complies with the ANSI specification. ANSI states,

A <table name> ... is exposed ... if and only if the <table reference>
does not specify a <correlation name>.
If an alias has been provided for a table name in the FROM clause, you can
use the alias only to qualify columns from the table; the table name cannot
be used elsewhere in the statement because they are flagged as syntax
errors.

As an example of the difference in behavior, assume this script has been
executed:

USE Northwind
GO
SELECT Customers.ContactName
FROM Customers cu
WHERE ContactName LIKE 'C%'
GO
SELECT cu.ContactName
FROM Customers cu
WHERE Customers.ContactName LIKE 'C%'
GO
In both SELECT statements, notice the use of Customers to qualify the column
ContactName even though a table alias of cu has been provided to substitute
for the table name. Both of these queries return this error message:

Server: Msg 107, Level 16, State 3

The column prefix 'Customers' does not match with a table name or alias name
used in the query.

Action
Use the column prefix that corresponds to the exposed name of the table.

Rewrite any queries where column names are qualified with the table name.
Use the table alias instead. For example, this SELECT statement is
equivalent to the ones above and uses a table alias for column
qualification:

USE Northwind
GO
SELECT cu.ContactName
FROM Customers cu
WHERE cu.ContactName LIKE 'C%'
GO

See Also

Errors 1 - 999

Query Fundamentals

SELECT

Using Table Aliases

 

[Atlas]

No answers.....WOW!!!!!
I gave up. Instead of appling a filter I've managed to modify straight away
the subform.recordsource property with a new select statement....


Bye guys......