Preventing duplicates in List Box

[Henryklassen]

What is the best way to prevent duplicates in a filtered list box? What I have is a form that pops up and the list box is filtered based on the previous form's ID Field, what I would like to do is if "Description" exist then another "Description" could not be enter.

 

[Access_guy49]

If the form pops up, and their are fields on that form linked to a given table, the field where a person would enter a description would be populated already. are you concerned with the user overwriting that existing data?

 

[Henryklassen]

Lets see if I can explain it better. On the main form the employee enters a new sub assembly part, once entered the new subassembly shows up in the listbox. The user can now double click on the subassembly part in the list box which takes him to a new form where he can enter the parts used to make that subassembly. As he adds parts they too show up in a listbox. It's this list box that I want to watch for duplicate entries and prevent them. One thing to keep in mind is a part may show up on other subassemblies that's why I can't index the field with no duplicates allowed. I hope this helps, and thank you for you replies. Once I have this figured out I think I'm ready to start testing it on the shop floor.

 

[Access_guy49]

You can index using multiple fields, so you would index using the subassemply ID and the subassembly part ID.

This way, any given part can have multiple records in your join table, and any sub assembly can have multiple records in the join table, but they can't duplicate the exact relationship twice

 

[Henryklassen]

Access_Guy49 you're the MAN! Works really well, Thank you!

 

[Access_guy49]

quite welcome glad to help.

 

[Henryklassen]

Now that I have that working I'm trying to trap error 3022 by using this code

Private Sub Form_Error(DataErr As Integer, Response As Integer)

'If an error occurs because of missing data in a required field
'display our own custom error message

    Const conErrRequiredData = 3022

    If DataErr = conErrRequiredData Then
        MsgBox ("This item is already in the list")
        Response = acDataErrContinue
        Me.Undo
        Me.Refresh
    Else
        'Display a standard error message
        Response = acDataErrDisplay
    End If
End Sub

it works fine till I place

Me.form.refresh

in the field were the data is entered, in this case it's called "Gauge" can you explain to me what I'm doing wrong?

 

[Moses76]

You can index using multiple fields, so you would index using the subassemply ID and the subassembly part ID.

This way, any given part can have multiple records in your join table, and any sub assembly can have multiple records in the join table, but they can't duplicate the exact relationship twice

Is there a property in a list box that I can set to make sure it does not populate duplicate entries ?

moses