Prevent duplicate dates (1 Viewer)

bishop0072 · Jan 11, 2013

I have tblWO_Points and column with firstname and lastname and dateWO column. What code do I use to prevent duplicate dates for same firstname and lastname? Also if a date is a duplicate that it wil ask if you want to go to that record?

Example:
Jane Doe Wednesday, January 07, 2012
Jane Doe Tuesday, January 06, 2012
Mike Doe Wednesday, January 07, 2012
Mike Doe Tuesday, January 06, 2012
Mike Doe Thursday, January 12, 2012

So the same dates can occur for different people but the same dates cannot be recorded for the same person.

jdraw · Jan 11, 2013

research unique compound index and then build one with

firstname and lastname and dateWO

Solo712 · Jan 12, 2013

bishop0072 said:
I have tblWO_Points and column with firstname and lastname and dateWO column. What code do I use to prevent duplicate dates for same firstname and lastname? Also if a date is a duplicate that it wil ask if you want to go to that record?

Example:
Jane Doe Wednesday, January 07, 2012
Jane Doe Tuesday, January 06, 2012
Mike Doe Wednesday, January 07, 2012
Mike Doe Tuesday, January 06, 2012
Mike Doe Thursday, January 12, 2012

So the same dates can occur for different people but the same dates cannot be recorded for the same person.

Best would be to test this with a DLookup function, in the BEFORE _UPDATE event of the form, something like:

Code:

 testdate = Nz(DLookup("MyPrimaryKeyField","tblWO_Points", _
                      "lastname = ' & Me!lastname & _
                      "' AND firstname = '" &  Me!firstname & _    
                      "' AND dateWO = #" & Me!dateWO & "#"),0)
 
 if testdate <> 0 Then
     '  the date is a duplicate for the given lastname and firstname
     '  display message 
     Cancel = True
 End if

Supply your primary key (assumed to be numeric) in the table for the first argument in DLookup). Also, you will need to place the format of the date between the last pair of ampersands like : FORMAT( Me!dateWO, "mm/dd/yyyy") if your Regional Settings are not set to that, i.e. if you do not live in the US. Good luck

Best,
Jiri

RainLover · Jan 12, 2013

I would go with the unique compound index

Solo712 · Jan 12, 2013

RainLover said:
I would go with the unique compound index

I understand the distrust of coding.

But you might want to consider the overhead of maintaining compound indexes as a way to execute a simple query. Surely, this would be an overkill in most situations.

Best,
Jiri

spikepl · Jan 12, 2013

The index thingy by itself has the disadvantage that you should also construct an error handler to bow out gracefully, if the user made the mistake.

The belt-and-braces approach is to have the index as a backstop, but to do the BeforeUpdate handling anyway.

As to "overheads" - honestly, in the era of quadcore processors and terabyte disks it is very seldom worthwhile to worry about such things.

Solo712 · Jan 12, 2013

spikepl said:
The index thingy by itself has the disadvantage that you should also construct an error handler to bow out gracefully, if the user made the mistake.

The belt-and-braces approach is to have the index as a backstop, but to do the BeforeUpdate handling anyway.

As to "overheads" - honestly, in the era of quadcore processors and terabyte disks it is very seldom worthwhile to worry about such things.

Sure, you can have it both ways, spikepl !

Best,
Jiri

jdraw · Jan 12, 2013

Solo712 said:
Sure, you can have it both ways, spikepl !

Best,
Jiri

I agree with spikepl as far as machines today and would advise
a) create the unique compound index
b) always create an error handler to reduce "shock" to the user ( and good feedback for bad data/processes etc)
c) use before update event to check for a possible duplicate and take appropriate action.

I'm sort of sorry for assuming a programmer would include error handling by default. You'd think that after so many posts, I wouldn't assume anything. I'll try to refrain from assumptions.....

For anyone reading this that does not yet have MZTools for VBA-- get it -- one click for an error handler for each module, plus many more useful features - and it's free.

Solo712 · Jan 12, 2013

jdraw said:
I agree with spikepl as far as machines today and would advise
a) create the unique compound index
b) always create an error handler to reduce "shock" to the user ( and good feedback for bad data/processes etc)
c) use before update event to check for a possible duplicate and take appropriate action.

I'm sort of sorry for assuming a programmer would include error handling by default. You'd think that after so many posts, I wouldn't assume anything. I'll try to refrain from assumptions.....

For anyone reading this that does not yet have MZTools for VBA-- get it -- one click for an error handler for each module, plus many more useful features - and it's free.

You guys crack me up, really !

So how would you check for duplicates (and - as the OP asks to handle this situation - flag the occurence) in the BEFORE UPDATE event ?
If you agree that my code is one way to accomplish that, then you pretty well have made my case for judging the indexing solution (your step 'a'), superfluous and extravagant. If you don't agree, please show the VBA coding ( for 'b' and 'c') that would accomplish what the OP asks and explain why 'a' would still be needed.

Incidentally, it is truly naive to believe that fast processors, ample storage ,or for that matter, black-fiber connectivity make use of resources by ACCESS a moot point. Experts agree that most Access databases greater than 200 Mb suffer performance woes. Why would that be, I wonder ?

Best,
Jiri

Pat Hartman · Jan 13, 2013

The reason for the unique index is to handle the situation regardles of where the update is performed. It is always preferable to rely on declarative RI rather than user code. Other people who work with the database in the future might not be as competent as yourself and may add another form that can perform the same insert but not include the appropriate validation edits.

I might also trap the "duplicate" error message if I thought the default Access message would confuse my users.

RainLover · Jan 13, 2013

Solo712 said:
I understand the distrust of coding. But you might want to consider the overhead of maintaining compound indexes as a way to execute a simple query. Surely, this would be an overkill in most situations.

Best,
Jiri

Jiri

I don't know how you came to the conclusion that I have some sort of distrust of coding. Was this a general statement or was it your code in particular. If it is your code in particular then I did not consider it as a viable method. The Unique Index was my first choice and still is.
Perhaps you wish to comment further.

You also mentioned the overhead of maintaining compound indexes and claim that this is an overkill. Could you explain this statement as I really do not understand what you are trying to portray.

I do like your confidence. It is good to see even though we differ in opinions.

Solo712 · Jan 13, 2013

Pat Hartman said:
The reason for the unique index is to handle the situation regardles of where the update is performed. It is always preferable to rely on declarative RI rather than user code. Other people who work with the database in the future might not be as competent as yourself and may add another form that can perform the same insert but not include the appropriate validation edits.

I might also trap the "duplicate" error message if I thought the default Access message would confuse my users.

Pat,
The idea of using indexes to do data validation (which is what the OP asks for) would seem counterintuitive to most programmers who have had their hands in real, commercially viable systems. If you made a policy of using indexes to set up business rules for your app you would be dead in water in no time. And isn't this interesting: the OP writer asks for 'code' to execute a query on his recordset. He/she has not asked - as far as I can see - to make assumptions about a size of the app, how complex its relationships will be, about processor speed, or about the competence of the future technical support of the app. The guy/gal asked for code to do a query and I seem to be the only one willing to give one.
Sue me !

Best,
Jiri

Solo712 · Jan 13, 2013

RainLover said:
Jiri
I don't know how you came to the conclusion that I have some sort of distrust of coding.

Obviously, since you have a problem decoding a smiley face next to the statement.

You also mentioned the overhead of maintaining compound indexes and claim that this is an overkill. Could you explain this statement as I really do not understand what you are trying to portray.

Oh, you have no idea, do you ? Ok then, here is what the Bible says on the subject:

Unnecessarily indexing a table that does not really require an index eats up a bit of disk space for each record in the table. More important , indexes extract a performance hit for each index on the table each time a record is added. Because Access automatically updates indexes each time a record is added (or removed), the internal indexing must be adjusted for each new record. If you have ten indexes on a table, Access makes ten adjustments to the indexes each time a new record is added or an existing record is deleted, causing a noticeable delay on large tables (particularly on slow computers). Michael R. Groh, MS Access Bible 2010, p 83

Hope it's all much clearer now !

Best,
Jiri

spikepl · Jan 13, 2013

Wauw ....

Access is a brilliant application in that many non-programmers get to/are forced to dabble in coding using Access. You should know - if you don't by now - that a huge share of OP's have no clue whatsoever about programming practices, maintainability, robustness etc etc, and you never know whether it is a knowledgeable one that asks for support here or not. Some can spend a huge amount of effort on implementing nonsensical solutions to nonexisting problems. This is why the answers provided here by the more experienced people do not always correspond exactly to what OP asks. It would often be too cruel to let them stew in the mess of their own making

As to the indexes and the overhead? Honestly this is bordering on ridiculous here. Yes there is an overhead, and no, it has 0 significance when inserting a record manually, and yes, there is an overhead if importing thousands of records one by one.... and this discussion is akin to discussing how many angels can dance on the head of a pin

RainLover · Jan 13, 2013

SOLO 712

Ok then, here is what the Bible says on the subject

You can quote whoever you like. But bear in mind that that person is most likely quoting someone else who quoted yet another person. The thing that is most credible is where you can show proof.
I can only assume that you have left out the other side of the argument which any good writer would include. That is while there is a drop in performance when writing the searching and sorting functions are greatly enhanced. It is up to the programmer to decide what is best for each situation.

What code do I use to prevent duplicate dates for same firstname and lastname? Also if a date is a duplicate that it will ask if you want to go to that record?

This is what the OP asked. Not what you claim he asked.

The guy/gal asked for code to do a query and I seem to be the only one willing to give one.

There has been nothing said about a Query, but the OP did ask how to go to the original record. So you are not the only one willing to answer the question, you have ignored the second part of the question as every one else has.

While your ability to write code is above average I would think that the code as posted by you has not been tested and most likely will fail in some situations. But then this is speculation on my part as I have not tested it either.

The more experienced people here at AWF quite often do not answer a question as asked. The OP must be new at Access due to the nature of the question. Hence the advise of a Compound Index is the simplest method for the OP to do what they need.

The OP did not ask about this because he most likely does not know about it. So in this case as in the case of many other questions one would be doing a disservice by answering a question that could lead to other problems. It would be better to put them on the right track in the first place.

Having said all that I feel that given time and experience you may become one of the better assets here at AWF.

RainLover · Jan 13, 2013

SOLO 712

I could not get your code to run.
I have attached a Sample DB for you to check and tell me if I did something wrong.

Solo712 · Jan 13, 2013

RainLover said:
SOLO 712

I could not get your code to run.
I have attached a Sample DB for you to check and tell me if I did something wrong.

Rain,
you need to substitute "WO_PointsPK" for the first arg "MyPrimaryKeyField". That said, there was a misplaced quotation mark after "lastname" in my criteria argument. Apologies.

The attached corrected code works. It intercepts the duplicates with a message "Can't Do That !".

Best,
Jiri

RainLover · Jan 13, 2013

SOLO 712

Thanks for that.

I will have a look tomorrow.

Solo712 · Jan 13, 2013

spikepl said:
Wauw ....

Access is a brilliant application in that many non-programmers get to/are forced to dabble in coding using Access. You should know - if you don't by now - that a huge share of OP's have no clue whatsoever about programming practices, maintainability, robustness etc etc, and you never know whether it is a knowledgeable one that asks for support here or not. Some can spend a huge amount of effort on implementing nonsensical solutions to nonexisting problems. This is why the answers provided here by the more experienced people do not always correspond exactly to what OP asks. It would often be too cruel to let them stew in the mess of their own making

Toot your horn, all you want, spikepl. It will not answer the one relevant question here. Assuming that the code I have provided (or other code) works, and fills the bill why should one use an index on top of that ? You will have to have code one way or another to trap the duplicate error if you want to handle it. Whether you index or not. So I see no advantage to complicating the design with an extra gizmo. If I have missed something, tell me what, ...puhleeze !

Best,
Jiri

ChrisO · Jan 13, 2013

Jiri.

Here's a question; how long would it take to write code that works well and would achieve the same level of protection as a unique compound index?

Chris.

Prevent duplicate dates (1 Viewer)

New member

Super Moderator

Registered User.

VIP From a land downunder

Registered User.

Eledittingent Beliped

Registered User.

Super Moderator

Registered User.

Super Moderator

VIP From a land downunder

Registered User.

Registered User.

Eledittingent Beliped

VIP From a land downunder

VIP From a land downunder

Attachments

Registered User.

Attachments

VIP From a land downunder

Registered User.

Registered User.

Similar threads

Users who are viewing this thread