docmd.openform where condition problems

[John Welch]

I have a command button with the following code:
DoCmd.OpenForm "frmSearchAssig nments", , , "SearchAssignme ntID = 1"
(SearchAssignme ntID is the PK, auto number)
When it runs, the form opens but shows all records, rather than going to the
one I want. If I look at the form in design view, it shows
'SearchAssignme ntID=1' (without quotes) as the filter in the properties
list.
frmSearchAssign ments is bound to a query qrySearchAssign ments.
the form's properties are:
allow filters-yes
allow edits-yes
allow deletions-yes
allow additions-no
data entry-no
recordset type-dynaset

Any suggestions for why the where condition isn't being applied?

Also - If this did work correctly, would it limit what gets sent over the
wire with a file server FE/BE application (as would happen if I based the
form on a query limited on an indexed field), or is it just applying a
filter after the fact?

thanks
-John

[Wayne Morgan]

Is there misspelling in the field name, but if so, you should get prompted
for a parameter value. Do you have any code in the form that may be turning
the filter off again?

No, all records will be returned and the form will filter them. If you set
up an ADP using SQL Server as the back-end there is a filter you can apply
that gets sent to the server before the records go over the wire.

--
Wayne Morgan
MS Access MVP
"John Welch" <john(remove)we lch@cal(remove) central.com> wrote in message
news:dg******** *@enews2.newsgu y.com...

I have a command button with the following code:
DoCmd.OpenForm "frmSearchAssig nments", , , "SearchAssignme ntID = 1"
(SearchAssignme ntID is the PK, auto number)
When it runs, the form opens but shows all records, rather than going to
the one I want. If I look at the form in design view, it shows
'SearchAssignme ntID=1' (without quotes) as the filter in the properties
list.
frmSearchAssign ments is bound to a query qrySearchAssign ments.
the form's properties are:
allow filters-yes
allow edits-yes
allow deletions-yes
allow additions-no
data entry-no
recordset type-dynaset

Any suggestions for why the where condition isn't being applied?

Also - If this did work correctly, would it limit what gets sent over the
wire with a file server FE/BE application (as would happen if I based the
form on a query limited on an indexed field), or is it just applying a
filter after the fact?

thanks
-John

[John Welch]

Thanks Wayne. No, there was no mispelling - I tested that by purposely
spelling the field wrong, and I got a parameter prompt. I did find that if I
went up to the menu and chose 'apply filter' , then the form filtered
correctly, but not until then. It really has me stumped. I couldn't see any
code that turned off the filter directly, but maybe there is something
subtle. I'll look harder and report back if I find anything.
But I think I'm not going to use a where condition after all because I want
to limit the data going over the wire, so I'm going to have the form set up
it's own recordsource based on what it gets in its openargs.

"Wayne Morgan" <co************ *************** @hotmail.com> wrote in message
news:sF******** *********@newss vr21.news.prodi gy.com...

Is there misspelling in the field name, but if so, you should get prompted
for a parameter value. Do you have any code in the form that may be
turning the filter off again?

No, all records will be returned and the form will filter them. If you set
up an ADP using SQL Server as the back-end there is a filter you can apply
that gets sent to the server before the records go over the wire.

--
Wayne Morgan
MS Access MVP
"John Welch" <john(remove)we lch@cal(remove) central.com> wrote in message
news:dg******** *@enews2.newsgu y.com...

I have a command button with the following code:
DoCmd.OpenForm "frmSearchAssig nments", , , "SearchAssignme ntID = 1"
(SearchAssignme ntID is the PK, auto number)
When it runs, the form opens but shows all records, rather than going to
the one I want. If I look at the form in design view, it shows
'SearchAssignme ntID=1' (without quotes) as the filter in the properties
list.
frmSearchAssign ments is bound to a query qrySearchAssign ments.
the form's properties are:
allow filters-yes
allow edits-yes
allow deletions-yes
allow additions-no
data entry-no
recordset type-dynaset

Any suggestions for why the where condition isn't being applied?

Also - If this did work correctly, would it limit what gets sent over the
wire with a file server FE/BE application (as would happen if I based the
form on a query limited on an indexed field), or is it just applying a
filter after the fact?

thanks
-John

[John Welch]

I found it - I had the line:
me.dataentry = false
in the onload event of the form. When I took this out, it filtered
correctly, so that must have removed the filter. thanks for the tip.

"John Welch" <john(remove)we lch@cal(remove) central.com> wrote in message
news:dg******** *@enews2.newsgu y.com...

Thanks Wayne. No, there was no mispelling - I tested that by purposely
spelling the field wrong, and I got a parameter prompt. I did find that if
I went up to the menu and chose 'apply filter' , then the form filtered
correctly, but not until then. It really has me stumped. I couldn't see
any code that turned off the filter directly, but maybe there is something
subtle. I'll look harder and report back if I find anything.
But I think I'm not going to use a where condition after all because I
want to limit the data going over the wire, so I'm going to have the form
set up it's own recordsource based on what it gets in its openargs.

"Wayne Morgan" <co************ *************** @hotmail.com> wrote in
message news:sF******** *********@newss vr21.news.prodi gy.com...

Is there misspelling in the field name, but if so, you should get
prompted for a parameter value. Do you have any code in the form that may
be turning the filter off again?

No, all records will be returned and the form will filter them. If you
set up an ADP using SQL Server as the back-end there is a filter you can
apply that gets sent to the server before the records go over the wire.

--
Wayne Morgan
MS Access MVP
"John Welch" <john(remove)we lch@cal(remove) central.com> wrote in message
news:dg******** *@enews2.newsgu y.com...

I have a command button with the following code:
DoCmd.OpenForm "frmSearchAssig nments", , , "SearchAssignme ntID = 1"
(SearchAssignme ntID is the PK, auto number)
When it runs, the form opens but shows all records, rather than going to
the one I want. If I look at the form in design view, it shows
'SearchAssignme ntID=1' (without quotes) as the filter in the properties
list.
frmSearchAssign ments is bound to a query qrySearchAssign ments.
the form's properties are:
allow filters-yes
allow edits-yes
allow deletions-yes
allow additions-no
data entry-no
recordset type-dynaset

Any suggestions for why the where condition isn't being applied?

Also - If this did work correctly, would it limit what gets sent over
the wire with a file server FE/BE application (as would happen if I
based the form on a query limited on an indexed field), or is it just
applying a filter after the fact?

thanks
-John

[Wayne Morgan]

Hi John,

I'm glad you found the problem.

Even if you use a query as the form's Record Source, all of the records will
be pulled across the wire. The query is run in the front-end file. The
back-end file is just that, a file. It just sits there and the Access
application on the has to open the file and read the records. To keep all of
the records from coming across the wire, you need a way to let the back-end
do the filtering. That would mean an application/program on the back-end to
retrieve the records and send only the relevant ones. For that, you are
talking about something like SQL Server.

To improve performance using a front-end, back-end setup, see Tony's
suggestions at this link. The one about maintaining an open recordset to the
back-end file makes a huge difference in performance.

http://www.granite.ab.ca/access/performancefaq.htm
http://www.granite.ab.ca/access/splitapp/index.htm

--
Wayne Morgan
MS Access MVP
"John Welch" <john(remove)we lch@cal(remove) central.com> wrote in message
news:dg******** *@enews2.newsgu y.com...

Thanks Wayne. No, there was no mispelling - I tested that by purposely
spelling the field wrong, and I got a parameter prompt. I did find that if
I went up to the menu and chose 'apply filter' , then the form filtered
correctly, but not until then. It really has me stumped. I couldn't see
any code that turned off the filter directly, but maybe there is something
subtle. I'll look harder and report back if I find anything.
But I think I'm not going to use a where condition after all because I
want to limit the data going over the wire, so I'm going to have the form
set up it's own recordsource based on what it gets in its openargs.

"Wayne Morgan" <co************ *************** @hotmail.com> wrote in
message news:sF******** *********@newss vr21.news.prodi gy.com...

Is there misspelling in the field name, but if so, you should get
prompted for a parameter value. Do you have any code in the form that may
be turning the filter off again?

No, all records will be returned and the form will filter them. If you
set up an ADP using SQL Server as the back-end there is a filter you can
apply that gets sent to the server before the records go over the wire.

--
Wayne Morgan
MS Access MVP
"John Welch" <john(remove)we lch@cal(remove) central.com> wrote in message
news:dg******** *@enews2.newsgu y.com...

I have a command button with the following code:
DoCmd.OpenForm "frmSearchAssig nments", , , "SearchAssignme ntID = 1"
(SearchAssignme ntID is the PK, auto number)
When it runs, the form opens but shows all records, rather than going to
the one I want. If I look at the form in design view, it shows
'SearchAssignme ntID=1' (without quotes) as the filter in the properties
list.
frmSearchAssign ments is bound to a query qrySearchAssign ments.
the form's properties are:
allow filters-yes
allow edits-yes
allow deletions-yes
allow additions-no
data entry-no
recordset type-dynaset

Any suggestions for why the where condition isn't being applied?

Also - If this did work correctly, would it limit what gets sent over
the wire with a file server FE/BE application (as would happen if I
based the form on a query limited on an indexed field), or is it just
applying a filter after the fact?

thanks
-John

[John Welch]

Thanks again Wayne,
I have looked at Tony's great site, and I have implemented a persistent link
to the back end.

On the point about queries pulling all the data across the wire, though, I'm
not sure I agree. I've been talking with lots of people about this and there
seems to be a difference of opinion among experienced Access developers.
What I've gleaned so far is that if a query is limited with a "WHERE" on an
indexed field, then Jet can search the index without getting all the
records, and can then figure out which full records to get from the back
end, (we're talking an mdb back end here). For this reason, several sources
I've read suggest limiting the size of a form's recordsource for
performance, even will a file server situation. See the August 05 thread
"Optimizing forms by limiting recordsource- what's really true" in
Microsoft.publi c.access.multiu ser for a discussion of this.
In that discussion, Abert Kallal suggested just using a where condition in
docmd.openform to get the same results, but it seems to me that this _will_
pull all the records across, and then appy the filter after the fact, since
if I turn the filter off, there they all are. (Or did it get all the records
_after_ I turned off the filter?)

-John

"Wayne Morgan" <co************ *************** @hotmail.com> wrote in message
news:lz******** *******@newssvr 13.news.prodigy .com...

Hi John,

I'm glad you found the problem.

Even if you use a query as the form's Record Source, all of the records
will be pulled across the wire. The query is run in the front-end file.
The back-end file is just that, a file. It just sits there and the Access
application on the has to open the file and read the records. To keep all
of the records from coming across the wire, you need a way to let the
back-end do the filtering. That would mean an application/program on the
back-end to retrieve the records and send only the relevant ones. For
that, you are talking about something like SQL Server.

To improve performance using a front-end, back-end setup, see Tony's
suggestions at this link. The one about maintaining an open recordset to
the back-end file makes a huge difference in performance.

http://www.granite.ab.ca/access/performancefaq.htm
http://www.granite.ab.ca/access/splitapp/index.htm

--
Wayne Morgan
MS Access MVP
"John Welch" <john(remove)we lch@cal(remove) central.com> wrote in message
news:dg******** *@enews2.newsgu y.com...

Thanks Wayne. No, there was no mispelling - I tested that by purposely
spelling the field wrong, and I got a parameter prompt. I did find that
if I went up to the menu and chose 'apply filter' , then the form
filtered correctly, but not until then. It really has me stumped. I
couldn't see any code that turned off the filter directly, but maybe
there is something subtle. I'll look harder and report back if I find
anything.
But I think I'm not going to use a where condition after all because I
want to limit the data going over the wire, so I'm going to have the form
set up it's own recordsource based on what it gets in its openargs.

"Wayne Morgan" <co************ *************** @hotmail.com> wrote in
message news:sF******** *********@newss vr21.news.prodi gy.com...

Is there misspelling in the field name, but if so, you should get
prompted for a parameter value. Do you have any code in the form that
may be turning the filter off again?

No, all records will be returned and the form will filter them. If you
set up an ADP using SQL Server as the back-end there is a filter you can
apply that gets sent to the server before the records go over the wire.

--
Wayne Morgan
MS Access MVP
"John Welch" <john(remove)we lch@cal(remove) central.com> wrote in message
news:dg******** *@enews2.newsgu y.com...
I have a command button with the following code:
DoCmd.OpenForm "frmSearchAssig nments", , , "SearchAssignme ntID = 1"
(SearchAssignme ntID is the PK, auto number)
When it runs, the form opens but shows all records, rather than going
to the one I want. If I look at the form in design view, it shows
'SearchAssignme ntID=1' (without quotes) as the filter in the properties
list.
frmSearchAssign ments is bound to a query qrySearchAssign ments.
the form's properties are:
allow filters-yes
allow edits-yes
allow deletions-yes
allow additions-no
data entry-no
recordset type-dynaset

Any suggestions for why the where condition isn't being applied?

Also - If this did work correctly, would it limit what gets sent over
the wire with a file server FE/BE application (as would happen if I
based the form on a query limited on an indexed field), or is it just
applying a filter after the fact?

thanks
-John

[Rick Brandt]

John Welch wrote:

Thanks again Wayne,
I have looked at Tony's great site, and I have implemented a
persistent link to the back end.

On the point about queries pulling all the data across the wire,
though, I'm not sure I agree. I've been talking with lots of people
about this and there seems to be a difference of opinion among
experienced Access developers. What I've gleaned so far is that if a
query is limited with a "WHERE" on an indexed field, then Jet can
search the index without getting all the records, and can then figure
out which full records to get from the back end, (we're talking an
mdb back end here). For this reason, several sources I've read
suggest limiting the size of a form's recordsource for performance,
even will a file server situation. See the August 05 thread
"Optimizing forms by limiting recordsource- what's really true" in
Microsoft.publi c.access.multiu ser for a discussion of this. In that
discussion, Abert Kallal suggested just using a where condition in
docmd.openform to get the same results, but it seems to me that this
_will_ pull all the records across, and then appy the filter after
the fact, since if I turn the filter off, there they all are. (Or did
it get all the records _after_ I turned off the filter?)

I can tell you that against a server ODBC source the WHERE clause of
OpenForm does get sent to the server so that the filtering is applied there
rather than on the client. Based on that I would assume that the same
happens with a Jet back end.

By that I mean that you can expect the same I/O from using the WHERE clause
of OpenForm that you would see from using a query with the same criteria.
NOT that you would get the same i/o as when using an ODBC source.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[Wayne Morgan]

I will have to let the smarter heads prevail on this. Like you, from what I
see, they all appear to be there. However, most of my databases only have a
few thousand records at most, so it doesn't take long for them to arrive
anyway. I suppose with a packet sniffer you could try both and see how much
data is transferred each time.

--
Wayne Morgan
MS Access MVP
"John Welch" <john(remove)we lch@cal(remove) central.com> wrote in message
news:dg******** @enews2.newsguy .com...

Thanks again Wayne,
I have looked at Tony's great site, and I have implemented a persistent
link to the back end.

On the point about queries pulling all the data across the wire, though,
I'm not sure I agree. I've been talking with lots of people about this and
there seems to be a difference of opinion among experienced Access
developers. What I've gleaned so far is that if a query is limited with a
"WHERE" on an indexed field, then Jet can search the index without getting
all the records, and can then figure out which full records to get from
the back end, (we're talking an mdb back end here). For this reason,
several sources I've read suggest limiting the size of a form's
recordsource for performance, even will a file server situation. See the
August 05 thread "Optimizing forms by limiting recordsource- what's really
true" in Microsoft.publi c.access.multiu ser for a discussion of this.
In that discussion, Abert Kallal suggested just using a where condition in
docmd.openform to get the same results, but it seems to me that this
_will_ pull all the records across, and then appy the filter after the
fact, since if I turn the filter off, there they all are. (Or did it get
all the records _after_ I turned off the filter?)

[Tom van Stiphout]

On Fri, 16 Sep 2005 03:07:04 GMT, "Wayne Morgan"
<co************ *************** @hotmail.com> wrote:

That's not what I understand. Rather I think it works like this:
(a) The INDEX (in this case the PK) is returned to the form. Much
smaller than the entire table.
(b) The form selects the record(s) in the index
(c) The form selects the record(s) pointed to by the index. Those
records are the only ones that travel the wire.

-Tom.
<clip>>

No, all records will be returned and the form will filter them.

<clip>