Solved How to prevent duplicates in subform field (1 Viewer)

hoohal

New member
Local time
Today, 19:13
Joined
Jul 29, 2020
Messages
4
Hi all,

I am beginner to ms access.
I made a very simple form in ms access. The purpose of the form is to show the material list of each Job.
The idea of the subform below is to select the materialcode from the droplist and the remaining filed (such as materialdescription and Unit) would be auto filled.

1596026034976.png


The issue I am facing is the duplicated values in materialcode column.
I tried to prevent duplicated values by changing the indexed of materialcode in Material Invoice Table to (Duplicates No).
However the relationship between Material Invoice Table and Material List Table is one to many as shown below. So changing the indexed is not valid.
Also, once I change the indexed to (Duplicates No) the remaining fields (materialdescription and Unit) are not auto filled as before.

1596026347880.png


I do not want to have duplicated materialcode for the same Job No. is there any mistakes in the relationship.

I hope my explanation is clear. Hope anyone could help me.

Regards.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:13
Joined
Oct 29, 2018
Messages
21,358
Hi. Welcome to AWF!

Have you tried creating a multi field index for your junction table?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:13
Joined
May 7, 2009
Messages
19,169
Youll have to implement the validation on the form level. Use beforeUpdate event of materialcode of subform to Count if the materialcode already exists on the table. Does the subfrm has master/child link fields? It should.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:13
Joined
Oct 29, 2018
Messages
21,358
Yes. It did works.
I really appreciate your help.
This feature is new to me. how could two fields could work as primary key.
can be utilized for any case ?

Thank you again
Hi. You're welcome. I rarely use it, but I know it's available. Just don't go crazy and take it slow. There may be repercussions if you get too carried away using it elsewhere. Good luck with your project.
 

hoohal

New member
Local time
Today, 19:13
Joined
Jul 29, 2020
Messages
4
Youll have to implement the validation on the form level. Use beforeUpdate event of materialcode of subform to Count if the materialcode already exists on the table. Does the subfrm has master/child link fields? It should.

It did not work. I found a code for beforeupdate suggested by one of this forum member CEH as shown below. However, the problem with this code that we cannot use the same materialcode for different JobNo (primary key).In the other words, the code used in beforeupdate event consider the duplicated value regardless which JobNo is this code belong to. So unfortunately this code does not work in this case.

1596051127758.png
 
Last edited:

Users who are viewing this thread

Top Bottom