Adding new records to table via query/form

[Guest]

I have a form that is based on a query. The problem I have with it is that I
can not add a new record with out an error, I can update the current records
I just can not add new ones. The specific error I'm get is "Microsoft jet can
not find a record in the table 'Flexible Converting Equipment with key
matching field 'Special Equipment 2'. This error happens both in the form and
the query. It does not for the under lying table.

My basic structure for this is Form name = [frmStructure builder] ; Query
name = [Structure builder query]
this query is linked to several tables

Tables names=
[Structures builder] , [Material Class Properties], [Material Class
Properties_1], [Material Class Properties_etc...], [Flexible Converting
Equipment], [Flexible Converting Equipment_1],[Flexible Converting
Equipment_etc...],

The materials and the equipment tables are jointed to the main structure
builder table at several points and are in fact just three tables , the
equipment and materials are just dupicates since there will be several
materials combined to make product along with several pieces of equipment.

The [structure builder] table has lookup fields to these same two tables
used in the query [structure builder query]

I've tried removing the [special equipment 2] referenced in the query, but
the error then just moves to the next piece of equipment reference [special
equipment 1]

My query is complex but I've reduced it down for to the essential of this
problem below:


SELECT [Structure Builder].ID, [Structure Builder].[Base Layer Amount],
[Structure Builder]![Base Layer Material] AS [BL Material], [Material Class
Properties].Density, [Structure Builder].[Tie Layer1a Amount], [Structure
Builder]![Tie Layer1a Material] AS [TL1a Material], [Structure
Builder]![Special Equipent 1] AS [Special Equipment 1], [Structure
Builder]![Special Equipment 2] AS [Special Equipment 2]
FROM [Flexible Converting Equipment] AS [Flexible Converting Equipment_5]
INNER JOIN ([Flexible Converting Equipment] AS [Flexible Converting
Equipment_4] INNER JOIN ([Material Class Properties] INNER JOIN ([Material
Class Properties] AS [Material Class Properties_1] INNER JOIN [Structure
Builder] ON [Material Class Properties_1].ID = [Structure Builder].[Tie
Layer1a Material]) ON [Material Class Properties].ID = [Structure
Builder].[Base Layer Material]) ON [Flexible Converting Equipment_4].ID =
[Structure Builder].[Special Equipent 1]) ON [Flexible Converting
Equipment_5].ID = [Structure Builder].[Special Equipment 2]
ORDER BY [Structure Builder].ID;

What can I do to fix this?

 

[Guest]

I believe I found problem. When I added new record some of the defaults were
blank - the query did not like that. Once I added default values in each of
the fields for the structure builder table I could add new records to from
form. Not sure if this was best approach but it worked.


--
David McKnight

I have a form that is based on a query. The problem I have with it is that I
can not add a new record with out an error, I can update the current records
I just can not add new ones. The specific error I'm get is "Microsoft jet can
not find a record in the table 'Flexible Converting Equipment with key
matching field 'Special Equipment 2'. This error happens both in the form and
the query. It does not for the under lying table.

My basic structure for this is Form name = [frmStructure builder] ; Query
name = [Structure builder query]
this query is linked to several tables

Tables names=
[Structures builder] , [Material Class Properties], [Material Class
Properties_1], [Material Class Properties_etc...], [Flexible Converting
Equipment], [Flexible Converting Equipment_1],[Flexible Converting
Equipment_etc...],

The materials and the equipment tables are jointed to the main structure
builder table at several points and are in fact just three tables , the
equipment and materials are just dupicates since there will be several
materials combined to make product along with several pieces of equipment.

The [structure builder] table has lookup fields to these same two tables
used in the query [structure builder query]

I've tried removing the [special equipment 2] referenced in the query, but
the error then just moves to the next piece of equipment reference [special
equipment 1]

My query is complex but I've reduced it down for to the essential of this
problem below:


SELECT [Structure Builder].ID, [Structure Builder].[Base Layer Amount],
[Structure Builder]![Base Layer Material] AS [BL Material], [Material Class
Properties].Density, [Structure Builder].[Tie Layer1a Amount], [Structure
Builder]![Tie Layer1a Material] AS [TL1a Material], [Structure
Builder]![Special Equipent 1] AS [Special Equipment 1], [Structure
Builder]![Special Equipment 2] AS [Special Equipment 2]
FROM [Flexible Converting Equipment] AS [Flexible Converting Equipment_5]
INNER JOIN ([Flexible Converting Equipment] AS [Flexible Converting
Equipment_4] INNER JOIN ([Material Class Properties] INNER JOIN ([Material
Class Properties] AS [Material Class Properties_1] INNER JOIN [Structure
Builder] ON [Material Class Properties_1].ID = [Structure Builder].[Tie
Layer1a Material]) ON [Material Class Properties].ID = [Structure
Builder].[Base Layer Material]) ON [Flexible Converting Equipment_4].ID =
[Structure Builder].[Special Equipent 1]) ON [Flexible Converting
Equipment_5].ID = [Structure Builder].[Special Equipment 2]
ORDER BY [Structure Builder].ID;

What can I do to fix this?