New/Update button error

[Stephen D Cook]

I am building a repair database.
It has 4 tables linked together.

The EquipmentType Table has: (PK) EquipTypeCode (text),
EquipTypeDetail(text).

The Equipment Table has: (PK)SerialNumber(text),(FK to EquipmentType
Table) EquipmentType(text).

The Tech Table has: (PK)TechNumber(number), TName(text).

The TechProblem Table has: (PK) Tech_PKey(AutoNumber), (FK to Tech
Table)TechNumber(number), TechProblem(text), TechDate(date), (FK to
Equipment Table)SerialNumber(text).

I have a form with a Textbox for Serial Number, and a combo box for
Equipment Type. The form is linked to the Equipment Table. There is an
Add button at the bottom of the form.

I have a subform in the form which has Tech Number textbox, TechDate
text box, and Problem textbox.

What I want to happen, is if the tech enters a serial number and it
already exsists in the Equipment Table, it will add the information
entered into the TechProblem Table. If the tech enters a serial number
and it doesnt exsist in the Equipment Table, it will add the serial
number and type in the Equipment Table and add the information in the
TechProblem Table.

The subform is linked to the main form via the serialnumber field.

When I enter a new serial number into the main form and the tech
information into the sub form, there is no problem.
When I enter a serial number into the main form which is not in the
database and there is already a record in the serialnumber table, I
get a duplicate record error.

What code do I need to add and where do I need to add it.
Im sure its something like:
If Me.txtSN.text = Table.Equipment.SerialNumber Then
INSERT INTO Table.TechProblem
.TechNumber = subform.technumber.text
.TechProblem = subform.techproblem.text
.TechDate = subform.techdate.text
End If
obviously the INSERT code will not work, but I'm sure I need an If
statement to fix this problem.

 

[jmonty]

It is a bit long winded and I am not sure I understand it all, but it sounds
to me that the Serial Number field in the Tech Problem table (foriegn key)
has an index on it and the Duplicates is set to No.

As long as the field is not (or part of) the primary key in the Tech problem
table - you might check to see if you have the Index property set to Yes(No
Duplicates)...try setting it to: Yes (Duplicates OK).

jmonty