OpenForm WhereCondition & ADP

[Guest]

Hi - I posted tis in microsoft.public.access.formscoding a week back and Guy
who replied (thanks Guy), but did not spot any obvious errors, suggested
posting here. In the meantime I am opening the detail for as acDialogue which
gets round the problem but isn't as usefull to the user .

Im having some strange behaviour with the OpenForm method now my mdb is
upsized to adp.

I have a continuous form to display a list of records, the user selects a
record and clicks an edit button. The code behinf the edit button is below;

Private Sub cmdEdit_Click()
Dim strWhereCondition As String
strWhereCondition = " ID = " + CStr(Me.sfrmMainRespondentList.Form.ID)
DoCmd.OpenForm "frmRespondent", acNormal, , strWhereCondition,
acFormEdit, acWindowNormal
Call cmdClearSearch_Click
End Sub

All work fine. The second form is opened and displays the record to edit.

If without closing the second form I go back to the list form and choose
another record and click edit then the second form (already open) displays
that record.

Now I upsize to adp.


Choosing a record from tthe list form and clicking edit brings up the second
form with the correct record. Going back to the list choosing a different
record and clicking edit brings up the second form but with no data - it
looks like it has created an empty new record.

The form (second form) record source is set to a query (now a view) in esign
view.
The RecordSource property reads 'qryRespondent' (without the quotes.

Any ideas?

 

[Sylvain Lafontaine]

Yeah, another old bug.

When the form is closed, the WhereCondition apply a Server Filter to the
record source; which means that the record source is directly filtered on
the server.

When you call it a second time, a Filter is applied to the form instead of
requerying the record source with a new server filter because the form is
already open; which means that the new filter condition is simply applied to
the already existing recordset but as this recordset has been created with
the old server filter; you end up with a filtering count of 0 in most cases.
(A Filter is also applied is the form is in design mode the first time the
open command is called.)

You can easily see this by creating a command button and use it to display
the following values:

MsgBox Me.Filter
MsgBox Me.FilterOn
MsgBox Me.ServerFilter

Notice also that the message « (Filtered) » appears at the right of the
navigation pane the second time, when the Filter is applied to the
recordset.

One way to circumvent this could be to test for the presence of the open
form and change the ServerFilter followed by a requery the second time:

Forms!f_Organismes.ServerFilter = strServerFilter
Forms!f_Organismes.Requery

but a better idea is probably to change the RecordSource each time.
Personally, I never use Filter or Server Filter with ADP, so I cannot tell
you more about this.

 

[Guest]

Hi Sylvain - thank you for answering this. Glad I'm not going mad.

Regards
Allen

 

[Guy]

Allen,

Just out on interest did you find out if opening and closing the form also
solved the problem?

Guy

 

[PBsoft]

Just out on interest did you find out if opening and closing the form

also solved the problem?

It doesn't solve.