VBA to avoid duplicate supplies item

[hfsitumo2001]

Hello any body can help how to avoid duplicate supplies in one go of the data entry in the form?
As you can see in the attached form, I want the user as he type the same supplies name there will be a massage saying " this is duplicate, please enter it in the next data entry.

Thank you.

Frank

 

[CJ_London]

Looks like user is typing the name rather than selecting from a combo

using a combo is the first step, removing previously selected items is the second

 

[hfsitumo2001]

It is already using combo and it is already automatic finding the name of supplies, but for the next row, even though we have told do not enter again for the same go, but they still try to enter, so I want the before update in the column, as they enter the same name, there is an alarm and there is an error message.

Thank you.

Frank

 

[XPS35]

Just set an unique index on the (order line?) table.

 

[The_Doc_Man]

The technique of the cascading combo box might help here. To do what you describe means you must plan ahead to include your method to prevent duplication. Let us say you are adding things to a shopping list. You use your combo box to select something to add to that list. To prevent duplicates, you might want the .RowSource of the combo box to include a WHERE clause. I'll use "air" code, meaning I don't know the names or setup of your data. I WILL presume that you have both an item name and some sort of internal unique item code for this.

SELECT MIL.ItemName, MIL.ItemCode, {other fields as needed} FROM MasterItemList MIL WHERE MIL.ItemCode NOT IN ( SELECT SIL.ItemCode FROM SelectedItemList SIL ) ;

Then when you add an item to your selection list, requery the combo box so that the list will now exclude the item you just added. I checked. You CAN requery just the combo box without having to requery everything. This method involves a SUB-QUERY which is the topic you should search to learn more about it.

 

[CJ_London]

Then when you add an item to your selection list, requery the combo box so that the list will now exclude the item you just added

would just add that you need to do more in a continuous form otherwise previous entries will appear to go blank

So you need to modify the rowsource to exclude previously entered items in the combo enter or perhaps gotfocus event then remove the exclusion on the combo exit of lostfocus event. There is no need to requery the combo as modifying the rowsource does that for you - but just for that single instance on the form. If you were to requery, all rows will appear to go blank.

Also means you would need to manage the situation if the user goes back to change an item (it will no longer be visible in the dropdown).

I would suggest the principle used in this thread would be more appropriate

A multi select form using unbound check boxes

Thought I would put this up since it is a fairly common topic and I have put it up elsewhere - selecting records from a list for onward processing without the need for a table based tickbox. Similar in concept to the conditional formatting version I provided here...

www.access-programmers.co.uk

The bit that matters is basically when a user enters a value the relevant uniqueID for the product? is checked against the hidden textbox. If it is already there then generate an 'already entered' message and presumably clear the combo, otherwise add it to the textbox

Still needs more work to remove from textbox if user changes the selection and perhaps the textbox needs populating on some event (such as current) if user can edit a previously created issue.

So two methods, one to restrict what users can select, one to create a a message if user chooses an item already chosen

 

[hfsitumo2001]

This is the sample to avoid duplicate item at one go of the data entry in the form., but my problem as you can see in the previous form, my form used sub form, so I am afraid it is not going to work.

If DCount("*"."Inventory","ItemCode='" & Me.Txt_ItemCode.Value & "' and ItemCode='" & Me.Txt_ItemCode.Value & "'")>0 Then
MsgBox "This item has already been entered, please try another supllies", vbInfromation, "Duplicate Item Number"
Me.Txt_ItemCode.SetFocus
Cancel = True

Anyone can help me?

Thank you

Frank

 

[Gasman]

Why are you checking itemcode twice?

 

[Pat Hartman]

The way to make a multi-field unique index is to use the Indexes dialog. See the picture. You can have from 1 to 10 fields in an index. Put the index name on the first line of the index. Then choose 1 - 10 fields to include. The sample shows two. When the first line of the index (the one with the name) is clicked, you will see the index options. Choose Unique.

 

[hfsitumo2001]

Why are you checking itemcode twice?

Because the user will input the ckout list by department, by requester name. so on one go of the data entry only allowed to enter one type of supply. but user try again to type the same supplier for the different requester which create error message and they do not know how to cancel it.

Thank you,

Frank

 

[Gasman]

Because the user will input the ckout list by department, by requester name. so on one go of the data entry only allowed to enter one type of supply. but user try again to type the same supplier for the different requester which create error message and they do not know how to cancel it.

Thank you,

Frank

That makes no sense to me whatsoever?

 

[arnelgp]

you can use Recordset to check if there is already have same item on it.
use BeforeUpdate Event of the Txt_Itemcode textbox:

Private Sub Txt_Itemcode_BeforeUpdate(Cancel As Integer)
Dim rs As DAO.Recordset
If Me.NewRecord Then
    Set rs = Me.RecordsetClone
    Set rs = rs.Clone
    rs.FindFirst "Itemcode ='" & Me!Txt_Itemcode & "'"
    Cancel = Not (rs.NoMatch)
    Set rs = Nothing
    If Cancel Then
        Msgbox "Item already in the list."
    End If
End If
End Sub

much better if you have Autonumber in your subform, so you can check also when
you are Editing a record:

Private Sub Txt_Itemcode_BeforeUpdate(Cancel As Integer)
Dim rs As DAO.Recordset

    Set rs = Me.RecordsetClone
    Set rs = rs.Clone
    rs.FindFirst "Itemcode ='" & Me!Txt_Itemcode & "'"
    Cancel = Not (rs.NoMatch)
    If Cancel Then
        Cancel = (Me!ID <> rs!ID)
    End If
    Set rs = Nothing
    If Cancel Then
        Msgbox "Item already in the list."
    End If

End Sub

 

[Pat Hartman]

Even if you use code to give the user a more friendly message, you should also always use whatever declarative RI you can so set up the unique index also.

 

[hfsitumo2001]

you can use Recordset to check if there is already have same item on it.
use BeforeUpdate Event of the Txt_Itemcode textbox:

Private Sub Txt_Itemcode_BeforeUpdate(Cancel As Integer)
Dim rs As DAO.Recordset
If Me.NewRecord Then
    Set rs = Me.RecordsetClone
    Set rs = rs.Clone
    rs.FindFirst "Itemcode ='" & Me!Txt_Itemcode & "'"
    Cancel = Not (rs.NoMatch)
    Set rs = Nothing
    If Cancel Then
        Msgbox "Item already in the list."
    End If
End If
End Sub

much better if you have Autonumber in your subform, so you can check also when
you are Editing a record:

Private Sub Txt_Itemcode_BeforeUpdate(Cancel As Integer)
Dim rs As DAO.Recordset

    Set rs = Me.RecordsetClone
    Set rs = rs.Clone
    rs.FindFirst "Itemcode ='" & Me!Txt_Itemcode & "'"
    Cancel = Not (rs.NoMatch)
    If Cancel Then
        Cancel = (Me!ID <> rs!ID)
    End If
    Set rs = Nothing
    If Cancel Then
        Msgbox "Item already in the list."
    End If

End Sub

Thank you Arnel, I will try it, but I see your suggestion is very good.

Thank you,

Frank

 

[hfsitumo2001]

you can use Recordset to check if there is already have same item on it.
use BeforeUpdate Event of the Txt_Itemcode textbox:

Private Sub Txt_Itemcode_BeforeUpdate(Cancel As Integer)
Dim rs As DAO.Recordset
If Me.NewRecord Then
    Set rs = Me.RecordsetClone
    Set rs = rs.Clone
    rs.FindFirst "Itemcode ='" & Me!Txt_Itemcode & "'"
    Cancel = Not (rs.NoMatch)
    Set rs = Nothing
    If Cancel Then
        Msgbox "Item already in the list."
    End If
End If
End Sub

much better if you have Autonumber in your subform, so you can check also when
you are Editing a record:

Private Sub Txt_Itemcode_BeforeUpdate(Cancel As Integer)
Dim rs As DAO.Recordset

    Set rs = Me.RecordsetClone
    Set rs = rs.Clone
    rs.FindFirst "Itemcode ='" & Me!Txt_Itemcode & "'"
    Cancel = Not (rs.NoMatch)
    If Cancel Then
        Cancel = (Me!ID <> rs!ID)
    End If
    Set rs = Nothing
    If Cancel Then
        Msgbox "Item already in the list."
    End If

End Sub

Thank you Arnel, I have tried your suggestion and it works like a chant

Frank

 

[Pat Hartman]

Don't forget to also set RI. That works behind the scenes no matter what process is updating the table.

 

[hfsitumo2001]

Don't forget to also set RI. That works behind the scenes no matter what process is updating the table.

What do you mean Pat by Don't forget to also set RI

 

[hfsitumo2001]

Private Sub Txt_Itemcode_BeforeUpdate(Cancel As Integer) Dim rs As DAO.Recordset If Me.NewRecord Then Set rs = Me.RecordsetClone Set rs = rs.Clone rs.FindFirst "Itemcode ='" & Me!Txt_Itemcode & "'" Cancel = Not (rs.NoMatch) Set rs = Nothing If Cancel Then Msgbox "Item already in the list." End If End If End Sub

Hi Arnel, I have a question, maybe because It was too long, 10 years ago I never touched anymore the MS Access VBA. so I forgot the naming convention. In my case now, the name of the field of Form, I made it as Itemcode, that should have been txt_ItemCode. So now the name of the field of the form is the same as the name of the field of the table: ItemCode.

Would there be a problem in executing the before update event?.

Thank you for your enlightening me.

Frank

 

[Pat Hartman]

Refer back to #9 where I showed you a picture of how to create a multi-column unique index. RI = Referential Integrity. It is enforced by the database engine REGARDLESS of how a record is updated. It encompasses enforcing relationships between tables as well as indexes and data macros and table level validation. The code you are using will give the user an understandable error message and prevent the form from saving the record. However, if you updated that table from two different forms, you would need to DUPLICATE your code but if you forgot to do that, the unique index would be the backstop. The database engine would prevent the duplicate from being saved. Or, if you ran an update query or an append query that caused a duplicate, the database engine would prevent the duplicate from being saved.

I am not saying there is anything wrong with validating and preventing the update in the BeforeUpdate event in the form. All I am saying is that, the method is insufficient. If you can get the database engine to save you from yourself, you should always take advantage of the opportunity You only need to use the RI method to protect the data. The validation method is extra and it will likely give the user a better error message so I would probably also do both. BUT, if I were to only do one, then the ONE is RI.

 

[arnelgp]

you just change txt_ItemCode in the code with ItemCode.
you also need to Rebuild your BeforeUpdate event since the event is for
txt_ItemCode and not for ItemCode textbox.