Form code fails when Openargs present...

[Fred Boer]

Hello:

Well, I've been giving my debugging muscles a workout (think 98 lb weakling,
sand in the face, no date for the prom...), and I am stumped. I have a form
with a subform. It works perfectly. If I open it from another form, using
OpenArgs, I get an error message. Why?

Thanks!
Fred Boer


Details:

Error #: 2455 "You entered an expression that has an invalid reference to
the property form/report"

The following line calls the form:

DoCmd.OpenForm ("Frm_PatronCirculationInformation"), , , , , acHidden,
"Patron_ID=" & Me.cboPatronFullname

The called form has the following code:

Private Sub Form_Open(Cancel As Integer)

If Not IsNull(Me.OpenArgs) Then
With Me.RecordsetClone
.FindFirst Me.OpenArgs
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End If

Me.cboBookID.SetFocus
Me.cboBookID = Null

End Sub


Private Sub Form_Current()

Me.RecordsetClone.MoveLast
Me.cmdGoNext.Enabled = Not (Me.CurrentRecord =
Me.RecordsetClone.RecordCount)
Me.cmdGoPrevious.Enabled = Not (Me.CurrentRecord = 1)

'NEXT LINE GENERATES ERROR..........

If
Forms!Frm_BookCirculationInformation!Frm_BookCirculationInformationSubform.Form.RecordsetClone.RecordCount
= 0 Then
Forms!Frm_BookCirculationInformation!Frm_BookCirculationInformationSubform.Visible
= False
Else
Forms!Frm_BookCirculationInformation!Frm_BookCirculationInformationSubform.Visible
= True
End If

End Sub

 

[Dirk Goldgar]

Hello:

Well, I've been giving my debugging muscles a workout (think 98 lb
weakling,
sand in the face, no date for the prom...), and I am stumped. I have
a form
with a subform. It works perfectly. If I open it from another form,
using
OpenArgs, I get an error message. Why?

Thanks!
Fred Boer


Details:

Error #: 2455 "You entered an expression that has an invalid
reference to
the property form/report"

The following line calls the form:

DoCmd.OpenForm ("Frm_PatronCirculationInformation"), , , , , acHidden,
"Patron_ID=" & Me.cboPatronFullname

The called form has the following code:

Private Sub Form_Open(Cancel As Integer)

If Not IsNull(Me.OpenArgs) Then
With Me.RecordsetClone
.FindFirst Me.OpenArgs
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End If

Me.cboBookID.SetFocus
Me.cboBookID = Null

End Sub


Private Sub Form_Current()

Me.RecordsetClone.MoveLast
Me.cmdGoNext.Enabled = Not (Me.CurrentRecord =
Me.RecordsetClone.RecordCount)
Me.cmdGoPrevious.Enabled = Not (Me.CurrentRecord = 1)

'NEXT LINE GENERATES ERROR..........

If
Forms!Frm_BookCirculationInformation!Frm_BookCirculationInformationSubfo
rm.Form.RecordsetClone.RecordCount
= 0 Then

Forms!Frm_BookCirculationInformation!Frm_BookCirculationInformationSubfo
rm.Visible
= False
Else

Forms!Frm_BookCirculationInformation!Frm_BookCirculationInformationSubfo
rm.Visible
= True
End If

End Sub

Fred -

Satisfy my curiosity. The DoCmd.OpenForm statement you quote is opening
a form called "Frm_PatronCirculationInformation". The code you quote
from the form's Current event refers to
Forms!Frm_BookCirculationInformation. That's not the form you opened,
and not the form the code is running behind, so is it an error, or are
you intending to refer to another form?

 

[Fred Boer]

Dear Dirk:

My apologies. It was an error in cutting and pasting. I have two forms
(Frm_BookCirculationInformation and Frm_PatronCirculationInformation) which
are virtually identical. I was careless...

This is the correct code:

Private Sub Form_Current()

Me.RecordsetClone.MoveLast
Me.cmdGoNext.Enabled = Not (Me.CurrentRecord =
Me.RecordsetClone.RecordCount)
Me.cmdGoPrevious.Enabled = Not (Me.CurrentRecord = 1)

'line below generates error....

If
Forms!Frm_PatronCirculationInformation!Frm_PatronCirculationInformationSubform.Form.RecordsetClone.RecordCount
= 0 Then
Me.Frm_PatronCirculationInformationSubform.Visible = False
Else
Me.Frm_PatronCirculationInformationSubform.Visible = True
End If

Me.Recalc

If Me.txtPatronType_ID <> 0 Then
Me.txtStaffFullname.Visible = False
Else
Me.txtStaffFullname.Visible = True
End If

End Sub

 

[Fred Boer]

I have been banging away at this over the last two days. My latest debugging
attempt has been to create competely new, bare-bones forms and try to
reproduce the problem. I have discovered the following:

If I use the "where" clause (as below), no error is generated.

e.g. DoCmd.OpenForm ("Tbl_Library"), , , "Book_ID=" & 456

If I use OpenArgs, but don't specify a criteria which would return a single
record, it *also* works?!

e.g. DoCmd.OpenForm ("Tbl_Library"), , , , , , "Book_ID>" & 456

If I use OpenArgs and specify a criteria which would return a single
record... error...

e.g. DoCmd.OpenForm ("Tbl_Library"), , , , , , "Book_ID=" & 456


Experimentation shows that this behaviour is reproduced in my actual
database application as well...

Cheers!
Fred

 

[Ken Snell [MVP]]

Fred -

Did you try this:

DoCmd.OpenForm ("Tbl_Library"), , , , , , "Book_ID=456"

Pass the actual expression as a complete string in the OpenArgs string.
--

Ken Snell
<MS ACCESS MVP>

 

[Dirk Goldgar]

I have been banging away at this over the last two days. My latest
debugging attempt has been to create competely new, bare-bones forms
and try to reproduce the problem. I have discovered the following:

If I use the "where" clause (as below), no error is generated.

e.g. DoCmd.OpenForm ("Tbl_Library"), , , "Book_ID=" & 456

If I use OpenArgs, but don't specify a criteria which would return a
single record, it *also* works?!

e.g. DoCmd.OpenForm ("Tbl_Library"), , , , , , "Book_ID>" & 456

If I use OpenArgs and specify a criteria which would return a single
record... error...

e.g. DoCmd.OpenForm ("Tbl_Library"), , , , , , "Book_ID=" & 456


Experimentation shows that this behaviour is reproduced in my actual
database application as well...

Okay, I've been able to reproduce it. What I find, though, is that it
doesn't cause an error if no record is found to meet the criteria, and
so the form's recordset isn't repositioned. If the form's recordset
*is* repositioned in the Open event, then the error occurs. I'll offer
a conjecture on why it happens in a moment, but the solution -- for me,
at least -- is to move the code from the form's Open event to the Load
event. That makes sense to do anyway, since your code searches the
form's recordsetclone and moves the form's recordset, and the recordset
isn't normally even loaded yet in the form's Open event.

Here's my vague conjecture about what's happening, for what it's worth.
The normal sequence of events for a form/subform combination like this
would be: Subform Open, Subform Load, Subform Current, Main Form Open,
Main Form Load, Main Form Current, Subform Current. Stepping through
with this code arrangement, the sequence is: Subform Open, Subform
Load, Subform Current, Main Form Open *up to setting of bookmark*, Main
Form Load, Main Form Current -- *error here in reference to
subform.Form, but if you step past it you get ...* , Subform Current
(more than once), *remainder of Main Form Open event*.

Clearly, setting the form's bookmark in the form's load event is causing
various events to fire out of the normal sequence, as Access tries to
catch up with you ("Oh, gosh! He wants the recordset! We'd better get
him the recordset!"). My *guess* is that, since the form's Open event
hasn't completed yet when the Current event fires (with this code of
yours), the subform control has not yet been "connected up" to the form
object that was loaded for it to display. The subform control's Form
property has not yet been set to point to the form that is its source
object. The property doesn't appear to be Nothing, either, so I'm not
sure what its status is.

But if you don't try to reposition the form until its Open event is
complete, I don't think you'll have this problem.

 

[Dirk Goldgar]

Hi Dirk:

Thanks for taking time to help me! I certainly feel bad to have
stressed poor Access, and didn't mean to make it feel overwhelmed!

I've tried putting the OpenArgs/Find process in the Load event,
rather than the Open event, but I still get the error message. To be
specific, I get the error message, and then when I clear the error
message, the form opens positioned to the correct record. It's like
Acess is saying "Wait, you did this wrong!", but then it works anyway.

Now, I'm using the recordsetclone of the subform, but I don't know if
I need that. I have tried just using the recordset.recordcount of the
subform, in case that helped, but it didn't seem to...

I haven't mentioned that I am working with linked back end tables.
That wouldn't be an issue would it? The OpenArgs code uses
.FindFirst...

Still puzzling...

Hmm, moving the code to the Load event fixed the problem for me. Are
you sure you didn't leave the code in the Open event, as well as copying
it to the Load event? Please post the complete code, as it is now, from
the form's Open, Load, and Current events.

Are the linked tables in a Jet database, or are they in an ODBC data
source like SQL Server or MSDE?

 

[Dirk Goldgar]

Hi Dirk:

Connecting to JET tables...

This line calls the form:

DoCmd.OpenForm ("Frm_PatronCirculationInformation"), , , , , ,
"Patron_ID=" & Me.cboPatronFullname


From Frm_PatronCirculationInformation:

Private Sub Form_Current()
On Error GoTo Err_Form_Current

Me.RecordsetClone.MoveLast
Me.cmdGoNext.Enabled = Not (Me.CurrentRecord =
Me.RecordsetClone.RecordCount)
Me.cmdGoPrevious.Enabled = Not (Me.CurrentRecord = 1)

If
Forms!Frm_PatronCirculationInformation!Frm_PatronCirculationInformationS
ubfo
rm.Form.RecordCount = 0 Then
Me.Frm_PatronCirculationInformationSubform.Visible = False
Else
Me.Frm_PatronCirculationInformationSubform.Visible = True
End If

Me.Recalc

If Me.txtPatronType_ID <> 0 Then
Me.txtStaffFullname.Visible = False
Else
Me.txtStaffFullname.Visible = True
End If


Exit_Form_Current:
Exit Sub

Err_Form_Current:
fncWRMSErrMsg Err.Number, Err.Description
Resume Exit_Form_Current

End Sub

Private Sub Form_Load()
If Not IsNull(Me.OpenArgs) Then
With Me.RecordsetClone
.FindFirst Me.OpenArgs
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End If
End Sub

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Form_Open

'Call fncLoadcbo([Form])



DoCmd.Maximize


Exit_Form_Open:
Exit Sub

Err_Form_Open:
fncWRMSErrMsg Err.Number, Err.Description
Resume Exit_Form_Open

End Sub



That's the code. Throws 2455. And it doesn't reposition correctly
after dismissing error message. No luck with similar changes in my
"barebones" test forms either. Sure hope I'm not doing something
stupid, but I can't see anything...

In this case, I'd expect this line:

If
Forms!Frm_PatronCirculationInformation!Frm_PatronCirculationInformationS
ubfo
rm.Form.RecordCount = 0 Then

to throw an error, because RecordCount isn't a property of a Form
object. Does it make a difference if you write this instead?

If
Forms!Frm_PatronCirculationInformation!Frm_PatronCirculationInformationS
ubform.Form.Recordset.RecordCount = 0 Then

(or the simpler ...

If
Me!Frm_PatronCirculationInformationSubform.Form.Recordset.RecordCount =
0 Then

)?

 

[Dirk Goldgar]

Hi Dirk

Changed to:

If
Me!Frm_PatronCirculationInformationSubform.Form.Recordset.RecordCount
= 0 Then Me.Frm_PatronCirculationInformationSubform.Visible =
False
Else
Me.Frm_PatronCirculationInformationSubform.Visible = True
End If


Still throws error message. BTW, slight correction to my earlier
statement: After dismissing error message, form *does* reposition
correctly.

Interesting. The DoCmd.Maximize statement in the Open event makes the
difference. You might try moving that to the form's Activate event, and
in the meanwhile I'll see what the sequence of events is now.

 

[Dirk Goldgar]

Interesting. The DoCmd.Maximize statement in the Open event makes the
difference. You might try moving that to the form's Activate event,
and in the meanwhile I'll see what the sequence of events is now.

Here's the sequence of relevant events when my test form's Open event
doesn't contain the statement DoCmd.Maximize:

docmd.OpenForm "tMain_Boer",,,,,, "ID=3"
tMain_Boer enter Open
tMain_Boer leave Open
tMain_Boer enter Load
tMain_Boer enter Activate
tMain_Boer leave Activate
tMain_Boer enter Current
tMain_Boer leave Current
tMain_Boer leave Load
tMain_Boer enter Activate
tMain_Boer leave Activate
tMain_Boer enter Current
tMain_Boer leave Current

Here's the sequence of relevant events when my test form's Open event
*does* contain the statement DoCmd.Maximize:

docmd.OpenForm "tMain_Boer",,,,,, "ID=3"
tMain_Boer enter Open
tMain_Boer enter Load
tMain_Boer enter Activate
tMain_Boer leave Activate
tMain_Boer enter Current
tMain_Boer leave Current
tMain_Boer leave Load
tMain_Boer enter Activate
tMain_Boer leave Activate
tMain_Boer leave Open

Notice that the form's Open event doesn't finish until long after the
Current event. Hmm ...

 

[Fred Boer]

You did it Dirk! Yes, if I move the Docmd.Maximize to the Activate event,
the error evaporates.

Man, if it isn't one thing, it's another!

Fred

 

[Fred Boer]

Dear Dirk:

Well, this is delving further into the event sequences than I've ever
imagined going... I feel like a character in Verne's "Journey to the Centre
of the Earth"! I'll spend time tomorrow studying your findings. For
tonight, I'm just grateful to have the problem solved, and to know that it
wasn't a silly mistake on my part!

Thank you very much for your help, Dirk!

Fred

P.S. Or maybe I feel more like a character in "The Fantastic Voyage", diving
deep, deep, deeper into the unknown recesses of the human bloodstream...

 

[Fred Boer]

Dear Ken:

That didn't seem to make a difference. I also tried just passing the Book_ID
as the argument, and having the "Book_ID="& built into the .FindFirst
statement.

No better luck.

Thanks!
Fred

 

[Fred Boer]

Hi Dirk:

Thanks for taking time to help me! I certainly feel bad to have stressed
poor Access, and didn't mean to make it feel overwhelmed!

I've tried putting the OpenArgs/Find process in the Load event, rather than
the Open event, but I still get the error message. To be specific, I get the
error message, and then when I clear the error message, the form opens
positioned to the correct record. It's like Acess is saying "Wait, you did
this wrong!", but then it works anyway.

Now, I'm using the recordsetclone of the subform, but I don't know if I need
that. I have tried just using the recordset.recordcount of the subform, in
case that helped, but it didn't seem to...

I haven't mentioned that I am working with linked back end tables. That
wouldn't be an issue would it? The OpenArgs code uses .FindFirst...

Still puzzling...

Fred

 

[Fred Boer]

Hi Dirk:

Connecting to JET tables...

This line calls the form:

DoCmd.OpenForm ("Frm_PatronCirculationInformation"), , , , , , "Patron_ID="
& Me.cboPatronFullname


From Frm_PatronCirculationInformation:

Private Sub Form_Current()
On Error GoTo Err_Form_Current

Me.RecordsetClone.MoveLast
Me.cmdGoNext.Enabled = Not (Me.CurrentRecord =
Me.RecordsetClone.RecordCount)
Me.cmdGoPrevious.Enabled = Not (Me.CurrentRecord = 1)

If
Forms!Frm_PatronCirculationInformation!Frm_PatronCirculationInformationSubfo
rm.Form.RecordCount = 0 Then
Me.Frm_PatronCirculationInformationSubform.Visible = False
Else
Me.Frm_PatronCirculationInformationSubform.Visible = True
End If

Me.Recalc

If Me.txtPatronType_ID <> 0 Then
Me.txtStaffFullname.Visible = False
Else
Me.txtStaffFullname.Visible = True
End If


Exit_Form_Current:
Exit Sub

Err_Form_Current:
fncWRMSErrMsg Err.Number, Err.Description
Resume Exit_Form_Current

End Sub

Private Sub Form_Load()
If Not IsNull(Me.OpenArgs) Then
With Me.RecordsetClone
.FindFirst Me.OpenArgs
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End If
End Sub

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Form_Open

'Call fncLoadcbo([Form])



DoCmd.Maximize


Exit_Form_Open:
Exit Sub

Err_Form_Open:
fncWRMSErrMsg Err.Number, Err.Description
Resume Exit_Form_Open

End Sub



That's the code. Throws 2455. And it doesn't reposition correctly after
dismissing error message. No luck with similar changes in my "barebones"
test forms either. Sure hope I'm not doing something stupid, but I can't see
anything...

Thanks!
Fred

 

[Fred Boer]

Hi Dirk

Changed to:

If Me!Frm_PatronCirculationInformationSubform.Form.Recordset.RecordCount = 0
Then
Me.Frm_PatronCirculationInformationSubform.Visible = False
Else
Me.Frm_PatronCirculationInformationSubform.Visible = True
End If


Still throws error message. BTW, slight correction to my earlier statement:
After dismissing error message, form *does* reposition correctly.

Fred

Hi Dirk:

Connecting to JET tables...

This line calls the form:

DoCmd.OpenForm ("Frm_PatronCirculationInformation"), , , , , ,
"Patron_ID=" & Me.cboPatronFullname


From Frm_PatronCirculationInformation:

Private Sub Form_Current()
On Error GoTo Err_Form_Current

Me.RecordsetClone.MoveLast
Me.cmdGoNext.Enabled = Not (Me.CurrentRecord =
Me.RecordsetClone.RecordCount)
Me.cmdGoPrevious.Enabled = Not (Me.CurrentRecord = 1)

If
Forms!Frm_PatronCirculationInformation!Frm_PatronCirculationInformationS
ubfo
rm.Form.RecordCount = 0 Then
Me.Frm_PatronCirculationInformationSubform.Visible = False
Else
Me.Frm_PatronCirculationInformationSubform.Visible = True
End If

Me.Recalc

If Me.txtPatronType_ID <> 0 Then
Me.txtStaffFullname.Visible = False
Else
Me.txtStaffFullname.Visible = True
End If


Exit_Form_Current:
Exit Sub

Err_Form_Current:
fncWRMSErrMsg Err.Number, Err.Description
Resume Exit_Form_Current

End Sub

Private Sub Form_Load()
If Not IsNull(Me.OpenArgs) Then
With Me.RecordsetClone
.FindFirst Me.OpenArgs
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End If
End Sub

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Form_Open

'Call fncLoadcbo([Form])



DoCmd.Maximize


Exit_Form_Open:
Exit Sub

Err_Form_Open:
fncWRMSErrMsg Err.Number, Err.Description
Resume Exit_Form_Open

End Sub



That's the code. Throws 2455. And it doesn't reposition correctly
after dismissing error message. No luck with similar changes in my
"barebones" test forms either. Sure hope I'm not doing something
stupid, but I can't see anything...

In this case, I'd expect this line:

If
Forms!Frm_PatronCirculationInformation!Frm_PatronCirculationInformationS
ubfo
rm.Form.RecordCount = 0 Then

to throw an error, because RecordCount isn't a property of a Form
object. Does it make a difference if you write this instead?

If
Forms!Frm_PatronCirculationInformation!Frm_PatronCirculationInformationS
ubform.Form.Recordset.RecordCount = 0 Then

(or the simpler ...

If
Me!Frm_PatronCirculationInformationSubform.Form.Recordset.RecordCount =
0 Then

)?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)