473,856 Members | 1,662 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 2826
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
6222
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
4039
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
3667
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
1722
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
7791
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
2022
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
5283
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
4064
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
3572
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
9904
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...
1
10772
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
9527
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
7928
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
7086
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5956
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4568
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
2
4169
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3195
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.