how to check if the same record is exist before insert it ?

[osfar]

hello .


I'm working in small access program about members of faculty , and keep track of their scientific mission to another countries

i got 2 tables : 1 for member personnel detail (MemberID,MemberName,College,Department,Degree,Gen eral_specifation,
Specialization,Date_Of_Hiring,MasterDate,PHD_Date)

and the other table for mission for each member

(MemberID,MissionType,Date_of_approve,Destination,TravelDate,ReturnDate,
DepartmenapproveDate,FacultyApproveDate,UnivApproveDate,ExtendDate1,ExtendDate2,ExtendDate3)


this is the first form to enter the Member Detail.


-----------------------


and this is the second form to enter the Mission Details (unbounded)
first i pick the member name from the first combobox (which is inserted in the first Form)
thin i fill all mission details
and insert the data using SQL

Private Sub Command31_Click()
Dim strSQL As String
strSQL = "insert INTO DData([member_ID],[Mission_ID],[order],[Destination],[travelDate],[returnDate],[extend1],[extend2],[extend3],[depCon],[FacCon],[collCon]) Values ('" & Me.Combo0.Column(0) & "','" & Me.Combo3.Column(0) & "','" & Me.m1 & "','" & Me.m3 & "','" & Me.m4 & "','" & Me.m5 & "','" & Me.m6 & "','" & Me.m7 & "','" & Me.m8 & "','" & Me.m9 & "','" & Me.m10 & "','" & Me.m11 & "');"
CurrentDb.Execute strSQL
End Sub

now i got some questions
1- how to check if i inserted this mission before for the same Member ?
2- i have 4 types of missions , each type can be extended for specific times and specfic period
Type1 , Type2 ,Type 3 , Type 4
for example Type 1 period is 4 years and can't be extended
Type 2 can be extended to 2 years Maximum and each extend is 3 months only
how to handle that in my tables
3- how to make a report from two tables if i want to show missions for each member ?

[orange]

how to check if i inserted this mission before for the same Member ?

What field or combination of fields makes a record unique in your table DData?
Make a unique composite index of those fields and Access will not allow duplicates.
You can trap the error when such an attempt occurs and provide a message to the user.
Another option is to use a Dcount(your criteria) >0 -which means a record with (your criteria) already exists - so take some other action, such as a message...

[osfar]

i can use the order and travel date as unique keys , now how prevent duplicate ?

[orange]

see http://www.geeksengine.com/article/composite-unique-index-access.html


DCount to check for duplicates

[osfar]

I've done the tutorial exactly , but it only prevent duplicates in one field , even if the other fields i specified got different data
how to fix that

[osfar]

see http://www.geeksengine.com/article/composite-unique-index-access.html


DCount to check for duplicates

thanks man it worked , but when i try to insert the same row i don't get any error messages but the row isn't inserted