Need some help preventing duplicate entries please.

[dmon2010]

Hi,
I'm pretty new to Access and I've created a table and a bunch of forms and everything is working good. My problem is that I want to avoid entering duplicate items based on 3 fields and can't figure out how to get it done. I read something about indexing, but tbh (like I said) I'm a total noob at this and have no idea what to do. Any help would be greatly appreciated.

I dont know what I should post here for anyone to be able to look and and maybe help me out, but I'm using Aceess 2007, my only table (which all of my forms, queries and report work off of) is MainInventory. The 3 fields that need to be checked for duplicates are Item, PN and Version (but version doesn't always apply and is sometimes a blank field).
For example:

Item = Filter
PN = 3665
version = A

or

Item = Bracket
PN = 45589
version = (blank)

When entering new data, I don't want to create another entry if I already have this item in inventory. I'd like to get a error message that says I already have this item (this way I can just go in and change the quantity) or something similar.



I tried looking around the forums, but didn't find anything that I thought would apply to me. Any help or pointers would be greatly appreciated.

[June7]

Could try setting the 3 fields as a compound key, with Version as Indexed: Yes(Duplicates okay)

[dmon2010]

OK thanks. How would I do that?

[June7]

Table in design view, select the 3 fields simultaneously by holding the shift key and right click, 'Primary key'. Then select each field individually and set its properties at the bottom.

[dmon2010]

Sorry it took so long for me to reply. I tried what you suggested, but since I don't always have a 'version' for my items, the field is mostly blank so I get an error about not being able to have a null value in a primary key field.

[mrojas]

Will there ever be a time when a record is missing the Item or PN fields?
If these will always be provided, simply check if the Version is blank or null.
You could do this with a simple If statement:
If IsNull([Version]) or ([Version]="") then
' Use the DCount function to determine if there is a duplicate record
If DCount(Insert criteria leaving out the Version field)>0 then ' There's a duplicate
' Display a message, then exit routine
Exit Sub
End If
else
' Do same as above but include the Version
If count is>0 you have a duplicate, else add a record
End If

[June7]

That or have the Version field default to 0 or x or None.

[dmon2010]

Will there ever be a time when a record is missing the Item or PN fields?
If these will always be provided, simply check if the Version is blank or null.
You could do this with a simple If statement:
If IsNull([Version]) or ([Version]="") then
' Use the DCount function to determine if there is a duplicate record
If DCount(Insert criteria leaving out the Version field)>0 then ' There's a duplicate
' Display a message, then exit routine
Exit Sub
End If
else
' Do same as above but include the Version
If count is>0 you have a duplicate, else add a record
End If

OK thanks. Where would I put this? Sorry if that seems like a dumb question, but like I said, I'm new at this.

[mrojas]

Not know exactly what input form you're using and its look, I will assume that whatever form you're using has some sort of button the user clicks when done, or when going to next record. I'd put the code behind this button.
Maybe you could send some screenshots of your input forms.