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?
8 3171
Just add a unique index on the description column
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).
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.
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
-
Thank you so much its working perfectly :)
No problem. Glad I could help.
can i have a copy of ur structure data base please
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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....
|
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...
|
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...
|
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,...
|
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...
|
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...
|
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,...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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
|
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...
|
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...
| |