Detecting / Avoiding Duplicates in a VBA userform

[noxios]

Hi,

I’m trying to create an Excel VBA userform to manage some parking slots and I would need to verify if, on a specific date, a parking space is already attributed and/or if a specific car plate already has a place.

For that, I need a VBA routine that informs and prevents users from entering duplicate data.

When the user clicks on the insert button, the program should verify column C (date in text format). If the value (date) already exists, it should then verify column D (plate n°) and/or column E (parking n°); should any of these latter values match an existing record, the program should inform the user that the entry already exists and prevent him from inserting it.


id_____name______date_______plate n°___parking n°
1______Joao______02/10/2011______XXX______-1/009
2______Pedro_____03/11/2011______YYY______-1/010
3______Luis______02/10/2011______ZZZ______ -1/009
3______Pedro_____15/10/2011______XXX______-1/012


Thanks for your help!

NOTE: The program runs in 2 separated workbooks (one is the main program and the other is just the data base (the one that should be "scanned”) .

 

[SkipVought]

Hi,

What method are you using to update your table?

The usual way is to query the key value. If the recordset is empty, then you can add. Otherwise, it exists.

VBA code questions are much better handled in forum707.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[noxios]

I'm sorry I don’t know the answer to your question, I'm not a programmer or expert (I assembled the program with several pieces of code, but now I'm stuck with the "duplicity" part.
Basically I insert data in a form and when I click OK it saves the data in other workbook (DB)

The reason to be 2 separated workbooks is to be possible to work in the DB from several places at same time
(It’s not tested yet but that's the main idea)

 

[SkipVought]

CODE???

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[macropod]

Cross-posted at:

http://www.vbaexpress.com/forum/showthread.php?t=39634

(where significant effort in providing help has already been given)

For cross-posting etiquette, please read:

http://www.excelguru.ca/node/7

Cheers
Paul Edstein
[MS MVP - Word]

 

[noxios]

Hi SkipVought

The all file is posted in this link
Cross-posted at:

http://www.vbaexpress.com/forum/showthread.php?t=39634

Feel free for giving any suggestion/contribute to the solution of this problem.

 

[SkipVought]

Try using MS Query as a parameter query, supplying the parking space coordinates (key). If you get a recordset returned, then you have an instance of that space.

faq68-5829

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[noxios]

Hi SkipVought
I tried to read about using MS Query it's to much for me, most of the info’s were to technical for my level.
I also noticed that at home I don’t have this MS Query in my tools bar but at work it exists... Anyway thank you for your help

 

[noxios]

Hi

For information, this problem is not solved yet ...
Can some one help me on this?

With regards

 

[SkipVought]

Your statement is very non-informative. Please be specific and forthcoming, with clear, concise and complete information!

You have not shared adequate information in past posts, and you have also cross posted, which is improper forum postin etiquette.

Not too much going in your favor, as it stands. In fact, it's downright noxious.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[noxios]

(re)Hi

if the problem for helping me is to see /know//understand the file, the ling to the file is here:

http://e-nautia.com/people/noxios/disk/Publico/Parking.zip

 

[SkipVought]

Many of us cannot access links due to company security.

If you want help, then clearly stated your problem HERE with concise and complete information.

Please explain what you have tried so far and exactly what problem you need advice on.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

I tried to read about using MS Query it's to much for me, most of the info's were to technical for my level.

You may be in over your head, according to your own admission.

If you do not have the capacity to read and understand MS Query, then attempting to design and code a system as described above is way over your head, as it is not a trivial task, involving inquiry, add, change & delete logic for your simple database, all of which is much more involved than understanding MS Query.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[noxios]

Well...
Maybe you are right about that, this is a forum for EXPERTS as i can understand by your words... and i was expecting like "learn to drive not to know the all components that make a car"
This in did is "over my head" or my capacity of understanding

However I am asking you or some one else for help in something u are comfortable helping I presume...

You see if I was or know how to program in VBA I for sure will not be here asking help because for sure I could help my self.

Anyway, take care and big thank you for your help.

 

[SkipVought]

I'd suggest using the COUNTIFS() function to determine if you have duplicates. That would be the simplest and avoid having to use any database features.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}