Geting error 3022 Duplicate Key data when applying a filter.

[lee weaver]

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.

[TheSmileyCoder]

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?

[missinglinq]

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)>

[lee weaver]

It's not a spacing issue.

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

[TheSmileyCoder]

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?

[lee weaver]

No Control '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.

[lee weaver]

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

[TheSmileyCoder]

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.

[lee weaver]

It's a field in a table.

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.

[lee weaver]

No luck with trying the string approch.

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.

[missinglinq]

Also note that Container is a Reserved Word in Access VBA and as Smiley suggested, you really do need to rename it.

Linq ;0)>

[lee weaver]

I don't have a control or a varable named container. I have a field in a table named container. should I chage that?