Two Access bugs.

[Atlas]

Access 2003 (with or without SP1) , adp, SQL server

1) Having a form and inserting manually a subform, when the subform wizard
pops up, selecting an existing subform, causes access to select a table with
the same name of the form. To force the subform loading the subform I have
to manually code in the "source object field": form.myformname

2) form + subform; if subform recordsource is set to a table, no problem.
if subform recordsource is set to a select statement (syntax check ok, works
if opened straightfully as a form) then an error message pops up stating
"The column name '_tableName' does not match with a table name or alias name
used in the query". Pay attention and notice that extra '_' in front of the
table name.
Traceing out the error with the profiler here's the offending statement:

declare @P1 int
set @P1=NULL
exec sp_prepare @P1 output, N'@P1 int', N'SELECT "_tableName".* FROM (SELECT
[tableName].*, [otherTable].* FROM [tableName] INNER JOIN [otherTable] ON
[tableName].IDT = [otherTable].IDO) AS DRVD_TBL WHERE ((@P1 = "IDT"))', 1
select @P1

There's an evident extra '_' char in the statement......

 

[Atlas]

BTW, I'm testing on WinXp + SP1, MDAC 2.8, Microsoft SQL Server OLE DB
provider.

 

[Steve Jorgensen]

Access 2003 (with or without SP1) , adp, SQL server

1) Having a form and inserting manually a subform, when the subform wizard
pops up, selecting an existing subform, causes access to select a table with
the same name of the form. To force the subform loading the subform I have
to manually code in the "source object field": form.myformname

That bug was probably never detected because it's a bad idea to have 2
different types of object with identical names, and I've never seen anyone do
that. Your form should usually be called something like frmCustomer or
frmContactsSub. Wizards frequently have limitations like that since they are
add-ons and don't have the same quality requirements as Access itself.

2) form + subform; if subform recordsource is set to a table, no problem.
if subform recordsource is set to a select statement (syntax check ok, works
if opened straightfully as a form) then an error message pops up stating
"The column name '_tableName' does not match with a table name or alias name
used in the query". Pay attention and notice that extra '_' in front of the
table name.
Traceing out the error with the profiler here's the offending statement:

declare @P1 int
set @P1=NULL
exec sp_prepare @P1 output, N'@P1 int', N'SELECT "_tableName".* FROM (SELECT
[tableName].*, [otherTable].* FROM [tableName] INNER JOIN [otherTable] ON
[tableName].IDT = [otherTable].IDO) AS DRVD_TBL WHERE ((@P1 = "IDT"))', 1
select @P1

There's an evident extra '_' char in the statement......

Very interesting. I know there's also a bug (supposedly fixed, but it isn't)
in which a subform or subreport has an ORDER BY clause with a qualified field
name in it such as "ORDER BY my_table.my_value". The form or report will
often fail to work in this case. Removing the qualifier makes it work. Of
course, the query builder always puts the qualifier in.

 

[MasterCherry]

That bug was probably never detected because it's a bad idea to have 2
different types of object with identical names, and I've never seen anyone do
that. Your form should usually be called something like frmCustomer or
frmContactsSub. Wizards frequently have limitations like that since they are
add-ons and don't have the same quality requirements as Access itself.

If it is allowed, why not? It may be confusing, but once used to it it's
quite natural. Anyhow the bug occurs also in the properties windows, not
only in the wizard.

 

[Steve Jorgensen]

I just tried an experiment. In my case, when I create a table and a form with
the same name, and create a new form that refers to that name for the source
object of a subform control, I get the form, not the table in the subform.

I can get the table by specifying Table.<tablename> for the source object, but
I only get the table if I do that. I don't have to specify "Form." to get the
form, so forms seem to take precedence over tables.

I'm using Access 2002, SP-2.