473,322 Members | 1,494 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

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

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, 305 views)
Oct 24 '14 #1
8 3171
dsatino
393 256MB
Just add a unique index on the description column
Oct 24 '14 #2
Seth Schrock
2,965 Expert 2GB
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
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
attached the db also
Attached Files
File Type: zip M22.zip (157.9 KB, 85 views)
Oct 24 '14 #5
Seth Schrock
2,965 Expert 2GB
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
Thank you so much its working perfectly :)
Oct 27 '14 #7
Seth Schrock
2,965 Expert 2GB
No problem. Glad I could help.
Oct 27 '14 #8
can i have a copy of ur structure data base please
Jan 10 '18 #9

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

Similar topics

5
by: Richard Holliingsworth | last post by:
Hello: Thanks for reading this post. I need to create a metrics (form or report - I don't care which) to display calculated fields about the database (A2002 front end to SQL Server 2K) 1) I...
2
by: Ross | last post by:
i have less hair now than i used to...i have a database with many duplicate records in it with only one exception, there is one field with the date it was put in the database and that is different....
4
by: Andrew Chanter | last post by:
I have been working with an A97 database that performs a data processing function. It imports data from a flat text file then uses a dao transaction that executes a number of sql statements (about...
4
by: bpneary1 | last post by:
First of all, I want to thank everyone who posts messages in this group. I am very new to VB and developing full-blown database apps, and I have learned a great deal by reading these posts, so I...
2
by: jmarr02s | last post by:
I don't know what I am doing wrong I get duplicate records when I query Here is my SQL query code: SELECT Utilization_T.Facid, Utilization_T.Year, Utilization_T.Beds, Utilization_T.LicBeds,...
3
by: kelley.l.turner | last post by:
Hi all, I am very new to MS Access so please bear with me! I have created a simple calculated field in my data entry form, yet when I view my data table or try to generate a report based on...
4
theaybaras
by: theaybaras | last post by:
Hi All, I am creating a databse of articles and related information. I need to find a way to prevent the user from inputting duplicate information in a few forms. In one of these forms I can...
3
by: pbrown | last post by:
Hi. I'm a relatively new Access 2000 user, and I've got a problem thats got me stumped. I have a table of street addresses and property numbers that looks something like this: Name, Location,...
5
by: Brett Barry: Go Get Geek! | last post by:
Hello, Can someone please paste VBA code and how I would go about doing this?: I have a customer table with over 6000 duplicates in the field called "Customer". While the customer names may...
2
by: noclueforid | last post by:
Here is my calculation: =DateAdd("d",-1,DateAdd("d",56,DateAdd("d",-(IIf(Weekday()=1,"0",Weekday(-1))),))) I get "#Error" in the field when displayed. However, when I enter the for the...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.