How to check the value of field in a new record is not present in 2 fields of the table?

[Lparra]

Hello,

I have a table that stores Parts information = "Part Data", I have a Master Part Number = "Part Number" and also I have an Alternate part number = " Alt Part Number" so for example Part Number "06178" could have this alternate part numbers E40SE011, 20000815, N104989 which will be all stored in the Alt Part Number field.

When I create a new record currently I am runing a Dlookup before update to check that the part is noot existing in the field "Part Number" and if it exist gives me an error message but I need to check also in the Alternate Part number field to make sure is not existing under one of the alternate part numbers.

This is what I have right now:


Private Sub Text95_BeforeUpdate(Cancel As Integer)

Dim Answer As Variant
Answer = DLookup("[SSI Part Number]", "Part Data", "[SSI Part Number] = '" & Me.Text95 & "'")
If Not IsNull(Answer) Then
MsgBox "The part number you are trying to create is already in use." & vbCrLf & "Please double check and try again." & vbCrLf & " " & vbCrLf & "Luis Parra.", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate Part Number Found"
Cancel = True


Me.Text95.Undo
Else:
End If


End Sub

How do I check on the aAlternate Part NUmber Field too?

I would appreciate any help!

[ranman256]

dont use BeforeUpdate, it just causes problems.

use Afterupdate.

[Lparra]

I will use Afterupdate. Regarding how to check also on the Alternate Part Number field is Dlookup capable of checking multiple fields in a table?
How could I achieve my goal of checking the Main Part Number and Alternate Part number fields to make sure is not used already in any of those 2 fields?

Thank you very much for your help.

[Lparra]

Somebody willing to help?

Thank you!

[ssanfu]

The first problem is that you have spaces in object names. You should NEVER use spaces in names.
Next, your table is not normalized - it is not even in First Normal Form (1NF).

I have a Master Part Number = "Part Number" and also I have an Alternate part number = " Alt Part Number" so for example Part Number "06178" could have this alternate part numbers E40SE011, 20000815, N104989 which will be all stored in the Alt Part Number field.

So, in part, your table "tblPartNum"looks like this:

Part Number	Alternate part number
06178	E40SE011, 20000815, N104989

This is from Rogers Access Blog:

The First Normal Form, or 1NF, is the very lowest, basic arrangement of fields in a table. If your table is not in 1NF, then it isn't really a table.
Sadly, many novice databases are not even in 1NF.

Definition
A table is said to be in First Normal Form if:
1) there is no row or column order
2) each row (record) is unique
3) each row by column value (field) contains exactly one value
4) there are no repeating columns

Because you have multiple values in one field, you are violating Rule 3.
You could change the table design to

Part_Number	Alt_Part_Number1	Alt_Part_Number2	Alt_Part_Number3
06178	E40SE011	20000815	N104989

But now you are violating Rule #4 because you have repeating fields.



Actually, your design should be 2 tables
Main table "tblPartNum":

PartNum_PK	Part_Number
1	06178

and the 2nd table "tblAltPartNum":

AltPartNumID_PK	PartNum_FK	Alt_Part_Number
1	1	E40SE011
2	1	20000815
3	1	N104989

tblPartNum.PartNum_PK ----> tblAltPartNum.PartNum_FK


Then it is relatively easy to check to see if a part number is in either table.

[Lparra]

Thank you very much for your time and help!!
I will redesign my tables as you suggested and I will take it from there.

[Lparra]

Hello Steve,
I have created my tables with the design you recommended.

Main table "tblPartNum":
PartNum_PK Part_Number
1 06178

and the 2nd table "tblAltPartNum":
AltPartNumID_PK PartNum_FK Alt_Part_Number
1 1 E40SE011
2 1 20000815
3 1 N104989

I created the relantionship as: tblPartNum.PartNum_PK ----> tblAltPartNum.PartNum_FK
I created a Form named Part Data which has a subform where the Alt_Part_Numbers are entered
I am trying to figure out the code to make sure that when I enter a new "Part_Number" it checks both tables before letting me save that part.
I have this so far but I dont think is working ��

Private Sub PartNumber_BeforeUpdate(Cancel As Integer)
Dim MainCheck As Variant
Dim AltCheck As Variant


MainCheck = DLookup("[Part_Number]", "tblPartNum", "[Part_Number] = '" & Me.PartNumber & "'")

AltCheck = DLookup("[Alt_Part_Number]", "tblAltPartNum", "[Alt_Part_Number] = '" & Me.PartNumber & "'")

If Not IsNull(MainCheck) And IsNull(AltCheck) Then
MsgBox "The part number you are trying to create is already in use." & vbCrLf & "Please double check and try again." & vbCrLf & " " & vbCrLf & "Luis Parra.", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate Part Number Found"
Cancel = True
PartNumber.Undo

Else:
End If


End Sub


All I need is to make sure that the new Part is not already used as an Alternative Part Number or already in the parts table.
Could you help me fix the code? I really appreciate your help. Thank you very much!

[ssanfu]

Have a look at this. I don't like DLookup(), so I modified the code to use queries in code. But your code using DLookup now works......

[Lparra]

Hello Steve,

Is perfect!
I can't tank you enought for your time and kindness!!!

Have a great day!

[ssanfu]

You are welcome.

Good luck with your project.......