473,326 Members | 2,148 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Prevent MultiValue Combobox to store duplicate entry

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

Expand|Select|Wrap|Line Numbers
  1. Dim ExistDAPost As String
  2.  
  3. ExistDAPost = DLookup("PostAssigned", "tabDealingAssistant", "PostAssigned = '" & Me.AssignPost.Selected(0) = True & " '")
  4. If ExistDAPost > 0 Then
  5. MsgBox "This Post already assigned. Kindly Check DA Register.."
  6. Exit Sub
  7. End If
  8.  
Jan 25 '16 #1
9 1937
jforbes
1,107 Expert 1GB
I'm guessing this is what is giving you problems:
Expand|Select|Wrap|Line Numbers
  1. ExistDAPost = DLookup("PostAssigned", "tabDealingAssistant", "PostAssigned = '" & Me.AssignPost.Selected(0) = True & " '")
  2.  
This might work:
Expand|Select|Wrap|Line Numbers
  1. ExistDAPost = DLookup("PostAssigned", "tabDealingAssistant", "PostAssigned = '" & Me.AssignPost.Selected(0) & " '")
  2.  
but this is probably what you are after:
Expand|Select|Wrap|Line Numbers
  1. ExistDAPost = DCount("PostAssigned", "tabDealingAssistant", "PostAssigned = '" & Me.AssignPost.Value & " '")
Jan 25 '16 #2
i tried previously following:-

Expand|Select|Wrap|Line Numbers
  1. ExistDAPost = DCount("PostAssigned", "tabDealingAssistant", "PostAssigned = '" & Me.AssignPost.Value & " '")

but there's Type Mismatch error..

and following return Runtime Error "3831"-

Expand|Select|Wrap|Line Numbers
  1. ExistDAPost = DLookup("PostAssigned", "tabDealingAssistant", "PostAssigned = '" & Me.AssignPost.Selected(0) & " '")
  2.  
Jan 25 '16 #3
jforbes
1,107 Expert 1GB
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.Value.

...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.
Jan 25 '16 #4
Thanks Jforbes.


changed variable to Integer and also long. But type mismatch error with both..
Jan 25 '16 #5
jforbes
1,107 Expert 1GB
What are you getting for Me.AssignPost.Value? Have you tried to debug the error at all?
Jan 25 '16 #6
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 tabDealingAssistant table.

Any suggestion...?

I am new with vba.....



Attached Images
File Type: jpg form.jpg (92.1 KB, 619 views)
File Type: jpg table.jpg (11.1 KB, 424 views)
Jan 25 '16 #7
jforbes
1,107 Expert 1GB
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.
Expand|Select|Wrap|Line Numbers
  1.     Dim sSearchValues As String
  2.     Dim sSearchValue As Variant
  3.     Dim iCount As Integer
  4.     sSearchValues = Nz(Me.AssignPost.Value, "")
  5.     For Each sSearchValue In Split(sSearchValues, ",")
  6.         If iCount = 0 Then iCount = iCount + DCount("[PostAssigned]", "tabDealingAssistant", "Instr([PostAssigned],'" & sSearchValue & "')>0")
  7.     Next
  8.     If iCount >0 Then
  9.        ' Error Message
  10.     End If
Jan 25 '16 #8
There's type mismatch error on Line 4
Jan 26 '16 #9
zmbd
5,501 Expert Mod 4TB
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.
Jan 30 '16 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

0
by: Geetu | last post by:
I am trying to jar up a file and getting this exception, not sure what could be wrong. The same piece of code works fine in few machines but there is one machine where this piece of code gives this...
0
by: jjliu | last post by:
i have a table with two fields (id and myfield). id is a primary key not nll auto_increment, and myfield is set as unique... Due to unique setting for myfield, if i try to insert the duplicate...
0
by: Gary Lundquest | last post by:
I have an application with MS Access 2000 as the front end and MySQL as the back end. All was well until I upgraded the MySQL (Linux) server. The Problem: I insert data into a cumulative table....
1
by: Joseph Chase | last post by:
I am running version 4.1.13a-log on a Mac XServe. How can I receive a 'duplicate entry' error for an UPDATE? An update isn't creating an entry, so why this error message? ...
8
by: Iona | last post by:
Hi Allan, I'm using a nifty piece of code you put on here some time back to do a duplicate entry check as below. I'm using to check for duplicate names. However I am getting an error message on...
5
by: baur79 | last post by:
Hi guys i try to run this code in loop and to pass even the entry is duplicated def email_insert_in_db(email): sql="INSERT INTO emails (email) values ('%s') "%(email)...
3
by: patelxxx | last post by:
I'm trying to update a template on our Content Management System, however getting the following error. I know its difficult to know what the problem is without looking at the PERL coding, however...
1
by: chicago1985 | last post by:
I have a unique constraint in my Oracle table for 3 fields. If I enter duplicate info on the table using Oracle client I will get an Ora message error ORA-00001 that tells me it is a duplicate entry...
1
by: waqasahmed996 | last post by:
Hi I am facing a strange problem. I have a duplicate entry message even though i am using IGNORE in query. My query is insert ignore into table1 select * from table2 Error is: Error Code :...
2
by: neelsfer | last post by:
I have a problem with duplicate entries. I can't get this code to work. I have one other request. When the message box appears, i would like the "racefinishtime" record in this same table to...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 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 former...

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.