473,698 Members | 2,344 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Filter a Form-----Frustration Factor High

I know I am getting in my own way .... so someone please save me from
myself!

I created a form and rather than force the users to scroll through all
the records to find theirs I decided to have the form open to an empty
screen and added a combo box(the combo box will allow for new
records). What I need is if the ID is not new, all the information for
that record will populate the form. I tried to use the combo box to
filter for the record but the wizard does not give me the option to
use it as a filter, plus I want to be able to add a new record if
needed.. I tried the following code but get an error msg (the changes
were not successful because they would create duplicate values in the
primary key field). I tried the same code int the before update event
and get the error message (RunTime error 3426 this action was
cancelled by an associated object) Can someone tell me where I am
going wrong?

Private Sub cboRFP_AfterUpd ate()

Dim rst As DAO.Recordset
Dim strSearchName As String
Dim AMOUNT As Integer

AMOUNT = DCount("[sol_no]", "RFP*NosTbl ", "[sol_no] = '" & Me!
[Sol_No] & "'")
Set rst = Me.Recordset
strSearchName = CStr(Me!Sol_No)

If AMOUNT < 1 Then
[Cntrk#].SetFocus
Else
rst.FindFirst "Sol_No = '" & strSearchName & "'"
Me.Refresh

End If
rst.Close

End Sub

Sep 28 '07 #1
3 2817
birt wrote:
I know I am getting in my own way .... so someone please save me from
myself!

I created a form and rather than force the users to scroll through all
the records to find theirs I decided to have the form open to an empty
screen and added a combo box(the combo box will allow for new
records). What I need is if the ID is not new, all the information for
that record will populate the form. I tried to use the combo box to
filter for the record but the wizard does not give me the option to
use it as a filter, plus I want to be able to add a new record if
needed.. I tried the following code but get an error msg (the changes
were not successful because they would create duplicate values in the
primary key field). I tried the same code int the before update event
and get the error message (RunTime error 3426 this action was
cancelled by an associated object) Can someone tell me where I am
going wrong?

Private Sub cboRFP_AfterUpd ate()

Dim rst As DAO.Recordset
Dim strSearchName As String
Dim AMOUNT As Integer

AMOUNT = DCount("[sol_no]", "RFP*NosTbl ", "[sol_no] = '" & Me!
[Sol_No] & "'")
Set rst = Me.Recordset
strSearchName = CStr(Me!Sol_No)

If AMOUNT < 1 Then
[Cntrk#].SetFocus
Else
rst.FindFirst "Sol_No = '" & strSearchName & "'"
Me.Refresh

End If
rst.Close

End Sub
All you need is...

Me.Filter = "Sol_No = '" & [Sol_No] & "'"
Me.FilterOn = True

If there is a match that record will be filtered on and if there is not you
will be taken to the new record position.

Is Sol_No really a character field? If not drop the single quotes.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Sep 28 '07 #2
On Sep 28, 4:36 pm, "Rick Brandt" <rickbran...@ho tmail.comwrote:
birt wrote:
I know I am getting in my own way .... so someone please save me from
myself!
I created a form and rather than force the users to scroll through all
the records to find theirs I decided to have the form open to an empty
screen and added a combo box(the combo box will allow for new
records). What I need is if the ID is not new, all the information for
that record will populate the form. I tried to use the combo box to
filter for the record but the wizard does not give me the option to
use it as a filter, plus I want to be able to add a new record if
needed.. I tried the following code but get an error msg (the changes
were not successful because they would create duplicate values in the
primary key field). I tried the same code int the before update event
and get the error message (RunTime error 3426 this action was
cancelled by an associated object) Can someone tell me where I am
going wrong?
Private Sub cboRFP_AfterUpd ate()
Dim rst As DAO.Recordset
Dim strSearchName As String
Dim AMOUNT As Integer
AMOUNT = DCount("[sol_no]", "RFP*NosTbl ", "[sol_no] = '" & Me!
[Sol_No] & "'")
Set rst = Me.Recordset
strSearchName = CStr(Me!Sol_No)
If AMOUNT < 1 Then
[Cntrk#].SetFocus
Else
rst.FindFirst "Sol_No = '" & strSearchName & "'"
Me.Refresh
End If
rst.Close
End Sub

All you need is...

Me.Filter = "Sol_No = '" & [Sol_No] & "'"
Me.FilterOn = True

If there is a match that record will be filtered on and if there is not you
will be taken to the new record position.

Is Sol_No really a character field? If not drop the single quotes.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com- Hide quoted text -

- Show quoted text -
Hello Rick,

Thank you for your response. I do have a few questions. The sol_no
field is a combo of text and # I am using Test1.

1.) When I changed the code for the before update event to the below I
get the Error Msg 2115 - Function set to the
before update property for this field is preventing Access from
saving data in this field. Debug takes me to
Me.FilterOn = True

Private Sub cboRFP_BeforeUp date(Cancel As Integer)

Me.Filter = "Sol_No = '" & [Sol_No] & "'"
Me.FilterOn = True

End Sub

2.) When I put the above code into the After Update Event I get the
Error Msg - The changes were not successful because
they would create duplicate values in the primary key field. It
too takes me to Me.FilterOn = True

What am I doing wrong?

Thank you,
Deb
Oct 1 '07 #3
birt wrote:
Hello Rick,

Thank you for your response. I do have a few questions. The sol_no
field is a combo of text and # I am using Test1.

1.) When I changed the code for the before update event to the below I
get the Error Msg 2115 - Function set to the
before update property for this field is preventing Access from
saving data in this field. Debug takes me to
Me.FilterOn = True

Private Sub cboRFP_BeforeUp date(Cancel As Integer)

Me.Filter = "Sol_No = '" & [Sol_No] & "'"
Me.FilterOn = True

End Sub

Nees to be AfterUpdate as you have below.

2.) When I put the above code into the After Update Event I get the
Error Msg - The changes were not successful because
they would create duplicate values in the primary key field. It
too takes me to Me.FilterOn = True

What am I doing wrong?
The ComboBox that you use to apply the filter needs to be an UNBOUND
control. Otherwise you are actually changing data in whatever record you
are currently looking at. Just remove whatever ControlSource property you
currently have in the ComboBox.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Oct 1 '07 #4

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

Similar topics

75
6161
by: Howard Nease | last post by:
Hello, everyone. I would appreciate any advice that someone could give me on my future career path. Here is my situation: I am a bright Junior in a very well-respected private high school, taking almost all AP and accelerated classes. I am HIGHLY interested in technology, more specifically the field of Computer Science and software engineering. I have heard a whole lot about the fact that the market for software engineers nowadays is...
13
4028
by: Peter Mutsaers | last post by:
Hello, Up to now I mostly wrote simple filter scripts in Perl, e.g. while(<>) { # do something with $_, regexp matching, replacements etc. print; } Now I learned Python and like it much more as a language.
7
3656
by: Irmen de Jong | last post by:
Hi, Things like Twisted, medusa, etc.... that claim to be able to support hundreds of concurrent connections because of the async I/O framework they're based on.... can someone give a few examples of some web sites actually using those Python frameworks? Thanks :) --Irmen
4
1707
by: YeeCN | last post by:
Hi, I need to write an application that requires HUGH volume of number crunching (tens of billions of calculations). Speed is the single most important factor for me. I am wondering is .NET (vb, c#) the right framework to develop the application so whether I should look somewhere else. Any help or suggestions is greatly appreciated.
13
7766
by: bgbauer70 | last post by:
My appologies if this ends up being a duplicate post. For some reason the first post never showed up. I've tried about 300 iterrations of this same ability, and none of them seem to work in Firefox. Take the following code for example. It WILL stop me from entering zero into the first text box, but it wont stop me from entering extended ascii characters (which is the final goal).
0
2017
by: Bonj | last post by:
hello this is a purely algorithmical question but if i have posted to an irrelevant group, apologies. can anyone point me at some good tutorials or info about the steps involved in creating a high-end generic binary tree (or, ternary search tree). The basic method I've got at the moment is having a resource file containing a series of data structures (which represent strings), specifically organised such that a test string can be matched...
4
5271
by: Gary Davis | last post by:
Once a day or so, I receive an error on a fairly active website that calls this StrMixed.cs method constructor. 99% of the time there is no exception: System.Web.HttpUnhandledException: Exception of type System.Web.HttpUnhandledException was thrown. ---> System.InvalidOperationException: Hashtable insert failed. Load factor too high. at System.Collections.Hashtable.Insert(Object key, Object nvalue, Boolean add)
7
4051
by: Udhay | last post by:
How to get the frequency of an audio file and how to separate the low and high frequency of an audio file
2
3559
by: PaulR | last post by:
Hi, (DB2 LUW v8.2) When using parameter markers how does the optimizer evaluate filter factors? - and is it able to make use of distribution stats. for parm. markers? The reason I ask, is we have heavily skewed data in places and we need
0
8680
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9169
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...
1
8899
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
8871
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
7738
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
6528
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
4371
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
4622
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3052
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.