Code mysteriously runs on wrong form

[mabl123456]

Hi everyone

I'm trying to get hold of some mysterious behaviour when opening one
form (F2) from another form (F1). By doubleclicking in the first form
(F1) in a datasheet view subform (SB1F1) and attaching code to the
doubleclick event I open the second form (F2) and navigate to the
current record in the subform (SB1F1) in the first form (F1). This is
a common way of doing this I believe, I have seen the code in several
places. The code in the doubleclick event calls a function:

Public Function openOtherForm(ByVal strForm As String, ByVal
strIdField As String, ByVal varIdControl As Variant, ByVal boolText As
Boolean) As Boolean
Dim frm As Form
DoCmd.OpenForm strForm
Set frm = Forms(strForm)
frm.Recalc
If boolText Then
frm.RecordsetClone.FindFirst CStr(strIdField) & " = '" &
varIdControl & "'"
Else
frm.RecordsetClone.FindFirst CStr(strIdField) & " = " &
varIdControl
End If
If Not frm.RecordsetClone.NoMatch Then
frm.Bookmark = frm.RecordsetClone.Bookmark
End If
openOtherForm = True
End Function

The line frm.recalc is inserted to make the newly opened form (F2) to
"wake up" and behave properly. But what happens is that it changes the
current record in (SB1F1) to the first record (like a requery) and
also changes the value of varIdControl where the primary key from
SB1F1 is stored. How come???? How can forms("F2").recalc change
anything in Forms("F1")????

The reason I need frm.recalc is that I in F2 have 2 subforms (SB1F2
and SB2F2) (showing something similar to customer [F2] - order [SB1F2]
- orderline SB2F2]). If F2 is already open when code above runs
everything works fine. BUT if F2 is closed there will be no records in
subform 2 SB2F2 after the code has completed. If I step through code
there is records in SB2F2 after docmd.openform but they disappear at
the bookmark codeline. If I enter the line frm.recalc SB2F2 will have
records and everything is OK except that it always is the first record
in SB1F1 that is opened in F2.

Please help! This is very frustrating.

Mats

 

[Matte]

Hi again

The reason why not SBF2F2 will show any records when the code runs and
F2 not is open is the following code from SBF1F2 onCurrent event:

Private Sub Form_Current()
On Error Resume Next
Parent.avsnittTaxSubstrSUB.Form!avsnittartSUB.Requery
Parent.avsnittTaxSubstrSUB.Form!avsnittsubstratSUB.Requery
End Sub

Without on error resume next I will get run-time error 2455 'You
entered an expression that has an invalid
reference to the property Form/Report' in this scenario. If F2 is
already open when openotherform runs this error will not happen.

How do I get around this BUG in Access?

Thanks in advance for any help.

Mats

 

[Albert D. Kallal]

"wake up" and behave properly

"wake up" is not a very technical term here. I would remove that command,
you don't need it at all.

you could just try:

dim strWhere as string

If boolText Then
strWhere = strIdField & " = '" & varIdControl & "'"
Else
strWhere = strIdField & " = " & varIdControl
end if
docmd.OpenForm strForm,,,strWhere

The above is a LOT less code and really is the recommend approach...

Your code should work, but I think the above is not only less code, but
should be more reliable. Furthermoe, why open a form and "load up" all
reocrds, and THEN move to the correct reocrd. If you running on a network,
then the above ONLY loads up the one reocrd you need (better for
performance)