423,109 Members | 2,116 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,109 IT Pros & Developers. It's quick & easy.

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

P: 25
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?
Attached Images
File Type: jpg ScreenshotFinal1.jpg (79.2 KB, 162 views)
Oct 24 '14 #1
Share this Question
Share on Google+
8 Replies


dsatino
100+
P: 393
Just add a unique index on the description column
Oct 24 '14 #2

Seth Schrock
Expert 2.5K+
P: 2,886
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).
Oct 24 '14 #3

P: 25
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.
Oct 24 '14 #4

P: 25
attached the db also
Attached Files
File Type: zip M22.zip (157.9 KB, 44 views)
Oct 24 '14 #5

Seth Schrock
Expert 2.5K+
P: 2,886
The query would create the calculated field without it needing to be stored, like so:
Expand|Select|Wrap|Line Numbers
  1. SELECT Brand
  2. , Model
  3. , Item
  4. , Brand & (' ' + Model) & (' ' + Item) As ItemDescription
  5. 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:
Expand|Select|Wrap|Line Numbers
  1. Dim strCriteria As String
  2. strCriteria = "Brand = '" & Me!Brand & "' And Model = '" & _
  3.               Me!Model & "' And Item = '" & Me!Item & "'"
  4.  
  5. If DCount("*", "Products", strCriteria) > 0 Then
  6.     MsgBox "Duplicate Record"
  7.     Cancel = True
  8. End If
  9.  
Oct 24 '14 #6

P: 25
Thank you so much its working perfectly :)
Oct 27 '14 #7

Seth Schrock
Expert 2.5K+
P: 2,886
No problem. Glad I could help.
Oct 27 '14 #8

P: 1
can i have a copy of ur structure data base please
Jan 10 '18 #9

Post your reply

Sign in to post your reply or Sign up for a free account.