Access VBA code require on calculated field for duplicate records without entry

[Sandhya1988]

Hello friends, Can anyone help on my issue

i have attached the screenshot with explanation

Brand, Model & Item fields are text fields and Items Description is calculated filed ([Brand] & " " & [Model] & " " & [Item]). So in Brand, Model & Item fields is need to allow duplicate records because same Brand is repeated with different models & different Items and same Model is repeated with different brands. And sometimes user using only Brand without Model & Item both of is blank as per above shown screenshot. And sometimes using only Model or Item as per above screenshot. So user however using the above fields then finally doesn’t repeat duplicate records in the Item Description. Above ID: 57 & 69 1st row and last row item details are same so don’t allow this type of duplicate records in the Item Description. If user force to entering the duplicate record then will show a message (Already Exist) and cancel it. In this case how to use the VBA code on Brand, Model & Item fields?? i think if using VBA code on Brand, Model & Item fields then don’t allow the duplicate records in this fields. I think possible only on Items Description?? So can anyone please replay how to use VBA code?

[dsatino]

Just add a unique index on the description column

[Seth Schrock]

It would be simpler if you just set an index on your description field and set it to not allow duplicates. Then you wouldn't have to write any VBA.

On the other hand, you shouldn't store the three fields concatenated together (which is what the Item Description Field is). One of the major points of database normalization is that you don't store calculated fields. If you need to view them calculated, then concatenate them in a query. Otherwise, if you need to make a change to your Model field, you have to make the change twice. The same goes for the other two fields.

If you make this change, then you would need to use VBA. What I would recommend is to use a DCount function in the form's Before_Update event that counts the number of records that has the same Brand, Model, and Item. If it returns anything greater than 0, then cancel the event (which stops the entry of the new record).

[Sandhya1988]

But there is no options select to unique or index on the Item Description column. Because this is calculated filed so they options is shown hide.
(Item Description field is using purpose of count final No. of products in other forms.)

So you mean calculated filed is change to in the query? Then how to do changes and how can i give a code can you please help how to do this. I have attached the db also.

[Sandhya1988]

attached the db also

[Seth Schrock]

The query would create the calculated field without it needing to be stored, like so:

SELECT Brand
, Model
, Item
, Brand & (' ' + Model) & (' ' + Item) As ItemDescription
FROM Products

You would then create a form based on this query. As you enter the data into each field, the ItemDescription field will automatically be updated.

As to stopping duplicated from being added, add the following to the form's Before_Update Event:

Dim strCriteria As String
strCriteria = "Brand = '" & Me!Brand & "' And Model = '" & _
              Me!Model & "' And Item = '" & Me!Item & "'"

If DCount("*", "Products", strCriteria) > 0 Then
    MsgBox "Duplicate Record"
    Cancel = True
End If

[Sandhya1988]

Thank you so much its working perfectly :)

[Seth Schrock]

No problem. Glad I could help.

[hulk009]

can i have a copy of ur structure data base please