unable to set RecordSource programatically

[T Ray Humphrey]

I have an Access 2002 application with linked tables to SQL Server (just
recently used SSMA to migrate data to SQL). The Jet d/b app has worked fine
for years with local data.

I have a main form with nested subforms, at most 4 levels deep. My 1st
through 3rd levels of subforms load fine. The 4th level doesn't load its
data. If I open the main form in the Jet d/b, all works fine. If I open the
3rd-level subform by itself (as the top level) in the SQL back-end d/b, it
shows the "4th" level subform's data correctly (really at the 2nd level in
this instance).

But when I open the main form in the SQL back-end d/b, the 4th level does
not load its data. In the debugger, I can see the RecordSource property of
the 4th level subform is "" (though in design mode, it is set to a table
name). I have added code in several places (parent's Current, subform's Load
and Current) to set the RecordSource property. When I step past the code, it
does not change the value. No errors are reported.

Is there a limit to the number of nested subforms that linked tables will
handle?

Thanks in advance,
Ray

 

[T Ray Humphrey]

A further clarification:
I'm in the process of changing my RecordSource properties for more efficient
loading at startup. Before I spoke about the 4th level subform not loading
its data. Now these code changes don't even work at the 3rd level (which was
loading its data when RecordSource was set to the table name):
1) change RecordSource at design time to SELECT * FROM tbl WHERE 0=1
2) remove Link Master Fields and Link Child Fields
3) at runtime in the parent form's Current event, set RecordSource to
"SELECT * FROM tbl WHERE SchedID=" & Nz(Me!SchedID, 0)
4) I even tried setting the RecordSource in the subform's Load event

When I step through this code in the debugger, it does not change the
RecordSource value! I am really stumped! What feature of SQL-linked tables
have I stumbled over?

Thanks,
Ray

 

[Dirk Goldgar]

A further clarification:
I'm in the process of changing my RecordSource properties for more
efficient
loading at startup. Before I spoke about the 4th level subform not loading
its data. Now these code changes don't even work at the 3rd level (which
was
loading its data when RecordSource was set to the table name):
1) change RecordSource at design time to SELECT * FROM tbl WHERE 0=1
2) remove Link Master Fields and Link Child Fields
3) at runtime in the parent form's Current event, set RecordSource to
"SELECT * FROM tbl WHERE SchedID=" & Nz(Me!SchedID, 0)
4) I even tried setting the RecordSource in the subform's Load event

When I step through this code in the debugger, it does not change the
RecordSource value! I am really stumped! What feature of SQL-linked tables
have I stumbled over?

Please post the code that you are using in the parent form's Current event
to set the subform's RecordSource. Your description makes it sound to me
like there's something wrong there.

 

[T Ray Humphrey]

Here's the line:
subProds.Form.RecordSource = "SELECT * FROM GetProds WHERE SchedID = " &
Nz(Me.SchedID, 0)

In the debugger, RecordSource is the same before and after this line of code
is run. It won't change it, and it doesn't produce an error.

GetProds is a local query that inner joins two tables that have a one-to-one
relationship on a unique ID. I can open the query in Access and see all the
records. Do you think the query should be converted to a SQL view?

Thanks,
Ray

 

[Dirk Goldgar]

Here's the line:
subProds.Form.RecordSource = "SELECT * FROM GetProds WHERE SchedID = "
&
Nz(Me.SchedID, 0)

Where, I presume, "subProds" is the name of the subform control on the form
where this code is running? I'd prefix it with "Me.", but I don't see why
that would make any practical difference.

In the debugger, RecordSource is the same before and after this line of
code
is run. It won't change it, and it doesn't produce an error.

So when you're debugging, you can display the value of
subProds.Form.RecordSource both before and after that line is executed, and
see that it is unchanged? That's very odd. You don't have error-handling
turned off, by any chance? And you have Option Explicit set at the top of
your module?

Have you verified that Me.SchedID is a valid number, so that the new
recordsource is a valid SQL statement?

GetProds is a local query that inner joins two tables that have a
one-to-one
relationship on a unique ID. I can open the query in Access and see all
the
records. Do you think the query should be converted to a SQL view?

I expect it would be more efficient, but I don't know if that would make a
difference to whatever is going wrong here.

How about testing whether SQL Server is involved at all by temporarily
renaming GetProds to something else, and creating and loading a local table
named GetProds with the same data? If the subform still doesn't work, it
probably has nothing to do with SQL Server.

 

[T Ray Humphrey]

Thanks for the response, Dirk. Below are answers to your questions, and
immediately below is what I've done since yesterday.

Based on your suggestions,
1) I first created a view in SQL called GetProds, and renamed the local
query. No change.
2) I made sure the table had matching rows (double-checked my 1-1
relationship). There were 6 rows in one table that weren't in the other, not
that it should have mattered as an INNER JOIN, but I fixed that. Data
consistency fixed. No change.
3) I then created local table that contained the result of the view,
effectively removing SQL Server and the table join from the equation. I
removed the link to the view and named the table GetProds so my SELECT * FROM
GetProds WHERE... was running against a single table in the local database.
No change.

So SQL Server is not the problem. What the heck prevents you from changing
the RecordSource property? I do it several other places in my code and it
works fine.

For example, Prods are subordinate to Scheds, Scheds to Contracts. In design
view, the Contracts form has a subform for Scheds with a RecordSource set to
"SELECT * FROM GetScheds WHERE 0=1". At runtime, this code changes
RecordSource:
subScheds.Form.RecordSource = "SELECT * FROM GetScheds WHERE ContractID = "
& Me.ContractID & _
" AND StatusID <> " & STATUS_HISTORY

But the same structure from Scheds to Prods does not work. Why not?

Thanks,
Ray

Where, I presume, "subProds" is the name of the subform control on the form
where this code is running? I'd prefix it with "Me.", but I don't see why
that would make any practical difference.

---> Yes, that is correct.

So when you're debugging, you can display the value of
subProds.Form.RecordSource both before and after that line is executed, and
see that it is unchanged? That's very odd. You don't have error-handling
turned off, by any chance? And you have Option Explicit set at the top of
your module?

Have you verified that Me.SchedID is a valid number, so that the new
recordsource is a valid SQL statement?

----> Yes, it's very odd, and yes, Me.SchedID is a valid number. I can even
take the string and create a new query and run it, and 1 record is returned.

 

[T Ray Humphrey]

Okay, here's my form hierarchy:
Contracts contains Scheds contains Prods

I've been placing the change to RecordSource in the Current event of the
parent to the subform. So:
Contracts sets Scheds RecordSource, which fires Sched's Current, which
sets Prods RecordSource
The nesting was causing Prods Recordsource to be set before the cascade of
events completed from setting Scheds RecordSource.

On a lark, I moved the code that set Prods RecordSource from the Sched level
up to the Contract level like this:
subScheds.Form.RecordSource = "SELECT * FROM GetScheds WHERE ContractID =
" & Me!ContractID & " AND StatusID <> " & STATUS_HISTORY
subScheds.Form!subProds.Form.RecordSource = "SELECT * FROM GetProds WHERE
SchedID = " & Nz(subScheds.Form!SchedID, 0)

And suddenly I was able to change Prods RecordSource!

I wonder if the "SELECT * FROM GetProds WHERE 0=1" is causing the whole
problem. I'm using that model throughout the form hierarchy. I got the idea
from an MS article about upsizing Access to SQL. Before that, I was using
"SELECT * FROM GetProds" at design time and changing RecordSource at runtime.
At that time, everything was working, if a bit heavy on Current events.

My new idea is to unbind the form's RecordSource at design time, update my
code with Me! references, and see if that helps. I'll report my success or
failure.

Has anyone else used "SELECT * FROM tbl WHERE 0=1" in a nested form hierarchy?

 

[T Ray Humphrey]

Okay, here's my form hierarchy:
Contracts contains Scheds contains Prods

I've been placing the change to RecordSource in the Current event of the
parent to the subform. So:
Contracts sets Scheds RecordSource, which fires Sched's Current, which
sets Prods RecordSource
The nesting was causing Prods Recordsource to be set before the cascade of
events completed from setting Scheds RecordSource.

On a lark, I moved the code that set Prods RecordSource from the Sched level
up to the Contract level like this:
subScheds.Form.RecordSource = "SELECT * FROM GetScheds WHERE ContractID =
" & Me!ContractID & " AND StatusID <> " & STATUS_HISTORY
subScheds.Form!subProds.Form.RecordSource = "SELECT * FROM GetProds WHERE
SchedID = " & Nz(subScheds.Form!SchedID, 0)

And suddenly I was able to change Prods RecordSource!

I wonder if the "SELECT * FROM GetProds WHERE 0=1" is causing the whole
problem. I'm using that model throughout the form hierarchy. I got the idea
from an MS article about upsizing Access to SQL. Before that, I was using
"SELECT * FROM GetProds" at design time and changing RecordSource at runtime.
At that time, everything was working, if a bit heavy on Current events.

My new idea is to unbind the form's RecordSource at design time, update my
code with Me! references, and see if that helps. I'll report my success or
failure.

Has anyone else used "SELECT * FROM tbl WHERE 0=1" in a nested form hierarchy?