duplicates recored in form entry

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

duplicates recored in form entry

Author

Topic

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2007-08-01 : 06:51:02

Hi i've a form where i can enter in many products of the same name where a product can have many shades but only one color of a kind can be entered into the shades text box

I've to check for the shades thats already in the table and for the product because i can have many of the same products but can't have the same shade in the same product,

The problem am having is to how to check this when entering the data into the shades field,

I need to make sure i can have a product that can have the same shade but once the product name is different its ok.. so for example

prod1 can have red, blue, and black

prod2 can have red, blue, and black also...

But if i try to add black to either i get a message saying its already in the table..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-01 : 06:59:42

Good for you!
What is the question?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-01 : 07:13:11

What do the tables look like?
Which indexes do you have?

E 12°55'05.25"
N 56°04'39.16"

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2007-08-01 : 07:17:53

Products
ProductID PK
ProductName

Prod_shade Pk auto number
ProductID
shadesid

Shades
shadesid Pk
sahdeName

Its an many to many relationship.

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2007-08-01 : 09:47:05

What about using something like this but i'd need to modify it to make sure i can enter in the same shade onces its a different products name, can any one help with this.

Private Sub ProductName_BeforeUpdate(Cancel As Integer)
If(Not IsNull(DLookup("[ShadeName]", _
"Shades", "[ShadeName] ='" _
& Me!ShadeName& "'"))) Then
MsgBox "Product has already been entered in the database."
Cancel = True
Me!ShadeName.Undo
End If
End Sub

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources