By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,953 Members | 1,109 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,953 IT Pros & Developers. It's quick & easy.

Searching a table for identical entries before adding new entry

P: 55
Hello all,

I'm using Access 2000 and have another question. I have a table that holds information about particular investments. Each investment has a LPName and an autonumber LPID. I have a form 'tied' to this table that the user uses when he needs to input new data. The concern I have is that a new user that's not familiar with all of the investments already entered in the table will enter a duplicate. For instance:

tblLPID already has the entry ABC 2008
Jonny does not know that the table already has this investment, so he goes to frmNewInvestment and adds this investment to the table. The table now has two separate occurances of the same investment, not good at all!

What I'm hoping to do is tie some vba to the 'submit' button that will "search" the table and see if the newly entered data already exists.
If it does not already exist it will continue its code already programmed that opens a message box that says "Data was updated"

But, If the new entry already exists I want it to run a delete sql statement, erasing the new entry and then present a message box that says "Entry already exists, data was not updated".

I've thought about different ways to do this and none of them seemed logical. The only real way I could concieve getting this done is to run a query off of the submit button and if it has 2 rows returned (the original's row and the new duplicate row) it would proceed from there. I didn't know if you could even do something like that or, for that matter if access / vba had built in fuctionality to search / find records.

Hopefully I've explained my situation in a manner that makes sense, if not though please ask me for further clarification. Thank you for any help provided!
Mar 12 '07 #1
Share this Question
Share on Google+
4 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
The easiest way is to Index the field that can't be duplicated with Yes (No duplicates). If this doesn't solve your problem then post the metadata for your table(s) so we can get a better idea what's going on.

Here is an example of how to post table MetaData :
Table Name=tblBookings
Expand|Select|Wrap|Line Numbers
  1. Field; Type; IndexInfo
  2. Field; Type; IndexInfo
  3. StudentID; Autonumber; PK
  4. Family; String; FK
  5. Name; String
  6. University; String; FK
  7. Mark; Numeric
  8. LastAttendance; Date/Time
Mary
Mar 12 '07 #2

P: 55
Oh my gosh Mary,

Do you ever have one of those moments where you're quite in despair and then someone helps you out. But then after being given advice that works perfectly you realize "You idiot! /slap you totally knew that!!"

Thank you so much for the help, that's exactly what I was wanting...just slightly irritating that I spent all that time trying to make a really complex way work when right in front of me there was a really simple way. :)

Thank you again!!
Mar 14 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
Oh my gosh Mary,

Do you ever have one of those moments where you're quite in despair and then someone helps you out. But then after being given advice that works perfectly you realize "You idiot! /slap you totally knew that!!"

Thank you so much for the help, that's exactly what I was wanting...just slightly irritating that I spent all that time trying to make a really complex way work when right in front of me there was a really simple way. :)

Thank you again!!
LOL, always look for the simple solution first. That's my motto :)
Mar 14 '07 #4

missinglinq
Expert 2.5K+
P: 3,532
Occams Razor: "All things being equal, the simplest solution tends to be the best one."
Mar 15 '07 #5

Post your reply

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