Prevent MultiValue Combobox to store duplicate entry

[mukeshpiplai]

I have a bound form with Multivalue Combobox (bounded). I successfully stored data to bound field using combobox values. but I want a prevent message if any item selected from this combobox already exist in its bounded field which stored previously. I using following code but could not success...

Dim ExistDAPost As String

ExistDAPost = DLookup("PostAssigned", "tabDealingAssistant", "PostAssigned = '" & Me.AssignPost.Selected(0) = True & " '")
If ExistDAPost > 0 Then
MsgBox "This Post already assigned. Kindly Check DA Register.."
Exit Sub
End If

[jforbes]

I'm guessing this is what is giving you problems:

ExistDAPost = DLookup("PostAssigned", "tabDealingAssistant", "PostAssigned = '" & [iCODE]Me.AssignPost.Selected(0) = True[/iCODE] & " '")

This might work:

ExistDAPost = DLookup("PostAssigned", "tabDealingAssistant", "PostAssigned = '" & Me.AssignPost.Selected(0) & " '")

but this is probably what you are after:

ExistDAPost = [iCODE]DCount[/iCODE]("PostAssigned", "tabDealingAssistant", "PostAssigned = '" & Me.AssignPost.[iCODE]Value [/iCODE]& " '")

[mukeshpiplai]

i tried previously following:-

ExistDAPost = DCount("PostAssigned", "tabDealingAssistant", "PostAssigned = '" & Me.AssignPost.Value & " '")

but there's Type Mismatch error..

and following return Runtime Error "3831"-

ExistDAPost = DLookup("PostAssigned", "tabDealingAssistant", "PostAssigned = '" & Me.AssignPost.Selected(0) & " '")

[jforbes]

The Runtime error doesn't surprise me, but the Type Mismatch does. I would put a breakpoint on that line and to inspect the value for Me.AssignPost.V alue.

...There is the remote chance that stuffing the result of a DCount() into a String Variable isn't working, so you might also want to change ExistDAPost to an Integer or Long.

[mukeshpiplai]

Thanks Jforbes.


changed variable to Integer and also long. But type mismatch error with both..

[jforbes]

What are you getting for Me.AssignPost.V alue? Have you tried to debug the error at all?

[mukeshpiplai]

Is there any other ways to prevent duplicate.

As I have multivalue combobox with values i.e. All designations/ Post of a department. These post will be dealt by some administration department employees. Each employee assigned separate designations more than 5.
I am working on work allocation form. After selecting an employee ID of administration dept. employee, work allocator will select multiple designations for this employee to deal all work related to these assigned post/designations.

I want to a message..after select any designation which already allotted to another dealing employee which stored in tabDealingAssis tant table.

Any suggestion...?

I am new with vba.....

[IMGnothumb]https://bytes.com/attachment.php? attachmentid=86 16[/IMGnothumb]

[IMGnothumb]https://bytes.com/attachment.php? attachmentid=86 17[/IMGnothumb]

[jforbes]

Well, that makes more sense, I guess. And I should have caught on earlier, but I didn't. I've stayed away from Multivalue fields as they really break normalization rules. I stay away from them so much, that I didn't even notice you were talking about one. I would recommend backing out and Normalizing your data instead of trying to stuff all those selections into one field, but I'm guessing there is no way you are going to do that.

The specific problem you are running into, as a result of the Multivalue field, is that you have multiple items in a string that you want to compare against another string containing multiple items. This isn't going to fly. You will probably need to resort to a loop or VBA function to get this to work.

The following is just example of a loop and Aircode, you'll need to take ownership of it.

    Dim sSearchValues As String
    Dim sSearchValue As Variant
    Dim iCount As Integer
    sSearchValues = Nz(Me.AssignPost.Value, "")
    For Each sSearchValue In Split(sSearchValues, ",")
        If iCount = 0 Then iCount = iCount + DCount("[PostAssigned]", "tabDealingAssistant", "Instr([PostAssigned],'" & sSearchValue & "')>0")
    Next
    If iCount >0 Then
       ' Error Message
    End If

[mukeshpiplai]

There's type mismatch error on Line 4

[zmbd]

mukeshpiplai:
Just to verify: did you use the LookUp-Wizard in the table to create a "Lookup/Multivalue-field" in the table? This is the only way I know of to create a multiple-choice combo-box.

If so, then this is the root of your problem.