Create a msgbox when duplicate data is entered into a form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a no duplicates Index (in the main table) with five fields in it. The
form based on the table contains all the fields from the table, including
these five fields. I want to create a message box to alert people if they
enter the same data as another record (same as in those five fields) before
they continue filling in the rest of the form. I've written a suitable
message but cannot see where in the macro options I can attach it to. there
doesn't seem to be an option to attach it to a Table Index. The default
message that Microsoft pops up when the record is completed is of no use as
it doesn't say where the problem lies and that is what is important.
 
I have a no duplicates Index (in the main table) with five fields in it. The
form based on the table contains all the fields from the table, including
these five fields. I want to create a message box to alert people if they
enter the same data as another record (same as in those five fields) before
they continue filling in the rest of the form. I've written a suitable
message but cannot see where in the macro options I can attach it to. there
doesn't seem to be an option to attach it to a Table Index. The default
message that Microsoft pops up when the record is completed is of no use as
it doesn't say where the problem lies and that is what is important.
Click to expand...

Macros are quite limited in their capabilities... and VBA code is
(despite appearences) not a dreadful jungle from which none have ever
returned alive. I'd really use VBA code to do this. The code would not
refer to the index at all (it's rather hard to do so and not
particularly useful); but it could detect the duplicate condition and
warn the user.

One problem is that in the Windows paradigm, the user is not
constrained to enter data in any particular order on the form. I
suppose you could leave all but these five controls disabled until
they are filled in, but that would be really annoying to most users!

The exact syntax of the code will depend on the controlnames,
datatypes and fieldnames of the five relevatn fields. If you wish to
post them, I or someone else can certainly help with the code.

John W. Vinson [MVP]
 
Here follows the message I would like to use. I would like it to pop-up
after the last of the five fields have been entered and when they
collectively form a duplicate. The fields are [Date],[Time],[lib],[Activity
Type],[Age Group Target]. These all fall in the first portion of the form
and tab one to the other. The first two are date/time types and the rest are
text types. You are correct in saying I find the idea of code VERY scarry, I
don't even know when to put it or anything else, but am very willing to
learn. Thank you very much for your offer of help.

WARNING@There is another record with the same DATE TIME LIB ACT-TYPE & AGE
GROUP.
Duplicates are not allowed.
Check and amend your entry.
NOTE: IF the entry is true, change the time on this record.@Unless amended,
this record will NOT be saved
..........................
 
Thank you for your AccessLinks document. I agree with your remarks on page
12, the default message is of no help. The only problem is I don't know
where to find that error message and I don't understand code, and I wouldn't
want to just change the default for all time, because the words I
specifically choose for this condition may not apply in another duplicate
error condition. Perhaps I need to go on a course to learn code in Access
databases.....
 
Hi JayStar,
The only problem is I don't know where to find that error message
Click to expand...

You don't find it....you enter it yourself. You can do this by copying the
code shown on page 12, and pasting it into the form's code module. To do
this, open the form in design view. Then click on View > Code. This should
open a code module associated with the form. In the case of (5) fields, you
can make the message a bit more generic. Instead of "This item has already
been added.", you would use the same suitable message that you indicated in
your initial post.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
 
Many thanks, I will try that later today and see how it works. I'll post how
I get on.
 
Many thanks, I will try that later today and see how it works. I'll post how
I get on.
Click to expand...

Sounds like Tom's got you on the right track; I'll watch this thread
to see if you get it resolved. Thanks Tom, and sorry for butting in!

John W. Vinson [MVP]
 
Hello again, I did just reply but I got an error message saying it wasn't
sent......

So in the hope this one succeeds.....

I have successfully added the code and changed the message to suit. But it
only works at the end of the record and then wipes out all the data including
all the correct bits. Also, the message only appears on the last tabbed page.

Is there a way of safely triggering it to pop up when [Age] has been entered
(the last of the 5 fields) and also to then return the focus to the [Date] so
that the record can be amended before going any further.

I've tried using "refresh" in the hope it would trigger it, but my message
still only appears on the last tabbed page and all the data is wiped out, so
that didn't work.

I've tried other things to, all with equal failure, Woe ! Your help would
be invaluable to me and to others who are not very IT folk to start with.

Many Many thanks.
 
Hi JayStar,
I have successfully added the code and changed the message to suit. But it
only works at the end of the record and then wipes out all the data including
all the correct bits.
Click to expand...

True. The solution I proposed only works when you attempt to save the
record. It is not designed to check the record as you are filling it out. To
prevent wiping out the data, try removing (or commenting out) the line of
code that reads: Me.Undo
Also, the message only appears on the last tabbed page.
Click to expand...

Not sure what you are telling us here, but the message is designed to work
only in the form(s) that you add this code to. So, it appears as if your last
tabbed page is the one that contains the form with this code.
Is there a way of safely triggering it to pop up when [Age] has been entered
(the last of the 5 fields) and also to then return the focus to the [Date] so
that the record can be amended before going any further.
Click to expand...

Yes, but that's going to take a bit of VBA code to write a procedure to
check for duplicates. This procedure would likely need to be called from the
BeforeUpdate event procedure of each of the five text boxes. It would need to
run only if all five text boxes had a value entered. Since I am currently on
my lunch break at work, I don't have the time to go into further details
right now.

By the way, Date is a reserved word in Access. You will be doing yourself a
big favor by avoiding the use of any reserved words for things that you
assign a name to in Access. Here is a handy list compiled by Access MVP Allen
Browne, which I recommend bookmarking for future reference:

Problem names and reserved words in Access
http://allenbrowne.com/AppIssueBadWord.html

Allen has also made a free utility available that you can download for
checking your databases out for bad field and table names, among other
problems.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

JayStar said:
Hello again, I did just reply but I got an error message saying it wasn't
sent......

So in the hope this one succeeds.....

I have successfully added the code and changed the message to suit. But it
only works at the end of the record and then wipes out all the data including
all the correct bits. Also, the message only appears on the last tabbed page.

Is there a way of safely triggering it to pop up when [Age] has been entered
(the last of the 5 fields) and also to then return the focus to the [Date] so
that the record can be amended before going any further.

I've tried using "refresh" in the hope it would trigger it, but my message
still only appears on the last tabbed page and all the data is wiped out, so
that didn't work.

I've tried other things to, all with equal failure, Woe ! Your help would
be invaluable to me and to others who are not very IT folk to start with.

Many Many thanks.

John W. Vinson said:
Sounds like Tom's got you on the right track; I'll watch this thread
to see if you get it resolved. Thanks Tom, and sorry for butting in!

John W. Vinson [MVP]
Click to expand...
Click to expand...
 
I have thought about what you've said, so I've split the form into 3 with the
first form just with the unique type data and a couple of other fields and
all the rest are in sub forms on the tabbed pages. Therefore the error
message now pops up on the first page and I took the me.undo out and the data
is now available to be corrected. I've added a refresh button to be used
after the amendments have been done, before proceeding further with the form.

So far it is working OK on my test DB. Is there anything here that you
think I've done wrong, or that would have bad consequences further down the
line ???

Many thanks for all you help.

Tom Wickerath said:
Hi JayStar,
I have successfully added the code and changed the message to suit. But it
only works at the end of the record and then wipes out all the data including
all the correct bits.
Click to expand...

True. The solution I proposed only works when you attempt to save the
record. It is not designed to check the record as you are filling it out. To
prevent wiping out the data, try removing (or commenting out) the line of
code that reads: Me.Undo
Also, the message only appears on the last tabbed page.
Click to expand...

Not sure what you are telling us here, but the message is designed to work
only in the form(s) that you add this code to. So, it appears as if your last
tabbed page is the one that contains the form with this code.
Is there a way of safely triggering it to pop up when [Age] has been entered
(the last of the 5 fields) and also to then return the focus to the [Date] so
that the record can be amended before going any further.
Click to expand...

Yes, but that's going to take a bit of VBA code to write a procedure to
check for duplicates. This procedure would likely need to be called from the
BeforeUpdate event procedure of each of the five text boxes. It would need to
run only if all five text boxes had a value entered. Since I am currently on
my lunch break at work, I don't have the time to go into further details
right now.

By the way, Date is a reserved word in Access. You will be doing yourself a
big favor by avoiding the use of any reserved words for things that you
assign a name to in Access. Here is a handy list compiled by Access MVP Allen
Browne, which I recommend bookmarking for future reference:

Problem names and reserved words in Access
http://allenbrowne.com/AppIssueBadWord.html

Allen has also made a free utility available that you can download for
checking your databases out for bad field and table names, among other
problems.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

JayStar said:
Hello again, I did just reply but I got an error message saying it wasn't
sent......

So in the hope this one succeeds.....

I have successfully added the code and changed the message to suit. But it
only works at the end of the record and then wipes out all the data including
all the correct bits. Also, the message only appears on the last tabbed page.

Is there a way of safely triggering it to pop up when [Age] has been entered
(the last of the 5 fields) and also to then return the focus to the [Date] so
that the record can be amended before going any further.

I've tried using "refresh" in the hope it would trigger it, but my message
still only appears on the last tabbed page and all the data is wiped out, so
that didn't work.

I've tried other things to, all with equal failure, Woe ! Your help would
be invaluable to me and to others who are not very IT folk to start with.

Many Many thanks.

John W. Vinson said:
On Sun, 4 Feb 2007 23:09:00 -0800, JayStar

Many thanks, I will try that later today and see how it works. I'll post how
I get on.

Sounds like Tom's got you on the right track; I'll watch this thread
to see if you get it resolved. Thanks Tom, and sorry for butting in!

John W. Vinson [MVP]
Click to expand...
Click to expand...
Click to expand...
 
Offhand, it sounds like it should work. My only concern is the idea of having
a unique index on a combination of five fields, which might not be so
user-friendly from a user's perspective. How are they going to know what a
valid combination of data is that will be unique if they start getting the
error telling them that this combination of values has already been entered?


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
 
Hello Tom
My database is used to record all none lending activities held in our 35
County libraries and which types of public that activity may or does attract
and also whether the activity meets certain performance indicators. The data
is input by library staff on a daily or weekly basis after the event has
happened, but sometimes they anticipate an event and fill in the basics,
returning later to fill in attendance and other data only known when the
event has happend. The problem occurs when an activity has been
inadvertently entered twice, either becaue of forward entering or because
someone else has not realised it's already been entered.

On a monthly basis I currently run "find duplicates" and then send them a
report asking if these events really did happen at that library on the same
day, time, activity type and for the same age range. 95% of the time they
are wrong duplicates and I then clear them out of the database or manually
alter the time a little so they are no longer duplicates.

Because of the time it takes to do this, I want to make the next version
(due in April) "tighter" and also to automate most of the performance
indicators based on the past two years data recording, to save them having to
record that bit of information over and over again for regularly run
activities. That part does give them the option to add or remove
automatically enter data, if a special or none standard event occured.

If a genuine duplicate occurs I've said they should alter the time of the
second activity slightly to make it unique. (there may be two schools or
community groups attending two similar events at the same time but both need
recording separately, this is very rare but is possible in our larger
libraries)

So part is to help them and part is to help me.

If the unique data is duplicated (ie date, time, library, activity type, and
age range) and they are now alerted to that early, it will stop them from
wasting their time entering the rest of the data all over again. They would
then search back to find the original record (they are listed in date order)
and either edit that by filling in the rest of the data post event, or do
nothing because it is already full entered.

I cannot think of another way of achieving this.

I hope I haven't bored you with all this, but I want to thank you very much
for your help and to explain how important it has been to me and what the
database is used for.
 
Thanks for the explaination. It helps clarify things to me. In this
situation, a user would know to look for the previous entry to verify first,
so that makes sense.

In case you really have two fields named Date and Time, I recommend that you
consider renaming these fields, to avoid any potential problems with the use
of reserved words.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
 
Hello again Tom
I took on board the comment about "reserved words" and downloaded the
document, which I have kept for future reference, I also ran the utility
which found no problems with my database (thank goodness). I had already
changed the Date and Time words to Activity Date and Activity Time, to be on
the safe side.

So much to learn...... So little time .............

I logged onto your web link and see you work in lovely Seattle at Boeing.
How wonderful. We visited there a few years ago. Had a fun meal at the
restaurant on the top of the needle in Seattle, did the tour bus thing and
also spent a day at Boeing Fields museum - very interesting. I went up
inside the Concorde you have there and was so surprised as to how narrow and
small it seemed, but it was interesting all the same.

We live in a village close to Stratford upon Avon in England, which is a
lovely part of the world.

Once again, so very many thanks.
 
So much to learn......

That's one if the things that I like about Access. It seems like one can
never know it all. There's always some new rock to turn over.

I haven't done the tour bus thing, or visited the Concorde yet, but I've
been to the Space Needle restaurant many times with guests from out of town.
The Museum of Flight is definately worth visiting for anyone who has an
interest in aviation.
We live in a village close to Stratford upon Avon in England, which is a
lovely part of the world.
Click to expand...

I'll bet it is!
Once again, so very many thanks.
Click to expand...

You're very welcome. Good luck on your project.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Data entry creating duplicate rows in table 3
Duplicate values 4
Using ADODB, how do I create a msgbox when duplicate data is enter 3
duplicate value error message - new issue 1
Duplicate values in the index, primary key, or relationship messag 2
Duplicate Records Problem 2
duplicates error after 2 years- why? 5
Merging of Tables in One New Table 5

Back
Top