473,717 Members | 2,026 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

docmd.openform where condition problems

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
Nov 13 '05 #1
8 13211
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

Nov 13 '05 #2
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


Nov 13 '05 #3
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



Nov 13 '05 #4
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



Nov 13 '05 #5
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



Nov 13 '05 #6
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
Nov 13 '05 #7
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?)

Nov 13 '05 #8
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>
Nov 13 '05 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
4198
by: beowulfs | last post by:
Here's what I've got: I've got a form with combo boxes. you can select already existing company names or type in new ones. if you type in a new one, it prompts you to double click the combo box if you want to add a new company. when you double click it brings up the new company form so you can enter the rest of the information besides just the name there. This all works. Here's what I want:
3
4517
by: Lyn | last post by:
Hi, I have a Search input form which collects from the user a person's name. I am using LIKE with a "%" suffix in the SQL so that the user does not have to type in the full name. When they hit the Search button, a query is run to search the Person table for a match. This produces a recordset (I am using ADO). If the RecordCount is zero, they get a No Match message. If the RecordCount is 1, a DoCmd.OpenForm is performed to open the...
15
3886
by: Thelma Lubkin | last post by:
formA determines some_where and some_value and issues docmd.close ,Me docmd.openform "formB", , ,some_where, , ,some_value formB receives the correct some_where and some_value After completing its work formB issues docmd.close ,Me docmd.openform "formA"
2
6855
by: Mike | last post by:
I am trying to open a search results form based on the input from a prompt form. I am using the following code: --- Begin Code --- Private Sub btnSearch_Click() 'Dim Variable and assign data Dim srce As String, fstnme As String, lstnme As String, bidnum As String srce = Me.Source_Control If Not IsNull(Me.Text18) Then
3
11799
by: gavm360 | last post by:
Hello, im trying to open a form from an dialog box form: the button on the dialog box has this on the onclick event: DoCmd.OpenForm "frmCASES_UNION", acViewNormal, , "MCH_CASECODE = #" & Me!txtCCODE & "#" the form I'm trying to open is "frmcases_union" and im trying to filter it by the "MCH_CASECODE" which i type into
1
2027
by: rdsandy | last post by:
Hi, I am using Access 2003. I have a list box on a form which has 6 columns in it, with data taken from a table. I have an unbound text box which holds the ID number for the selected item in the list box. I have a command button which I want to use to open another form on that record. At the moment it opens the other form but only to the first record. The other form has its recordset set to the same table as what the list box displays...
1
2421
by: altesse33 | last post by:
I am trying to open the form "frmClients_Main" to the record that is specified by the end user. Here is the code: ---------------------------------------------------------- Public Function IDSearch() 'This function allows the user to open the main form to a particular client record. Dim stSql As String Dim stInput As String 'InputBox prompts user to enter client ID for the form they wish to open.
1
2716
by: silen | last post by:
i am using Access2000. Currently i had a main form call "frmDownload" and sub form "fsubAdmmission". Under the fsubAdmmission, i do have another sub form "fsubHospital". Once i link my application to database A, when "frmDownload" loaded, it does successfully create "fsubAdmmission" object and i am able to assign "fsubAdmmission.form.recordsource". Once i link my application to database B, when "frmDownload" loaded, it failed to assign...
3
1720
by: erict | last post by:
Access 2002-2003 runtime App works fine on single proc machines but we are rolling out a tech refresh and we found this case where the code appears to simply slip through the open form command and the form never opens. If Not IsNull(frmPODetails.Form!txtPONumber) Then MsgBox "frmPurchaseOrders Open Form" DoCmd.OpenForm "frmPODetailsUnbound" End If Note since this is a production issue Ihad to...
0
9348
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9202
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9109
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9048
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7981
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6648
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4478
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4739
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3177
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.