Unbound form"s recordsource property changes

I have an access application with a backend Sql Server. All data is passed
from the back-end (remote Sql Server) to unbound forms via stored
procedure. I then set the recordsource of the form via vba to the recordset
returned by the stored procedure. If an unexpected error occurs (I need to
add more error trapping but it is hard to imagine every scenario that can be
created by an end user) or the form is closed under certain conditions, the
recordsource property shows a reference to the stored procedure and the next
time the form loads it will generate an error as a result.

Is there a way to ensure that this does not happen?

There are two ways I thought of but not sure if they will work.

One is to set each of the form and subform properties to allow design
changes only in design view and convert the database to an mde file. I am
not sure if this will prevent access from filling in the values itself or if
it will prevent me from assigning the recordsource to the recordset in vba.
I will be converting it to an mde file anyway so no one has access to the
underlying code.

The other idea I came up with is to set the recordsource of each form to
null just before opening. This way when the form first opens there is no
recordsource and then it is assigned programmatically after it is open. I
would just need to get the timing right.

I have over 100 forms/subforms in the app and it occurs somewhat randomly so
I was hoping someone could provide some insight before I make all the
modifications in the hope that it would solve my problem. Has anyone
encountered this before and would any of these two solutions work? Any
other ideas?

Re: Unbound form"s recordsource property changes

On Dec 5, 11:20 pm, "Frank Kolbe" wrote:
> I have an access application with a backend Sql Server. All data is passed
> from the back-end (remote Sql Server) to unbound forms via stored
> procedure. I then set the recordsource of the form via vba to the recordset
> returned by the stored procedure. If an unexpected error occurs (I need to
> add more error trapping but it is hard to imagine every scenario that can be
> created by an end user) or the form is closed under certain conditions, the
> recordsource property shows a reference to the stored procedure and the next
> time the form loads it will generate an error as a result.
>
> Is there a way to ensure that this does not happen?
>
> There are two ways I thought of but not sure if they will work.
>
> One is to set each of the form and subform properties to allow design
> changes only in design view and convert the database to an mde file. I am
> not sure if this will prevent access from filling in the values itself or if
> it will prevent me from assigning the recordsource to the recordset in vba.
> I will be converting it to an mde file anyway so no one has access to the
> underlying code.
>
> The other idea I came up with is to set the recordsource of each form to
> null just before opening. This way when the form first opens there is no
> recordsource and then it is assigned programmatically after it is open. I
> would just need to get the timing right.
>
> I have over 100 forms/subforms in the app and it occurs somewhat randomly so
> I was hoping someone could provide some insight before I make all the
> modifications in the hope that it would solve my problem. Has anyone
> encountered this before and would any of these two solutions work? Any
> other ideas?

I have used what (I think) you are describing extensively !!!!!
except!!!!! that I do not make the subform a true SubForm, it's just a
form that I open on a certain event, but it's definitely a child-form
a pseudo sub-form

Private Sub cmdEnrolment_Click()
If Len(Nz(lstSchools.Column(1))) <> 0 And
Len(Nz(lstPrograms.Column(1))) <> 0 And Not Me.NewRecord Then
If SysCmd(acSysCmdGetObjectState, acForm,
"frmGetSchoolEnrolments") <> 0 Then DoCmd.Close acForm,
"frmGetSchoolEnrolments"
With Form_frmGetSchoolEnrolments
.RecordSource = "SELECT * FROM tblSchoolEnrolments e JOIN
tblGrades g ON e.fldGradeID = g.fldGradeID WHERE e.fldSchoolID = " &
Me.lstSchools.Column(0) & " AND e.fldProgramID = " &
Me.lstPrograms.Column(0) & " ORDER BY g.fldSequence"
.Caption = lstSchools.Column(1) & " / " &
lstPrograms.Column(1)
.txtProgramID.DefaultValue = Me.lstPrograms.Column(0)
.txtSchoolID.DefaultValue = Me.lstSchools.Column(0)
.Visible = True
End With
Else
MsgBox "Please, select a school and program.", vbOKOnly,
"FFDBA"
End If
End Sub

Notes. I use Form_FormName to point to the pseudo SubForm. This
requires that the pseudo sub form has s module, or has its
"HasModule" (Access >=2000) Property set to True.
The pseudo subform contains opening code that positions it, usually
immediately to the right of its pseudo parent form. I've never checked
to see if Access saves the RecordSource but I'm 99.44% sure it does
not. And if it did, it would not make any substantive difference, as
the recordsource is always set new upon opening.
It's not necessary to close and repoen the pseudo subform. A Requery
often suffices.

Another:
Const mTSQL As String = "SELECT tt.fldTeacherID , tt.fldSchoolID,
tt.fldTeacherName FROM tblTeachers tt WHERE tt.fldSchoolID = @SchoolID
ORDER BY tt.fldTeacherName"
Private Sub cmdTeachers_Click()
On Error Resume Next
With Form_frmGetSchoolTeachers
.RecordSource = Replace(mTSQL, "@SchoolID",
CStr(txtSchoolID.Value))
.txtSchoolID.DefaultValue = txtSchoolID.Value
.Caption = Me.txtSchoolName.Value & " Teachers"
.CanEdit CBool(Me.txtSchoolID.Value = LoginID())
.Visible = True
End With
End Sub

Note. I no longer use prefixes to indicates variable type.

Pseudo subforms can have as many sub pseudo sub-forms as memory allows
(We can have children, grandchildren, great grandchildren, etc, etc.
Pseudo subforms can be opened form their parent's Current Event as
well as from an explicit event like a Click.

From frmGetSchoolTeachers module:

Private Sub Form_Current()
Dim TID As Long
On Error Resume Next
TID = Nz(Me.txtTeacherID, 0)
If TID > 0 Then
With Form_frmNixPeriods
.RecordSource = "SELECT * FROM tblNixPeriods WHERE
fldTeacherID = " & TID
.txtTeacherID.DefaultValue = TID
.Visible = True
End With
End If
End Sub

Psuedo SubFroms can position themselves on opening:

Private Sub Form_Open(Cancel As Integer)
With Me
.InsideHeight = 15 * .Detail.Height + .FormHeader.Height
Me.Width = Me.txtTeacherName.Width
End With
PlaceForm Me
End Sub

and Close their children on closing:

Private Sub Form_Close()
On Error Resume Next
With DoCmd
.Close acForm, "frmHTMLHelp"
.Close acForm, "frmNixPeriods"
End With
End Sub

The positioning code resides in a Standard Module:


Private Declare Function GetWindowRect Lib "user32" _
(ByVal hwnd As Long, lpRect As Rectangle) As Long
Private Declare Function SetFocus Lib "user32" _
(ByVal hwnd As Long) As Long
Private Declare Function SetWindowPos Lib "user32" _
(ByVal hwnd As Long, ByVal hWndInsertAfter As Long, _
ByVal X As Long, ByVal y As Long, _
ByVal cx As Long, ByVal cy As Long, _
ByVal wFlags As Long) As Long

Private Const HWND_TOP As Long = 0
Private Const HWND_TOPMOST As Long = -1
Private Const SWP_NOZORDER As Long = &H4
Private Const SWP_NOOWNERZORDER As Long = &H2

Private Type Dimensions
Width As Long
Height As Long
End Type

Private Type Rectangle
Left As Long
Top As Long
Right As Long
Bottom As Long
End Type

Public Sub PlaceForm(ByRef Form As Form, Optional ByVal Center As
Boolean, Optional Below As Boolean, Optional CallingForm As Form)

Dim AccessDimensions As Dimensions
Dim AccessRectangle As Rectangle
Dim AccessWindow As Long

Dim CallingFormDimensions As Dimensions
Dim CallingFormRectangle As Rectangle
Dim CallingFormWindow As Long

Dim FormDimensions As Dimensions
Dim FormRectangle As Rectangle
Dim FormWindow As Long

Dim WindowStyleInformation As Long

Dim Slack As Long

On Error Resume Next

AccessWindow = hWndAccessApp
GetWindowRect AccessWindow, AccessRectangle
With AccessRectangle
AccessDimensions.Width = .Right - .Left
AccessDimensions.Height = .Bottom - .Top
End With

If CallingForm Is Nothing Then Set CallingForm =
Application.Screen.ActiveForm
If Not CallingForm Is Nothing Then
CallingFormWindow = CallingForm.hwnd
GetWindowRect CallingFormWindow, CallingFormRectangle
With CallingFormRectangle
CallingFormDimensions.Width = .Right - .Left
CallingFormDimensions.Height = .Bottom - .Top
End With
End If

FormWindow = Form.hwnd
GetWindowRect FormWindow, FormRectangle
With FormRectangle
FormDimensions.Width = .Right - .Left
FormDimensions.Height = .Bottom - .Top
End With

If Center = True Then

FormRectangle.Top = (AccessRectangle.Bottom -
FormDimensions.Height) / 2
FormRectangle.Left = (AccessRectangle.Right -
FormDimensions.Width) / 2

ElseIf Below = True Then
FormRectangle.Left = CallingFormRectangle.Left
FormRectangle.Top = CallingFormRectangle.Bottom
Else

FormRectangle.Left = CallingFormRectangle.Right
' How much room is there to the right of the form now
Slack = AccessRectangle.Right - FormRectangle.Left
' if there is not enough room on rhs
If Slack < FormDimensions.Width Then
Slack = CallingFormRectangle.Left - AccessRectangle.Left
' if there's enough room on lhs
If Slack > FormDimensions.Width Then
' move it
FormRectangle.Left = CallingFormRectangle.Left -
FormDimensions.Width
End If
End If

FormRectangle.Top = CallingFormRectangle.Top
' How much room is there below the form now
Slack = AccessRectangle.Bottom - FormRectangle.Top
' if there is not enough room on rhs
If Slack < FormDimensions.Height Then
' move it up until so there is enough room
FormRectangle.Top = AccessRectangle.Bottom -
FormDimensions.Height
End If
' but in any case don't move it out of the access window
If FormRectangle.Top < AccessRectangle.Top Then
FormRectangle.Top = AccessRectangle.Top
End If
End If
SetWindowPos FormWindow, HWND_TOP, FormRectangle.Left,
FormRectangle.Top, FormDimensions.Width, FormDimensions.Height,
SWP_NOZORDER
End Sub

Private Sub SizeAccessWindow640480()
SetWindowPos hWndAccessApp, HWND_TOP, 0, 0, 640, 480, SWP_NOZORDER
End Sub

Note: Some of the API calls in the last snippet may be redundant in
Access >=2003.

Note: One might think this is slow. It is not. One might think it's
complicated. After a few times. It's just second nature and flows
easily from the keyboard.

And a non-subform is a much more powerful item than a subform. For
starters, it can be moved, sized, minimized, maximized, etc.

And if we create our own ADODB recordset as its recordset ....

Re: Unbound form"s recordsource property changes

lyle wrote:

> On Dec 5, 11:20 pm, "Frank Kolbe" wrote:
>> I have an access application with a backend Sql Server. All data is
>> passed
>> from the back-end (remote Sql Server) to unbound forms via stored
>> procedure. I then set the recordsource of the form via vba to the
>> recordset
>> returned by the stored procedure. If an unexpected error occurs (I need
>> to add more error trapping but it is hard to imagine every scenario that
>> can be created by an end user) or the form is closed under certain
>> conditions, the recordsource property shows a reference to the stored
>> procedure and the next time the form loads it will generate an error as a
>> result.
>>
>> Is there a way to ensure that this does not happen?
>>
>> There are two ways I thought of but not sure if they will work.
>>
>> One is to set each of the form and subform properties to allow design
>> changes only in design view and convert the database to an mde file. I
>> am not sure if this will prevent access from filling in the values itself
>> or if it will prevent me from assigning the recordsource to the recordset
>> in vba. I will be converting it to an mde file anyway so no one has
>> access to the underlying code.
>>
>> The other idea I came up with is to set the recordsource of each form to
>> null just before opening. This way when the form first opens there is no
>> recordsource and then it is assigned programmatically after it is open.
>> I would just need to get the timing right.
>>
>> I have over 100 forms/subforms in the app and it occurs somewhat randomly
>> so I was hoping someone could provide some insight before I make all the
>> modifications in the hope that it would solve my problem. Has anyone
>> encountered this before and would any of these two solutions work? Any
>> other ideas?
>
> I have used what (I think) you are describing extensively !!!!!
> except!!!!! that I do not make the subform a true SubForm, it's just a
> form that I open on a certain event, but it's definitely a child-form
> a pseudo sub-form
>
> Private Sub cmdEnrolment_Click()
> If Len(Nz(lstSchools.Column(1))) <> 0 And
> Len(Nz(lstPrograms.Column(1))) <> 0 And Not Me.NewRecord Then
> If SysCmd(acSysCmdGetObjectState, acForm,
> "frmGetSchoolEnrolments") <> 0 Then DoCmd.Close acForm,
> "frmGetSchoolEnrolments"
> With Form_frmGetSchoolEnrolments
> .RecordSource = "SELECT * FROM tblSchoolEnrolments e JOIN
> tblGrades g ON e.fldGradeID = g.fldGradeID WHERE e.fldSchoolID = " &
> Me.lstSchools.Column(0) & " AND e.fldProgramID = " &
> Me.lstPrograms.Column(0) & " ORDER BY g.fldSequence"
> .Caption = lstSchools.Column(1) & " / " &
> lstPrograms.Column(1)
> .txtProgramID.DefaultValue = Me.lstPrograms.Column(0)
> .txtSchoolID.DefaultValue = Me.lstSchools.Column(0)
> .Visible = True
> End With
> Else
> MsgBox "Please, select a school and program.", vbOKOnly,
> "FFDBA"
> End If
> End Sub
>
> Notes. I use Form_FormName to point to the pseudo SubForm. This
> requires that the pseudo sub form has s module, or has its
> "HasModule" (Access >=2000) Property set to True.
> The pseudo subform contains opening code that positions it, usually
> immediately to the right of its pseudo parent form. I've never checked
> to see if Access saves the RecordSource but I'm 99.44% sure it does
> not. And if it did, it would not make any substantive difference, as
> the recordsource is always set new upon opening.
> It's not necessary to close and repoen the pseudo subform. A Requery
> often suffices.
>
> Another:
> Const mTSQL As String = "SELECT tt.fldTeacherID , tt.fldSchoolID,
> tt.fldTeacherName FROM tblTeachers tt WHERE tt.fldSchoolID = @SchoolID
> ORDER BY tt.fldTeacherName"
> Private Sub cmdTeachers_Click()
> On Error Resume Next
> With Form_frmGetSchoolTeachers
> .RecordSource = Replace(mTSQL, "@SchoolID",
> CStr(txtSchoolID.Value))
> .txtSchoolID.DefaultValue = txtSchoolID.Value
> .Caption = Me.txtSchoolName.Value & " Teachers"
> .CanEdit CBool(Me.txtSchoolID.Value = LoginID())
> .Visible = True
> End With
> End Sub
>
> Note. I no longer use prefixes to indicates variable type.
>
> Pseudo subforms can have as many sub pseudo sub-forms as memory allows
> (We can have children, grandchildren, great grandchildren, etc, etc.
> Pseudo subforms can be opened form their parent's Current Event as
> well as from an explicit event like a Click.
>
> From frmGetSchoolTeachers module:
>
> Private Sub Form_Current()
> Dim TID As Long
> On Error Resume Next
> TID = Nz(Me.txtTeacherID, 0)
> If TID > 0 Then
> With Form_frmNixPeriods
> .RecordSource = "SELECT * FROM tblNixPeriods WHERE
> fldTeacherID = " & TID
> .txtTeacherID.DefaultValue = TID
> .Visible = True
> End With
> End If
> End Sub
>
> Psuedo SubFroms can position themselves on opening:
>
> Private Sub Form_Open(Cancel As Integer)
> With Me
> .InsideHeight = 15 * .Detail.Height + .FormHeader.Height
> Me.Width = Me.txtTeacherName.Width
> End With
> PlaceForm Me
> End Sub
>
> and Close their children on closing:
>
> Private Sub Form_Close()
> On Error Resume Next
> With DoCmd
> .Close acForm, "frmHTMLHelp"
> .Close acForm, "frmNixPeriods"
> End With
> End Sub
>
> The positioning code resides in a Standard Module:
>
>
> Private Declare Function GetWindowRect Lib "user32" _
> (ByVal hwnd As Long, lpRect As Rectangle) As Long
> Private Declare Function SetFocus Lib "user32" _
> (ByVal hwnd As Long) As Long
> Private Declare Function SetWindowPos Lib "user32" _
> (ByVal hwnd As Long, ByVal hWndInsertAfter As Long, _
> ByVal X As Long, ByVal y As Long, _
> ByVal cx As Long, ByVal cy As Long, _
> ByVal wFlags As Long) As Long
>
> Private Const HWND_TOP As Long = 0
> Private Const HWND_TOPMOST As Long = -1
> Private Const SWP_NOZORDER As Long = &H4
> Private Const SWP_NOOWNERZORDER As Long = &H2
>
> Private Type Dimensions
> Width As Long
> Height As Long
> End Type
>
> Private Type Rectangle
> Left As Long
> Top As Long
> Right As Long
> Bottom As Long
> End Type
>
> Public Sub PlaceForm(ByRef Form As Form, Optional ByVal Center As
> Boolean, Optional Below As Boolean, Optional CallingForm As Form)
>
> Dim AccessDimensions As Dimensions
> Dim AccessRectangle As Rectangle
> Dim AccessWindow As Long
>
> Dim CallingFormDimensions As Dimensions
> Dim CallingFormRectangle As Rectangle
> Dim CallingFormWindow As Long
>
> Dim FormDimensions As Dimensions
> Dim FormRectangle As Rectangle
> Dim FormWindow As Long
>
> Dim WindowStyleInformation As Long
>
> Dim Slack As Long
>
> On Error Resume Next
>
> AccessWindow = hWndAccessApp
> GetWindowRect AccessWindow, AccessRectangle
> With AccessRectangle
> AccessDimensions.Width = .Right - .Left
> AccessDimensions.Height = .Bottom - .Top
> End With
>
> If CallingForm Is Nothing Then Set CallingForm =
> Application.Screen.ActiveForm
> If Not CallingForm Is Nothing Then
> CallingFormWindow = CallingForm.hwnd
> GetWindowRect CallingFormWindow, CallingFormRectangle
> With CallingFormRectangle
> CallingFormDimensions.Width = .Right - .Left
> CallingFormDimensions.Height = .Bottom - .Top
> End With
> End If
>
> FormWindow = Form.hwnd
> GetWindowRect FormWindow, FormRectangle
> With FormRectangle
> FormDimensions.Width = .Right - .Left
> FormDimensions.Height = .Bottom - .Top
> End With
>
> If Center = True Then
>
> FormRectangle.Top = (AccessRectangle.Bottom -
> FormDimensions.Height) / 2
> FormRectangle.Left = (AccessRectangle.Right -
> FormDimensions.Width) / 2
>
> ElseIf Below = True Then
> FormRectangle.Left = CallingFormRectangle.Left
> FormRectangle.Top = CallingFormRectangle.Bottom
> Else
>
> FormRectangle.Left = CallingFormRectangle.Right
> ' How much room is there to the right of the form now
> Slack = AccessRectangle.Right - FormRectangle.Left
> ' if there is not enough room on rhs
> If Slack < FormDimensions.Width Then
> Slack = CallingFormRectangle.Left - AccessRectangle.Left
> ' if there's enough room on lhs
> If Slack > FormDimensions.Width Then
> ' move it
> FormRectangle.Left = CallingFormRectangle.Left -
> FormDimensions.Width
> End If
> End If
>
> FormRectangle.Top = CallingFormRectangle.Top
> ' How much room is there below the form now
> Slack = AccessRectangle.Bottom - FormRectangle.Top
> ' if there is not enough room on rhs
> If Slack < FormDimensions.Height Then
> ' move it up until so there is enough room
> FormRectangle.Top = AccessRectangle.Bottom -
> FormDimensions.Height
> End If
> ' but in any case don't move it out of the access window
> If FormRectangle.Top < AccessRectangle.Top Then
> FormRectangle.Top = AccessRectangle.Top
> End If
> End If
> SetWindowPos FormWindow, HWND_TOP, FormRectangle.Left,
> FormRectangle.Top, FormDimensions.Width, FormDimensions.Height,
> SWP_NOZORDER
> End Sub
>
> Private Sub SizeAccessWindow640480()
> SetWindowPos hWndAccessApp, HWND_TOP, 0, 0, 640, 480, SWP_NOZORDER
> End Sub
>
> Note: Some of the API calls in the last snippet may be redundant in
> Access >=2003.
>
> Note: One might think this is slow. It is not. One might think it's
> complicated. After a few times. It's just second nature and flows
> easily from the keyboard.
>
> And a non-subform is a much more powerful item than a subform. For
> starters, it can be moved, sized, minimized, maximized, etc.
>
> And if we create our own ADODB recordset as its recordset ....


I appreciate the pointers but my issue is really that I need a way to
prevent Access from setting the recordsource of an unbound form to the
stored procedure and retaining it. Setting each form's recordsource to
blank or null prior to loading generated errors stating I couldn't do that
(not supported). That may be a timing issue where the form is
not "present" when I am assigning its recordsource.

What appears to be happening now is that the form loads and the recordsource
is set to a recordset generated by a stored procedure on a remote sql
server. I have a number of subforms that are on tabs on a form so that the
user can navigate between tabs to see categories of info. Each time they
click on a tab the subforms underlying data is refreshed. There are about
fifteen tabs and each tab has at least 10-20 controls on it. This way when
the form is initially opened, only the header info and the first tab are
loaded and each subsequent tab's subform is loaded when selected.

From time to time when the subform is reloaded it already has some info
about the SP in it's recordsource property but the connection is not open
yet to the remote sql server so it generates an error because it cannot
access the SP.

If I could come up with some code that "Flushed" all the recordsource info
from all of my forms and subforms just when they load or when another form
is "activated" that would help. Or possibly, I could adjust the timing of
the opening of the connection and that might help but I am not sure at what
point I should open it.

Re: Unbound form"s recordsource property changes

On Dec 7, 3:59 pm, Frank wrote:
> lyle wrote:
> > On Dec 5, 11:20 pm, "Frank Kolbe" wrote:
> >> I have an access application with a backend Sql Server. All data is
> >> passed
> >> from the back-end (remote Sql Server) to unbound forms via stored
> >> procedure. I then set the recordsource of the form via vba to the
> >> recordset
> >> returned by the stored procedure. If an unexpected error occurs (I need
> >> to add more error trapping but it is hard to imagine every scenario that
> >> can be created by an end user) or the form is closed under certain
> >> conditions, the recordsource property shows a reference to the stored
> >> procedure and the next time the form loads it will generate an error as a
> >> result.
>
> >> Is there a way to ensure that this does not happen?
>
> >> There are two ways I thought of but not sure if they will work.
>
> >> One is to set each of the form and subform properties to allow design
> >> changes only in design view and convert the database to an mde file. I
> >> am not sure if this will prevent access from filling in the values itself
> >> or if it will prevent me from assigning the recordsource to the recordset
> >> in vba. I will be converting it to an mde file anyway so no one has
> >> access to the underlying code.
>
> >> The other idea I came up with is to set the recordsource of each form to
> >> null just before opening. This way when the form first opens there is no
> >> recordsource and then it is assigned programmatically after it is open.
> >> I would just need to get the timing right.
>
> >> I have over 100 forms/subforms in the app and it occurs somewhat randomly
> >> so I was hoping someone could provide some insight before I make all the
> >> modifications in the hope that it would solve my problem. Has anyone
> >> encountered this before and would any of these two solutions work? Any
> >> other ideas?
>
> > I have used what (I think) you are describing extensively !!!!!
> > except!!!!! that I do not make the subform a true SubForm, it's just a
> > form that I open on a certain event, but it's definitely a child-form
> > a pseudo sub-form
>
> > Private Sub cmdEnrolment_Click()
> > If Len(Nz(lstSchools.Column(1))) <> 0 And
> > Len(Nz(lstPrograms.Column(1))) <> 0 And Not Me.NewRecord Then
> > If SysCmd(acSysCmdGetObjectState, acForm,
> > "frmGetSchoolEnrolments") <> 0 Then DoCmd.Close acForm,
> > "frmGetSchoolEnrolments"
> > With Form_frmGetSchoolEnrolments
> > .RecordSource = "SELECT * FROM tblSchoolEnrolments e JOIN
> > tblGrades g ON e.fldGradeID = g.fldGradeID WHERE e.fldSchoolID = " &
> > Me.lstSchools.Column(0) & " AND e.fldProgramID = " &
> > Me.lstPrograms.Column(0) & " ORDER BY g.fldSequence"
> > .Caption = lstSchools.Column(1) & " / " &
> > lstPrograms.Column(1)
> > .txtProgramID.DefaultValue = Me.lstPrograms.Column(0)
> > .txtSchoolID.DefaultValue = Me.lstSchools.Column(0)
> > .Visible = True
> > End With
> > Else
> > MsgBox "Please, select a school and program.", vbOKOnly,
> > "FFDBA"
> > End If
> > End Sub
>
> > Notes. I use Form_FormName to point to the pseudo SubForm. This
> > requires that the pseudo sub form has s module, or has its
> > "HasModule" (Access >=2000) Property set to True.
> > The pseudo subform contains opening code that positions it, usually
> > immediately to the right of its pseudo parent form. I've never checked
> > to see if Access saves the RecordSource but I'm 99.44% sure it does
> > not. And if it did, it would not make any substantive difference, as
> > the recordsource is always set new upon opening.
> > It's not necessary to close and repoen the pseudo subform. A Requery
> > often suffices.
>
> > Another:
> > Const mTSQL As String = "SELECT tt.fldTeacherID , tt.fldSchoolID,
> > tt.fldTeacherName FROM tblTeachers tt WHERE tt.fldSchoolID = @SchoolID
> > ORDER BY tt.fldTeacherName"
> > Private Sub cmdTeachers_Click()
> > On Error Resume Next
> > With Form_frmGetSchoolTeachers
> > .RecordSource = Replace(mTSQL, "@SchoolID",
> > CStr(txtSchoolID.Value))
> > .txtSchoolID.DefaultValue = txtSchoolID.Value
> > .Caption = Me.txtSchoolName.Value & " Teachers"
> > .CanEdit CBool(Me.txtSchoolID.Value = LoginID())
> > .Visible = True
> > End With
> > End Sub
>
> > Note. I no longer use prefixes to indicates variable type.
>
> > Pseudo subforms can have as many sub pseudo sub-forms as memory allows
> > (We can have children, grandchildren, great grandchildren, etc, etc.
> > Pseudo subforms can be opened form their parent's Current Event as
> > well as from an explicit event like a Click.
>
> > From frmGetSchoolTeachers module:
>
> > Private Sub Form_Current()
> > Dim TID As Long
> > On Error Resume Next
> > TID = Nz(Me.txtTeacherID, 0)
> > If TID > 0 Then
> > With Form_frmNixPeriods
> > .RecordSource = "SELECT * FROM tblNixPeriods WHERE
> > fldTeacherID = " & TID
> > .txtTeacherID.DefaultValue = TID
> > .Visible = True
> > End With
> > End If
> > End Sub
>
> > Psuedo SubFroms can position themselves on opening:
>
> > Private Sub Form_Open(Cancel As Integer)
> > With Me
> > .InsideHeight = 15 * .Detail.Height + .FormHeader.Height
> > Me.Width = Me.txtTeacherName.Width
> > End With
> > PlaceForm Me
> > End Sub
>
> > and Close their children on closing:
>
> > Private Sub Form_Close()
> > On Error Resume Next
> > With DoCmd
> > .Close acForm, "frmHTMLHelp"
> > .Close acForm, "frmNixPeriods"
> > End With
> > End Sub
>
> > The positioning code resides in a Standard Module:
>
> > Private Declare Function GetWindowRect Lib "user32" _
> > (ByVal hwnd As Long, lpRect As Rectangle) As Long
> > Private Declare Function SetFocus Lib "user32" _
> > (ByVal hwnd As Long) As Long
> > Private Declare Function SetWindowPos Lib "user32" _
> > (ByVal hwnd As Long, ByVal hWndInsertAfter As Long, _
> > ByVal X As Long, ByVal y As Long, _
> > ByVal cx As Long, ByVal cy As Long, _
> > ByVal wFlags As Long) As Long
>
> > Private Const HWND_TOP As Long = 0
> > Private Const HWND_TOPMOST As Long = -1
> > Private Const SWP_NOZORDER As Long = &H4
> > Private Const SWP_NOOWNERZORDER As Long = &H2
>
> > Private Type Dimensions
> > Width As Long
> > Height As Long
> > End Type
>
> > Private Type Rectangle
> > Left As Long
> > Top As Long
> > Right As Long
> > Bottom As Long
> > End Type
>
> > Public Sub PlaceForm(ByRef Form As Form, Optional ByVal Center As
> > Boolean, Optional Below As Boolean, Optional CallingForm As Form)
>
> > Dim AccessDimensions As Dimensions
> > Dim AccessRectangle As Rectangle
> > Dim AccessWindow As Long
>
> > Dim CallingFormDimensions As Dimensions
> > Dim CallingFormRectangle As Rectangle
> > Dim CallingFormWindow As Long
>
> > Dim FormDimensions As Dimensions
> > Dim FormRectangle As Rectangle
> > Dim FormWindow As Long
>
> > Dim WindowStyleInformation As Long
>
> > Dim Slack As Long
>
> > On Error Resume Next
>
> > AccessWindow = hWndAccessApp
> > GetWindowRect AccessWindow, AccessRectangle
> > With AccessRectangle
> > AccessDimensions.Width = .Right - .Left
> > AccessDimensions.Height = .Bottom - .Top
> > End With
>
> > If CallingForm Is Nothing Then Set CallingForm =
> > Application.Screen.ActiveForm
> > If Not CallingForm Is Nothing Then
> > CallingFormWindow = CallingForm.hwnd
> > GetWindowRect CallingFormWindow, CallingFormRectangle
> > With CallingFormRectangle
> > CallingFormDimensions.Width = .Right - .Left
> > CallingFormDimensions.Height = .Bottom - .Top
> > End With
> > End If
>
> > FormWindow = Form.hwnd
> > GetWindowRect FormWindow, FormRectangle
> > With FormRectangle
> > FormDimensions.Width = .Right - .Left
> > FormDimensions.Height = .Bottom - .Top
> > End With
>
> > If Center = True Then
>
> > FormRectangle.Top = (AccessRectangle.Bottom -
> > FormDimensions.Height) / 2
> > FormRectangle.Left = (AccessRectangle.Right -
> > FormDimensions.Width) / 2
>
> > ElseIf Below = True Then
> > FormRectangle.Left = CallingFormRectangle.Left
> > FormRectangle.Top = CallingFormRectangle.Bottom
> > Else
>
> > FormRectangle.Left = CallingFormRectangle.Right
> > ' How much room is there to the right of the form now
> > Slack = AccessRectangle.Right - FormRectangle.Left
> > ' if there is not enough room on rhs
> > If Slack < FormDimensions.Width Then
> > Slack = CallingFormRectangle.Left - AccessRectangle.Left
> > ' if there's enough room on lhs
> > If Slack > FormDimensions.Width Then
> > ' move it
> > FormRectangle.Left = CallingFormRectangle.Left -
> > FormDimensions.Width
> > End If
> > End If
>
> > FormRectangle.Top = CallingFormRectangle.Top
> > ' How much room is there below the form now
> > Slack = AccessRectangle.Bottom - FormRectangle.Top
> > ' if there is not enough room on rhs
> > If Slack < FormDimensions.Height Then
> > ' move it up until so there is enough room
> > FormRectangle.Top = AccessRectangle.Bottom -
> > FormDimensions.Height
> > End If
> > ' but in any case don't move it out of the access window
> > If FormRectangle.Top < AccessRectangle.Top Then
> > FormRectangle.Top = AccessRectangle.Top
> > End If
> > End If
> > SetWindowPos FormWindow, HWND_TOP, FormRectangle.Left,
> > FormRectangle.Top, FormDimensions.Width, FormDimensions.Height,
> > SWP_NOZORDER
> > End Sub
>
> > Private Sub SizeAccessWindow640480()
> > SetWindowPos hWndAccessApp, HWND_TOP, 0, 0, 640, 480, SWP_NOZORDER
> > End Sub
>
> > Note: Some of the API calls in the last snippet may be redundant in
> > Access >=2003.
>
> > Note: One might think this is slow. It is not. One might think it's
> > complicated. After a few times. It's just second nature and flows
> > easily from the keyboard.
>
> > And a non-subform is a much more powerful item than a subform. For
> > starters, it can be moved, sized, minimized, maximized, etc.
>
> > And if we create our own ADODB recordset as its recordset ....
>
> I appreciate the pointers but my issue is really that I need a way to
> prevent Access from setting the recordsource of an unbound form to the
> stored procedure and retaining it. Setting each form's recordsource to
> blank or null prior to loading generated errors stating I couldn't do that
> (not supported). That may be a timing issue where the form is
> not "present" when I am assigning its recordsource.
>
> What appears to be happening now is that the form loads and the recordsource
> is set to a recordset generated by a stored procedure on a remote sql
> server. I have a number of subforms that are on tabs on a form so that the
> user can navigate between tabs to see categories of info. Each time they
> click on a tab the subforms underlying data is refreshed. There are about
> fifteen tabs and each tab has at least 10-20 controls on it. This way when
> the form is initially opened, only the header info and the first tab are
> loaded and each subsequent tab's subform is loaded when selected.
>
> From time to time when the subform is reloaded it already has some info
> about the SP in it's recordsource property but the connection is not open
> yet to the remote sql server so it generates an error because it cannot
> access the SP.
>
> If I could come up with some code that "Flushed" all the recordsource info
> from all of my forms and subforms just when they load or when another form
> is "activated" that would help. Or possibly, I could adjust the timing of
> the opening of the connection and that might help but I am not sure at what
> point I should open it.

We might be able to suggest a solution if you post the procedure code
in which you set the subform's recordsource.