referential integrity in access DB

[lasherz2]

:confused: Hi, i have four tables each capturing data on assets.Each asset is required to have a unique code and serial too.So no entry is supposed to allow a double entry epecially the code and the serial.How can i implement this is in access?Someone knows how to go about it?If possible, details will greatly help.Thanx

[QVeen72]

Hi,

Create a "Primary Key" for that Serial Number Field. and If u want , u can make it "AutoGenerate", If u insert a Record into that table, Serial Number will be automatically assigned to that record.

REgards
Veena

[lasherz2]

hi, i have tried that but still had the problem.I cannot make the serial to autogenerate coz it comes with the assets.As for the code,it is allocated to each incoming assets eg. a computer will be identified by a unique code apart from the serial.Is there another way i can do it?

[jireh]

hi, i have tried that but still had the problem.I cannot make the serial to autogenerate coz it comes with the assets.As for the code,it is allocated to each incoming assets eg. a computer will be identified by a unique code apart from the serial.Is there another way i can do it?

You can use a function check so that your data cannot be doubled or redundant.
For example (let me use my own example and you get the idea, and let me use only one table then the other is yours...I think you can make it after this :lol!.) I have a customer table and have fields CusNum, CusName, CusAddress (this table has no primary key but we can prevent the double entry in CusNum through checking)

this code is to check if the CusNum is already exist

Function savecheck() As Boolean
      Dim db As DataBase
      Dim rsCustomer As Recordset

      Set db = OpenDatabase(App.Path & "\sample.mdb")

      savecheck = True

      Set rsCustomer  = db.OpenRecordset("SELECT * FROM [Customer Table] WHERE CusNum=" & txtCustomerNumber.Text & " ")
             If Not rsCustomer.BOF Then
                  savecheck = False
                  MsgBox "Customer Number already exists.", vbInformation
                  txtCustomerNumber.SetFocus
                  SendKeys "{Home}+{End}" 
                  Exit Function
             End If
End Function 

In the Save button of VB put this code..

Private Sub SaveCmd_Click()
     If savecheck Then
           ...put your code here to save your data
     End If
End Sub

[lasherz2]

thanx Jireh.Tis workin fine.God bless all u helped...