Prevent Users from entering duplicates

[medranoestraaadriana]

Prevent Users from entering duplicate data on a form

Here's a dummy database of what I'm doing. Look at Item 123AAA on the main table. It has been in and out 3 times. Let's say it was back on 07/08/10 and I checked it in in the inventory.
So today as I do the inventory, I see that item and 123AAA is in. So what I want is that when I try to enter this item on the inventory form a message telling me that the item already exists on the inventory. Item 123AAA would be in the inventory because it was log in yesterday.
It would be on the main table for a third time, which is ok because it was on three different occassions.

Try to enter item 123AAA on the Inventory form and see what happens

 

[medranoestraaadriana]

I posted it as a new thread, can you check it out

 

[boblarson]

I posted it as a new thread, can you check it out

And I'm merging it back - no need to do up a new thread for something that's currently being worked.

 

[vbaInet]

All that you needed to do was to change the table name in the DCount function to the query. Plus, you were using incorrect names in there too. See attached.

I noticed that your table doesn't have a PK. Does it have one (or more) in your original db?

For more on the DCount() function, read this:

http://www.techonthenet.com/access/functions/domain/dcount.php

 

[medranoestraaadriana]

Thanks a bunch!!
I've tried it on my original and I get this message 'Compile Error: Method or data member not found'. and it highlights this, "Me![Item ID]".

 

[vbaInet]

You might have called it Item_ID with the underscore. Check your field names.

 

[medranoestraaadriana]

I've checked my field names, and everything on the database I'm still getting the same message. I just give up!!!, I've been trying to fix it the whole day and nothing!!, I don't know what else to do.
Thanks!! a bunch, eventhough I've not acomplished anything, I've learned alot about access.

 

[vbaInet]

I doubt you would have only Me![Item Id] on one line because that would be redundant. It must be highlighting a whole line. What line is that?

 

[medranoestraaadriana]

It's highlighting 'Private Sub form_beforeupdate(cancel As Integer)' and gives me the compile error 46.

Oh my God, I got it. I change the name of the text box and label, and IT'S WORKING!!! IT'S WORKING!!!

 

[vbaInet]

There are thousands of error numbers out there What is the exact error message?

 

[medranoestraaadriana]

I've have one more question. On this same db, If I ever want to go back and see the inventory for just one day. How would I do it? Should I save a copy of the inventory by entering a code on the 'On-close' event.

 

[vbaInet]

Create a query and set the criteria to that day perhaps?

 

[medranoestraaadriana]

I've did it, but it would only show me the items that I've entered for that day. It would not show the ones that have been entered previously.

 

[vbaInet]

Your question was, " On this same db, If I ever want to go back and see the inventory for just one day. How would I do it? " but you're saying that's not want you want now?

So maybe you want a query to return ALL the records from that day and below?

 

[medranoestraaadriana]

I want to be able to get a report that would show the inventory for a specific day.
Let say that on 7/01/10, I had Item A and Item B. I do the inventory and I see that I have Item C and Item D so I add them up. Any day after that I want to be able to get a report that would show me all the these 4 items that were on the inventory on 7/01/10. If I do the query, it would only show me items: C and D because those are the ones that I entered on 7/01/10

 

[vbaInet]

If there's no record of those Items A and B on that day then I don't see how the report is possible.

 

[YNWA]

You must surely mean you want a stock count of what you had in stock on set day.

So you would need as vba said to count what was entered on that day and before.

If you have a filter to identifiy what has gone out of stock then filter using that in your query ...I think.