Unbound form's recordsource property changes
Frank Kolbe
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?
lyle
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 ....
Frank
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.
lyle
We might be able to suggest a solution if you post the procedure code
in which you set the subform's recordsource.
Frank Kolbe
I have eliminated the issue by ensuring that all possible errors are trapped
and this has solved the problem for now.
Below is the code from one of the forms:
Public Sub AppStart()
Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
Dim rs As ADODB.Recordset
Set cmd = New ADODB.Command
Set rs = New ADODB.Recordset
Dim AID As Long
AID = SrchApp
Call OpenConnection
Set cmd.ActiveConnection = gcnn
cmd.CommandText = "Proc_AppStart"
cmd.CommandType = adCmdStoredProc
Set prm = cmd.CreateParameter("AppId", adInteger, adParamInput, , AID)
cmd.Parameters.Append prm
With rs
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.Open cmd, Options:=adCmdStoredProc
End With
Set Forms!frmApplicationStart.Form.Recordset = rs
Set rs.ActiveConnection = Nothing
Set cmd = Nothing
Set rs = Nothing
Call ClientAddress
Forms!frmApplicationStart!sfmClientAddress.Requery
End Sub
The last two lines of code call a sub that does the same thing for another
visible subform.
The form has several tabs and as a tab is selected I use the on change event
to call the appropriate sub to populate the subform on the tab with data
from a one record recordset created by a stored procedure.
It makes for a very fast application. The data is stored on a remote sql
server accessed via a vpn connection and there are no direct connections to
the database.
So only small amounts of data are passed back and forth between the client
and the server.
Every once in a while someone would click a submit button without selecting
a value form a combo box and it would throw an error and then when the usre
tries to select the tab again they would get another error because the
recordsource property contained a reference to a stored procedure that it
cannot access because the connection is not open .
I was hoping someone had experienced this before and had a simple solution.
My current solution as I stated above is to ensure that there are no
untrapped errors. Of course I should never underestimate the ability of
users to come up with a way to create one.
"lyle" <lyle.fa...@gmail.com> wrote in message
news:68dfb16e-508e-4c09...@d27g2000prf.googlegroups.com...