why does this code not work?

[Jon123]

If (Me.[Part1b]) = (Me.[Part2b]) Or (Me.Part3b) Or (Me.Part4b) Or (Me.Part5b) Or (Me.Part6b) Or (Me.Part7b) Or (Me.Part8b) Or (Me.Part9b) Or (Me.Part10b) Or (Me.Part11b) Or (Me.Part12b) Or (Me.Part13b) Or (Me.Part14) Or (Me.Part15) Then

Is there a better way to write it?

jon

 

[MarkK]

There is almost certainly a normalization problem with your data, and if you fix that you would also be able to solve this comparison problem much more simply.

But the problem you are asking about is probably the difference between these expressions...

• x = a or x = b or x = c or x = d
• x = a or b or c or d

You probably mean to do the first one. It's unlikely that...

Part5b OR Part6b

...is a valid boolean expression.
hth
Mark

 

[kevlray]

Valid, but what are you trying to achieve?

 

[Jon123]

I'm actually getting an error. If I drop the last to OR's it works.

So I'm building a kit that contains 15 parts all different. As a user selects the parts from a main table to put into a kit I want to verify there all different.
Part1a, Part2a, etc etc are the different parts and can be entered in any order but once you update if that one is already in the kit I want to catch it and stopn it.

 

[MarkK]

This is a one-to-many relationship between the kit and the parts. To model this in a single row in a database is a mistake. This should be done with two tables, and the solution will be way simpler.

 

[arnelgp]

Use a function to do it. On a standard module add this.

Public function IsKitExists(pComp, Paramarray p() as variant) as boolean
Dim sConcat as string
Dim var as variant
Dim bolreturn as boolean
'concatrnate each kit
sConcat="/"
For each var in p
sConcat= sConcat & "/"
Next
'test for duplicate kit
For each var in p
Bolreturn=(instr (sConcat,"/" & pComp & "/")>0)
If bolreturn then exit for
Next
IsKitExists=bolreturn
End function

You pass the kit to compare followed by list of kits to compare with.

Now you call the function to validate:

If IskitExists(me.part1b,me.part2b, ...) Then
'part already exists

Else
'kit does not exist so Okey

End If

 

[Jon123]

It is being done in 2 tables but I need to build the kit as a form because the parts can and do change until we get a matched set if you will.

 

[kevlray]

Without restructuring the tables (which looks like it should be done). The proper syntax for the if statement (I believe) would be like this.

If (Me.[Part1b]) = (Me.[Part2b]) Or (Me.[Part1b]) =(Me.Part3b) Or (Me.[Part1b]) =(Me.Part4b) Or (Me.[Part1b]) =(Me.Part5b) Or (Me.[Part1b]) = (Me.Part6b) Or (Me.[Part1b]) =(Me.Part7b) Or (Me.[Part1b]) = (Me.Part8b) Or (Me.[Part1b]) =(Me.Part9b) Or (Me.[Part1b]) =(Me.Part10b) Or (Me.[Part1b]) =(Me.Part11b) Or (Me.[Part1b]) =(Me.Part12b) Or (Me.[Part1b]) =(Me.Part13b) Or (Me.[Part1b]) =(Me.Part14) Or (Me.[Part1b]) =(Me.Part15) Then

basically every statement between the OR's has to evaluate to a True or False.

 

[plog]

So I'm building a kit that contains 15 parts all different.

kevlray's last post gets you 2/15th of the way there. He does 14 comparisons, what you want requires 105.

Structure your table properly as per Mark's advice.

 

[MarkK]

In SQL, testing for the existence of a related child row with criteria is trivial.

SELECT Count(*)
FROM tKitParts
WHERE ParentKitID = 123
AND PartID = 456

That counts how many parts there already are in kit 123 having part number 456. If the result is > 0, then the part exists.
Or use DCount()...

dim criteria as string

criteria = "ParentKitID = " & Me.ActiveKitID & " AND PartID = " & Me.PartBeingAddedID

If DCount("*", "tKitParts", criteria) > 0 Then
   Msgbox "Part already exists in this kit"
Else
[COLOR="Green"]   'add part to kit[/COLOR]
End If

...but this...

If (Me.[Part1b]) = (Me.[Part2b]) Or (Me.[Part1b]) =(Me.Part3b) Or (Me.[Part1b]) =(Me.Part4b) Or (Me.[Part1b]) =(Me.Part5b) Or (Me.[Part1b]) = (Me.Part6b) Or (Me.[Part1b]) =(Me.Part7b) Or (Me.[Part1b]) = (Me.Part8b) Or (Me.[Part1b]) =(Me.Part9b) Or (Me.[Part1b]) =(Me.Part10b) Or (Me.[Part1b]) =(Me.Part11b) Or (Me.[Part1b]) =(Me.Part12b) Or (Me.[Part1b]) =(Me.Part13b) Or (Me.[Part1b]) =(Me.Part14) Or (Me.[Part1b]) =(Me.Part15) Then

...is a losing battle, because you haven't compared to Part7 to Part11 yet, and you haven't checked for Nulls

hth
Mark

 

[jdraw]

Jon 123,

Is this thread about this same business/issue?
https://www.access-programmers.co.uk/forums/showthread.php?t=295347

 

[Jon123]

no that was creating a sn post