For some reason when applyinf a filter, not changeing or adding information to my database i get a 3022 error.
here is the offending code. - Private Sub Updatebutton()
-
-
Dim f As String
-
-
Command6.Caption = "Deactivate " & Me.List6.Value
-
Command6.ForeColor = vbRed
-
Command6.FontSize = 30 - ((Len(Me.List6.Value) - 8) / 2)
-
f = "Container =" & List6.Value
-
Debug.Print f
-
Me.Filter = f '******************** this is what get highlighted when i get teh error and hit debug.************
-
Form.FilterOn = True
-
l6 = List6.Value
-
-
End Sub
what this does is changes the text on a command button and sets a filter to only display the record corisponding to the listbox selection made.
it works on other forms for other data, the thing diferent about this 1 is that list6 is a multi column list box. is that my downfall?
FYI list6 has 2 column's 0 and 1 column 0 contains the unique container number i am trying to filter on. the second column is a location for the container wich is a text field.
No luck with trying the string approch. I'm going to start over on this form from scratch see if i can maybe come up with a completely diferent way to attack this. I will post bak if i come to a resolution.
11 2989
Hi and welcome to Bytes.
I have read through your code and it seems fine (That said, You should really name your command buttons and listbox with a sensible name, imagine if it was a database you had inherited from someone else and had to make heads and tails of that code)
The first thing that pops to mind is,
1) Do you have any code attached to the OnFilter event of the form?
2) Does adding the filter cause your record to update? (Is the forms BeforeUpdate event run for example)
3) What is the value of f when you get the code?
First off, of course, is that one of Access' many quirks is that it doesn't always throw error messages that actually reflect the error being committed!
I wonder if the problem could be f = "Container =" & List6.Value
If, for example, List6.Value is 99, the above line would generate a filter that actually reads f = "Container =99"
because you do not have a space after the equal sign, and when something like this is placed within double quotes, Access doesn't automatically place the space for you, as it would if you simply had the line Container =99
without the quotes.
Try replacing f = "Container =" & List6.Value
with f = "Container = " & List6.Value
and see what happens.
Linq ;0)>
Nope tried many permutations of spacing, even tried: - f = "Container = '" & List6.Value &" '"
as if it were a text field. that didn't help.
there are also no on update or on filter or before update events
Stepping through the code with F8 does not take you to any other code?
Do you by chance have a control in your form also named f?
Nope it's a very simple form, consisting of the list box list6 and it's associated label, an checkbox 'active' and it's lavel and teh command6 button.
incase it helps since teh project is so small i'm posting the entire code for the whole form. - Private l6 As String ' value of List6 selection
-
Option Compare Database
-
-
-
-
Private Sub List6_Click()
-
Updatebutton
-
-
End Sub
-
-
Private Sub List6_Exit(Cancel As Integer)
-
-
End Sub
-
Private Sub Command6_Click()
-
On Error GoTo Err_Command6_Click
-
Dim f As String
-
-
Forms!deactivatecontainer.active.Value = False
-
f = "[Container] = '" & l6 & "' AND Status = True "
-
-
Debug.Print "f= " & f
-
-
stDocName = "Container access"
-
DoCmd.OpenReport stDocName, acPreview, , f 'Generate report What containers need Combination changed becasue of this deactivation.
-
-
Form.FilterOn = True
-
-
-
Exit_Command6_Click:
-
Exit Sub
-
-
Err_Command6_Click:
-
MsgBox Err.Description
-
Resume Exit_Command6_Click
-
-
End Sub
-
Private Sub Updatebutton()
-
-
Dim f As String
-
-
Command6.Caption = "Deactivate " & Me.List6.Value
-
Command6.ForeColor = vbRed
-
Command6.FontSize = 30 - ((Len(Me.List6.Value) - 8) / 2)
-
f = "'Container = " & List6.Value & "'"
-
Debug.Print f
-
Me.Filter = f
-
Form.FilterOn = True
-
l6 = List6.Value
-
-
End Sub
Is container the name of a control, a field in the table, or possibly both? If it is both, try renaming the control to something else.
I will admit though, that I am shooting in the dark here, I really don't know why its acting that way.
Container is a numeric field in the table Containers, which is the table that this form is bound to.
Like I said before I used almost the same code to deal with a diferent table for deactivating employees, and it works flawlessly. but those were text fields, and teh list box was only a sinfle column instead of a multi compuln one like here.
For perspective here it the other code that works. -
Private l4 As String ' value of list4 selection
-
Option Compare Database
-
-
-
-
Private Sub List4_Click()
-
Updatebutton
-
-
End Sub
-
-
Private Sub List4_Exit(Cancel As Integer)
-
-
End Sub
-
Private Sub Command6_Click()
-
On Error GoTo Err_Command6_Click
-
Dim f As String
-
-
Forms!deactivatestaff.active.Value = False
-
f = "[Full name] = '" & l4 & "' AND Access = True "
-
-
Debug.Print "f= " & f
-
-
stDocName = "Container access"
-
DoCmd.OpenReport stDocName, acPreview, , f 'Generate report What containers need Combination changed becasue of this deactivation.
-
-
Form.FilterOn = True
-
-
-
Exit_Command6_Click:
-
Exit Sub
-
-
Err_Command6_Click:
-
MsgBox Err.Description
-
Resume Exit_Command6_Click
-
-
End Sub
-
Private Sub Updatebutton()
-
-
Dim f As String
-
-
Command6.Caption = "Deactivate " & Me.List4.Value
-
Command6.ForeColor = vbRed
-
Command6.FontSize = 30 - ((Len(Me.List4.Value) - 8) / 2)
-
f = "[Full name] = '" & List4.Value & "'"
-
Me.Filter = f
-
Form.FilterOn = True
-
l4 = List4.Value
-
-
End Sub
-
This is driving me nuts. i'm about to break normalazition let acccess create a primary key and copy it to my container field and making container a text field to see if it works.
No luck with trying the string approch. I'm going to start over on this form from scratch see if i can maybe come up with a completely diferent way to attack this. I will post bak if i come to a resolution.
Also note that Container is a Reserved Word in Access VBA and as Smiley suggested, you really do need to rename it.
Linq ;0)>
I don't have a control or a varable named container. I have a field in a table named container. should I chage that?
Sign in to post your reply or Sign up for a free account.
Similar topics
by: newsfan |
last post by:
I get that error when I try to update a table in my database
This is my table
+----+------------+---------+-----------+
| ID | CPRNR | NAME | SURNAME |...
|
by: Nathan Bloomfield |
last post by:
Hi there,
I am having difficulty with a piece of code which would work wonders
for my application if only the error trapping worked properly.
Basically, it works as follows:
- adds records...
|
by: craig.brenizer |
last post by:
I have a table of data that has duplicate values in the pagenumber
field.
How can I combine the data so that the page numbers of duplicate part
numbers are on one record for that part number?
...
|
by: Brad |
last post by:
I have a response filter which injects "standard" html into my pages. The
filter works fine when the initial stream is small enough not to
buffer...or....if I have a large unbuffered stream (i.e. I...
|
by: sara |
last post by:
I have a procedure to automate bringing several Excel files into our
Access tables, on a daily basis.
The problem is that if the user has a problem, and tries to run the
import again (maybe 3...
|
by: g_man |
last post by:
I am trying trap Runtime error 3022 (duplicates) in the click event of
a command button that closes the form. I have code in the Form_Error
event that does a good job of providing a more meaningful...
|
by: rksadhi |
last post by:
/*Geting error ---object reference not set to an instance---at bold line----plz reply asap thanks in advance*/
cmd = new OleDbCommand ("SELECT e.emp_id,e.email, m.email AS Email FROM emp_details...
|
by: Jan |
last post by:
Hi:
When I searched the newsgroup for this problem, I saw two or three
instances of the question being asked, but it was never answered. Not
too promising, but here goes:
I have a form with...
|
by: oaklander |
last post by:
I would like to make sure there are no duplicate data entries in my Oracle 9i table (called MainTable) which has an Id field that is the primary key, ValData with a varchar data type, Fid and Fid2...
|
by: Fr33dan |
last post by:
Hi. I've been working on a database for awhile now and I have a button on a form that is supposed to apply a filter based on criteria from the user. However when I actually go to apply the filter in...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
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,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
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...
|
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,...
| |