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
-
9 1937
I'm guessing this is what is giving you problems: - ExistDAPost = DLookup("PostAssigned", "tabDealingAssistant", "PostAssigned = '" &
Me.AssignPost.Selected(0) = True & " '") -
This might work: - ExistDAPost = DLookup("PostAssigned", "tabDealingAssistant", "PostAssigned = '" & Me.AssignPost.Selected(0) & " '")
-
but this is probably what you are after: - ExistDAPost =
DCount ("PostAssigned", "tabDealingAssistant", "PostAssigned = '" & Me.AssignPost.Value & " '")
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) & " '")
-
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.
Thanks Jforbes.
changed variable to Integer and also long. But type mismatch error with both..
What are you getting for Me.AssignPost.Value? Have you tried to debug the error at all?
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.....
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
There's type mismatch error on Line 4
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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....
|
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?
...
|
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...
|
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)...
|
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...
|
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...
|
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 :...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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
|
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...
| |