Can't pass form to sub/function and...

[David Mueller]

I'm passing a form object to a function.

Within the function, I create a new recordset object.

When I attempt to set the new recordset to the form's recordsetclone my new
recordset inconsistently errs. "invalid object/no longer set"

The inconsistency of the error seems to be directly related to a
not-so-obviously related user action (ODBC preconnect to Oracle). If the
user chooses to login to Oracle via my login form, I get the error. If the
user skips the Oracle login, no error.

I verified that I am cleaning up (closing objects/set to nothing) objects
related to the oracle login. I even renamed them all so there are no
similarly named objects.

Anyone have suggestions?

Thanks

 

[Klatuu]

It would be helpful if we could see the code for the function and the code
where you call the function.

 

[David Mueller]

Sure...

It's called from the OnCurrent event of a subform that is master/child
linked with a main form.

Private Sub Form_Current()
Set_GUI_Indicator Me, "Customer_ID"
End Sub



Public Function Set_GUI_Indicator(MyForm As Form, strCustomerIDField As
String) As Boolean
On Error GoTo Err_Set_GUI_Indicator

Dim ctlEstimated As Control

Dim rstEstData As DAO.Recordset
Dim rstEstDataOpen As Boolean
Dim strSubFormControlName As String

strSubFormControlName = DLookup("[Sub_Form_Control_Name]",
"[SETUP_MS_FORMS]", "FORM_NAME='" & MyForm.Name & "'")

Set rstEstData = MyForm.RecordsetClone
rstEstDataOpen = True
rstEstData.FindFirst strCustomerIDField & " = " &
Nz(Forms("Frm_main").Controls(strSubFormControlName).Form.Controls(strCustomerIDField).Value, "0")
......

..... immediately after the set command the Watch window shows it as
invalid/not set. The .FindFirst is where it errs.


Other attempts include...
strRecordSourceSQL =
Forms("frm_main").Controls(strSubFormControlName).Form.RecordSource
Set rstEstData = CurrentDb.OpenRecordset(strRecordSourceSQL)

and ...
Set rstEstData =
Forms("frm_main").Controls(strSubFormControlName).Form.RecordsetClone


I hope y'all see something in this.

 

[Klatuu]

I don't see a problem with the code. In that the error is intermittent, my
guess is that it is related to the fact that you are using a connection to
Oracle.

I'm sorry, but I don't have any experience using Oracle, so I guess there is
not much else I can do.

Sure...

It's called from the OnCurrent event of a subform that is master/child
linked with a main form.

Private Sub Form_Current()
Set_GUI_Indicator Me, "Customer_ID"
End Sub



Public Function Set_GUI_Indicator(MyForm As Form, strCustomerIDField As
String) As Boolean
On Error GoTo Err_Set_GUI_Indicator

Dim ctlEstimated As Control

Dim rstEstData As DAO.Recordset
Dim rstEstDataOpen As Boolean
Dim strSubFormControlName As String

strSubFormControlName = DLookup("[Sub_Form_Control_Name]",
"[SETUP_MS_FORMS]", "FORM_NAME='" & MyForm.Name & "'")

Set rstEstData = MyForm.RecordsetClone
rstEstDataOpen = True
rstEstData.FindFirst strCustomerIDField & " = " &
Nz(Forms("Frm_main").Controls(strSubFormControlName).Form.Controls(strCustomerIDField).Value, "0")
.....

.... immediately after the set command the Watch window shows it as
invalid/not set. The .FindFirst is where it errs.


Other attempts include...
strRecordSourceSQL =
Forms("frm_main").Controls(strSubFormControlName).Form.RecordSource
Set rstEstData = CurrentDb.OpenRecordset(strRecordSourceSQL)

and ...
Set rstEstData =
Forms("frm_main").Controls(strSubFormControlName).Form.RecordsetClone


I hope y'all see something in this.

It would be helpful if we could see the code for the function and the code
where you call the function.

 

[David Mueller]

Thanks for the second pair of eyes.

I found a known error with similar Recordset/RecordsetClone behavior in the
MS knowledge base ... but it referred to ACC2, not ACC2000/2002.

I'll work the login form some more and see if that doesn't change anything.

I don't see a problem with the code. In that the error is intermittent, my
guess is that it is related to the fact that you are using a connection to
Oracle.

I'm sorry, but I don't have any experience using Oracle, so I guess there is
not much else I can do.

Sure...

It's called from the OnCurrent event of a subform that is master/child
linked with a main form.

Private Sub Form_Current()
Set_GUI_Indicator Me, "Customer_ID"
End Sub



Public Function Set_GUI_Indicator(MyForm As Form, strCustomerIDField As
String) As Boolean
On Error GoTo Err_Set_GUI_Indicator

Dim ctlEstimated As Control

Dim rstEstData As DAO.Recordset
Dim rstEstDataOpen As Boolean
Dim strSubFormControlName As String

strSubFormControlName = DLookup("[Sub_Form_Control_Name]",
"[SETUP_MS_FORMS]", "FORM_NAME='" & MyForm.Name & "'")

Set rstEstData = MyForm.RecordsetClone
rstEstDataOpen = True
rstEstData.FindFirst strCustomerIDField & " = " &
Nz(Forms("Frm_main").Controls(strSubFormControlName).Form.Controls(strCustomerIDField).Value, "0")
.....

.... immediately after the set command the Watch window shows it as
invalid/not set. The .FindFirst is where it errs.


Other attempts include...
strRecordSourceSQL =
Forms("frm_main").Controls(strSubFormControlName).Form.RecordSource
Set rstEstData = CurrentDb.OpenRecordset(strRecordSourceSQL)

and ...
Set rstEstData =
Forms("frm_main").Controls(strSubFormControlName).Form.RecordsetClone


I hope y'all see something in this.

It would be helpful if we could see the code for the function and the code
where you call the function.

:


I'm passing a form object to a function.

Within the function, I create a new recordset object.

When I attempt to set the new recordset to the form's recordsetclone my new
recordset inconsistently errs. "invalid object/no longer set"

The inconsistency of the error seems to be directly related to a
not-so-obviously related user action (ODBC preconnect to Oracle). If the
user chooses to login to Oracle via my login form, I get the error. If the
user skips the Oracle login, no error.

I verified that I am cleaning up (closing objects/set to nothing) objects
related to the oracle login. I even renamed them all so there are no
similarly named objects.

Anyone have suggestions?

Thanks