Prevent Users from entering duplicates

[medranoestraaadriana]

I've created a database that keeps an inventory of items that are in and out of the office. Each item has an ID number. Inventory is taken daily and entered in a form.
I want to prevent users from entering an item that's already in the inventory. I know it's under the 'Before Update' field, but I have no idea how to build the code

 

[DCrake]

Use a combo box for the product ID and set a condition on it so the list only contains items that can be selected.

 

[vbaInet]

Welcome to AWF!

Is the ID manually inputted or it's an autonumber field?

What field do you want to be unique (or not duplicated)?

 

[medranoestraaadriana]

The ID is manually imputted because sometimes we have the same item back in the office and sometimes we have new ones.

 

[vbaInet]

So, is it the item ID or the name of the item you don't want entered twice?

 

[medranoestraaadriana]

The item ID
example:
Item ID: 123CCA Description: Scanner Color:White
If I take the inventory for that day and I see that item ID: 123CCA is physically here, I go into the inventory form and try to enter it, but if the item has already been entered before, I want a message to be displayed, stating that the item already exists in the inventory.

 

[vbaInet]

1. On the table, set the Index property of the field to Yes (No Duplicates) or even better set it as the Primary Key
2. On the form's On Error event you could use something like this:

private sub form_error(dataerr as integer, response as Integer)
     Const DUP_ID = 3022
     if dataerr = dup_id then
           msgbox "Duplicate Item ID is not allowed", vbexclamation, "Duplicate ID"
           response = acdataerrcontinue
     end if
end sub

I think it's 3022 but test it first. You can put a msgbox to show what error code it actually is.

 

[medranoestraaadriana]

The thing is that I do want duplicates on the main table, because each unit would have a history, I just don't want duplicates on the form where I enter daily inventory.

 

[vbaInet]

Where does the record you save via this form get saved into? Have a look in the Record Source property of the form.

 

[medranoestraaadriana]

The record gets saved on a main table, from that table I have several queries. From one of those queries I get an inventory form that it's updated everyday. On that form is where I do not want a duplicate because it's an iventory that tracks each item in the office. The items leave, go and might or not might come back.

 

[vbaInet]

Sounding like your db isn't properly normalized. If you don't want duplicates records to be saved into the table but then again you're allowing duplicates via the table directly, you're might run into problems when trying to manipulate records using a query.

In any case, use something like this in the form's Before Update and Before Insert events:

private sub form_beforeupdate(cancel as integer)
    if dcount("*", "[[COLOR=Red][B]Table_Name[/B][/COLOR]]", "[[COLOR=Red][B]ID_field[/B][/COLOR]] = '" & Me.[[COLOR=Red][B]Id_field[/B][/COLOR]] & "'") <> 0 then
         cancel = true
         msgbox "cannot save duplicate", vbexclamation, "Duplicate"
    end if
end sub

Substitute the fields in red with the correct names. That would work as long as you don't have too many people performing this same operation.

If you do, then you should create a Date field and set it's default value to today's date, then set the ID and the Date field as Joint Primary Keys. Then use the code I gave you in my other post.

 

[medranoestraaadriana]

Thanks a lot for your patience and help. I apologize for not being very clear.
This code, would be applied to the table, but I want to apply it to the ‘before Update’ on a text box, and only to the form, not the table.

 

[medranoestraaadriana]

It does not work because I do want duplicates on the main table, I just don't want them on the form.

 

[vbaInet]

It does not work because I do want duplicates on the main table, I just don't want them on the form.

It seems that you believe records are being saved on the form?

This code, would be applied to the table, but I want to apply it to the ‘before Update’ on a text box, and only to the form, not the table.

Let's see. Maybe you will be able to see how both our statements correlate:

In any case, use something like this in the form's Before Update and Before Insert events:

private sub [COLOR=Blue][B]form[/B][/COLOR]_[COLOR=Blue][B]beforeupdate[/B][/COLOR](cancel as integer)
    if dcount("*", "[[COLOR=Red][B]Table_Name[/B][/COLOR]]", "[[COLOR=Red][B]ID_field[/B][/COLOR]] = '" & Me.[[COLOR=Red][B]Id_field[/B][/COLOR]]  & "'") <> 0 then
         cancel = true
         msgbox "cannot save duplicate", vbexclamation, "Duplicate"
    end if
end sub

You can have duplicates when you add records directly on the table, i.e. when you open the table and add records that way. Duplicates will not be applied if adding or amending records via the form.

I think you're also confusing Before Update and Before Insert so I would advice you first of all try it on the Before_Insert event and see what happens.

 

[medranoestraaadriana]

I've tried it and it kind of works.
I getting the 'duplicate' message for any record that I want to enter duplicate or not. It actually does not let me enter anything at all. For Example I have Item ID: 123CCA on the inventory, it would give me the 'duplicate' message if I try to enter it again.
But the same thing would happen with Item ID: 123BBA, and that one is not on the inventory.

I'm using the following code:

Private sub Inventory_beforeupdate(cancel as integer)
if dcount("*", "[Items]", "[Item_ID] = '" & Me.[Item_ID] & "'") <> 0 then
cancel = true
msgbox "Item al ready exists in Inventory", vbexclamation, "Duplicate"
end if
end sub

 

[vbaInet]

I see the problem. For the Before_Update replace the first line:

if dcount("*", "[Item[COLOR=black]s[/COLOR]]", "[Item[COLOR=black]_ID[/COLOR]] = '" & Me[COLOR=Black]![Item_ID][/COLOR]   & "' AND [Item_ID] <> '" & Me[COLOR=Red][B]txtBoxNameforID[COLOR=Black].OldValue[/COLOR][/B][/COLOR] & "'")  <> 0

Substitute the bit in red with the name of the textbox control.

Then for the Before_Insert event use what you already have:

if dcount("*", "[Item[COLOR=black]s[/COLOR]]", "[Item[COLOR=black]_ID[/COLOR]] = '" & Me.[Item[COLOR=black]_ID[/COLOR]]   & "'") <> 0

 

[medranoestraaadriana]

Would it make a difference if I told you that my property source for this form is not my main table but a query.

 

[vbaInet]

It wouldn't make a difference. Have you tried the code yet?

There's a little typo, I missed out a dot in the Before_Update code:

Me[COLOR=Red][B].[/B][/COLOR]txtBoxNameforID

 

[medranoestraaadriana]

I've tried it and it does not work. Can I upload the database on the forum, that way you could take a look at it.

 

[vbaInet]

Yes of course. Strip it of sensitive information and put some dummy data. Post a zipped file.