Can't Enter New Record in Subform

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form and a subform. I modeled them after the Orders/ Order Details
relationship in the Northwind database. I can't seem to enter new data into
the subform. I assume this is because the order number is not being passed
to the subform. Both forms are based on queries.

The record source for the subform is

SELECT tblPartNumbers.[Part Number], tblPartNumbers.Size,
tblPartNumbers.Thick, tblPartNumbers.CompanyName, tblPartNumbers.[Rev:],
tblPartNumbers.Alloy, tblPartNumbers.Coating, tblPartNumbers.Form,
tblPartNumbers.[Each Wt], tblPartNumbers.[Tool #], tblOrderDetails.[Order
Number], tblOrderDetails.Tool, tblOrderDetails.Coupons, tblOrderDetails.Qty,
tblOrderDetails.[Customer Lot], tblOrderDetails.ID
FROM tblPartNumbers LEFT JOIN tblOrderDetails ON tblPartNumbers.[Part
Number] = tblOrderDetails.[Part Number];

The records source for the main form is

SELECT [Order Entry5].[Written By], [Order Entry5].[Received By], [Order
Entry5].[Order Number], [Order Entry5].[Master Process], [Order
Entry5].ContactID, [Order Entry5].Received, [Order Entry5].Due, [Order
Entry5].[ID Number], [Order Entry5].[Purchase Order], [Order Entry5].[Line
Item], [Order Entry5].[Job Number], [Order Entry5].[Lot Number], [Order
Entry5].[Serial Numbers], [Order Entry5].[Shipping Method], [Order
Entry5].[Ship to Address], [Order Entry5].[Model Number], [Order
Entry5].[Material Spec:], [Order Entry5].SpecID, [Order Entry5].[Additional
Specifications], [Order Entry5].[ID Process Key], [Order
Entry5].SpecialRacking, [Order Entry5].SpecialPackaging, [Order
Entry5].[Inspection Instructions], [Order Entry5].[Start Temper], [Order
Entry5].[Finish Temper], [Order Entry5].Shipped, [Order Entry5].Certed,
[Order Entry5].[To Lab], [Order Entry5].[In Age], [Order Entry5].[In Str],
[Order Entry5].Bond, [Order Entry5].Promised, [Order Entry5].[By:], [Order
Entry5].[Notes From Gordon], [Order Entry5].Invoiced, [Order Entry5].Notes,
[Order Entry5].[Additional Specifications #2], [Order Entry5].Specification,
[Order Entry5].[Backlog Note], [Order Entry5].[Tensile Test], [Order
Entry5].FurnaceCharts, [Order Entry5].[Re-Stencil], [Order Entry5].[Remove
Stencil]
FROM [Order Entry5];

The link between them is based on the field [Order Entry]. It seems to work
fine for existing records, but when I try to add a new record, I get the
message that the field cannot be updated. Any clues?

-
Why are you asking me? I dont know what Im doing!

Jaybird
 
This problem may occur because of several reasons:

1) It could be because the foreign key value is not set in your subform.
There has to be a field in the subform that has to be defaulted to the Order
Number. Set the default value property of the foreign key field to the number
of the Order.

2) The query may not be structured in a way that allows additions. i.e.
complicated subqueries, etc.

Jaybird said:
I have a form and a subform. I modeled them after the Orders/ Order Details
relationship in the Northwind database. I can't seem to enter new data into
the subform. I assume this is because the order number is not being passed
to the subform. Both forms are based on queries.

The record source for the subform is

SELECT tblPartNumbers.[Part Number], tblPartNumbers.Size,
tblPartNumbers.Thick, tblPartNumbers.CompanyName, tblPartNumbers.[Rev:],
tblPartNumbers.Alloy, tblPartNumbers.Coating, tblPartNumbers.Form,
tblPartNumbers.[Each Wt], tblPartNumbers.[Tool #], tblOrderDetails.[Order
Number], tblOrderDetails.Tool, tblOrderDetails.Coupons, tblOrderDetails.Qty,
tblOrderDetails.[Customer Lot], tblOrderDetails.ID
FROM tblPartNumbers LEFT JOIN tblOrderDetails ON tblPartNumbers.[Part
Number] = tblOrderDetails.[Part Number];

The records source for the main form is

SELECT [Order Entry5].[Written By], [Order Entry5].[Received By], [Order
Entry5].[Order Number], [Order Entry5].[Master Process], [Order
Entry5].ContactID, [Order Entry5].Received, [Order Entry5].Due, [Order
Entry5].[ID Number], [Order Entry5].[Purchase Order], [Order Entry5].[Line
Item], [Order Entry5].[Job Number], [Order Entry5].[Lot Number], [Order
Entry5].[Serial Numbers], [Order Entry5].[Shipping Method], [Order
Entry5].[Ship to Address], [Order Entry5].[Model Number], [Order
Entry5].[Material Spec:], [Order Entry5].SpecID, [Order Entry5].[Additional
Specifications], [Order Entry5].[ID Process Key], [Order
Entry5].SpecialRacking, [Order Entry5].SpecialPackaging, [Order
Entry5].[Inspection Instructions], [Order Entry5].[Start Temper], [Order
Entry5].[Finish Temper], [Order Entry5].Shipped, [Order Entry5].Certed,
[Order Entry5].[To Lab], [Order Entry5].[In Age], [Order Entry5].[In Str],
[Order Entry5].Bond, [Order Entry5].Promised, [Order Entry5].[By:], [Order
Entry5].[Notes From Gordon], [Order Entry5].Invoiced, [Order Entry5].Notes,
[Order Entry5].[Additional Specifications #2], [Order Entry5].Specification,
[Order Entry5].[Backlog Note], [Order Entry5].[Tensile Test], [Order
Entry5].FurnaceCharts, [Order Entry5].[Re-Stencil], [Order Entry5].[Remove
Stencil]
FROM [Order Entry5];

The link between them is based on the field [Order Entry]. It seems to work
fine for existing records, but when I try to add a new record, I get the
message that the field cannot be updated. Any clues?

-
Why are you asking me? I dont know what Im doing!

Jaybird
Click to expand...
 
Ray,

Thanks for the response. I'm sorry if my post wasn't detailed enough. I
was pretty tired at the end of yesterday, so I forgot to mention the
structure of the tables behind the subform. One of the tables does indeed
have Order Number as a foreign key. Just to clarify... the Order Entry5
table has Order Number as its primary key. This is the table behind the main
form. The subform has a query as its record source, including two tables:
tblOrderDetails and tblPartNumbers. They are related by the field Part
Number. tblPartNumbers has a multi field index as its primary key, including
Order Number and Part Number. The query results are updatable, so I wonder
if the problem is that not all the tables included in the subform are moving
to a new record when I attempt to add a new record in the mainform. I didn't
use a wizard to create the subform, so it's entirely possible that I forgot
something in the process. I THINK it's that something that I need help
with... but then again, maybe I've overlooked something else with regards to
the table relationships.
--
Why are you asking me? I dont know what Im doing!

Jaybird


Ray C said:
This problem may occur because of several reasons:

1) It could be because the foreign key value is not set in your subform.
There has to be a field in the subform that has to be defaulted to the Order
Number. Set the default value property of the foreign key field to the number
of the Order.

2) The query may not be structured in a way that allows additions. i.e.
complicated subqueries, etc.

Jaybird said:
I have a form and a subform. I modeled them after the Orders/ Order Details
relationship in the Northwind database. I can't seem to enter new data into
the subform. I assume this is because the order number is not being passed
to the subform. Both forms are based on queries.

The record source for the subform is

SELECT tblPartNumbers.[Part Number], tblPartNumbers.Size,
tblPartNumbers.Thick, tblPartNumbers.CompanyName, tblPartNumbers.[Rev:],
tblPartNumbers.Alloy, tblPartNumbers.Coating, tblPartNumbers.Form,
tblPartNumbers.[Each Wt], tblPartNumbers.[Tool #], tblOrderDetails.[Order
Number], tblOrderDetails.Tool, tblOrderDetails.Coupons, tblOrderDetails.Qty,
tblOrderDetails.[Customer Lot], tblOrderDetails.ID
FROM tblPartNumbers LEFT JOIN tblOrderDetails ON tblPartNumbers.[Part
Number] = tblOrderDetails.[Part Number];

The records source for the main form is

SELECT [Order Entry5].[Written By], [Order Entry5].[Received By], [Order
Entry5].[Order Number], [Order Entry5].[Master Process], [Order
Entry5].ContactID, [Order Entry5].Received, [Order Entry5].Due, [Order
Entry5].[ID Number], [Order Entry5].[Purchase Order], [Order Entry5].[Line
Item], [Order Entry5].[Job Number], [Order Entry5].[Lot Number], [Order
Entry5].[Serial Numbers], [Order Entry5].[Shipping Method], [Order
Entry5].[Ship to Address], [Order Entry5].[Model Number], [Order
Entry5].[Material Spec:], [Order Entry5].SpecID, [Order Entry5].[Additional
Specifications], [Order Entry5].[ID Process Key], [Order
Entry5].SpecialRacking, [Order Entry5].SpecialPackaging, [Order
Entry5].[Inspection Instructions], [Order Entry5].[Start Temper], [Order
Entry5].[Finish Temper], [Order Entry5].Shipped, [Order Entry5].Certed,
[Order Entry5].[To Lab], [Order Entry5].[In Age], [Order Entry5].[In Str],
[Order Entry5].Bond, [Order Entry5].Promised, [Order Entry5].[By:], [Order
Entry5].[Notes From Gordon], [Order Entry5].Invoiced, [Order Entry5].Notes,
[Order Entry5].[Additional Specifications #2], [Order Entry5].Specification,
[Order Entry5].[Backlog Note], [Order Entry5].[Tensile Test], [Order
Entry5].FurnaceCharts, [Order Entry5].[Re-Stencil], [Order Entry5].[Remove
Stencil]
FROM [Order Entry5];

The link between them is based on the field [Order Entry]. It seems to work
fine for existing records, but when I try to add a new record, I get the
message that the field cannot be updated. Any clues?

-
Why are you asking me? I dont know what Im doing!

Jaybird
Click to expand...
Click to expand...
 
I think I know what's going on... I set my subform up so that it's trying to
write to the tblPartNumbers table. Not what I want. I want to write to the
tblOrderDetails table and simply refer to the part numbers from the
tblPartNumbers table for data entry. The subform should be set up with a
combobox that refers to the tblPartNumbers fills in the rest of the fields
with the information from that table for reference only, but records the part
number , qty, and other information into the tblOrderDetails table. My set
up is dumb. I guess I didn't think about it enough.
--
Why are you asking me? I dont know what Im doing!

Jaybird


Jaybird said:
Ray,

Thanks for the response. I'm sorry if my post wasn't detailed enough. I
was pretty tired at the end of yesterday, so I forgot to mention the
structure of the tables behind the subform. One of the tables does indeed
have Order Number as a foreign key. Just to clarify... the Order Entry5
table has Order Number as its primary key. This is the table behind the main
form. The subform has a query as its record source, including two tables:
tblOrderDetails and tblPartNumbers. They are related by the field Part
Number. tblPartNumbers has a multi field index as its primary key, including
Order Number and Part Number. The query results are updatable, so I wonder
if the problem is that not all the tables included in the subform are moving
to a new record when I attempt to add a new record in the mainform. I didn't
use a wizard to create the subform, so it's entirely possible that I forgot
something in the process. I THINK it's that something that I need help
with... but then again, maybe I've overlooked something else with regards to
the table relationships.
--
Why are you asking me? I dont know what Im doing!

Jaybird


Ray C said:
This problem may occur because of several reasons:

1) It could be because the foreign key value is not set in your subform.
There has to be a field in the subform that has to be defaulted to the Order
Number. Set the default value property of the foreign key field to the number
of the Order.

2) The query may not be structured in a way that allows additions. i.e.
complicated subqueries, etc.

Jaybird said:
I have a form and a subform. I modeled them after the Orders/ Order Details
relationship in the Northwind database. I can't seem to enter new data into
the subform. I assume this is because the order number is not being passed
to the subform. Both forms are based on queries.

The record source for the subform is

SELECT tblPartNumbers.[Part Number], tblPartNumbers.Size,
tblPartNumbers.Thick, tblPartNumbers.CompanyName, tblPartNumbers.[Rev:],
tblPartNumbers.Alloy, tblPartNumbers.Coating, tblPartNumbers.Form,
tblPartNumbers.[Each Wt], tblPartNumbers.[Tool #], tblOrderDetails.[Order
Number], tblOrderDetails.Tool, tblOrderDetails.Coupons, tblOrderDetails.Qty,
tblOrderDetails.[Customer Lot], tblOrderDetails.ID
FROM tblPartNumbers LEFT JOIN tblOrderDetails ON tblPartNumbers.[Part
Number] = tblOrderDetails.[Part Number];

The records source for the main form is

SELECT [Order Entry5].[Written By], [Order Entry5].[Received By], [Order
Entry5].[Order Number], [Order Entry5].[Master Process], [Order
Entry5].ContactID, [Order Entry5].Received, [Order Entry5].Due, [Order
Entry5].[ID Number], [Order Entry5].[Purchase Order], [Order Entry5].[Line
Item], [Order Entry5].[Job Number], [Order Entry5].[Lot Number], [Order
Entry5].[Serial Numbers], [Order Entry5].[Shipping Method], [Order
Entry5].[Ship to Address], [Order Entry5].[Model Number], [Order
Entry5].[Material Spec:], [Order Entry5].SpecID, [Order Entry5].[Additional
Specifications], [Order Entry5].[ID Process Key], [Order
Entry5].SpecialRacking, [Order Entry5].SpecialPackaging, [Order
Entry5].[Inspection Instructions], [Order Entry5].[Start Temper], [Order
Entry5].[Finish Temper], [Order Entry5].Shipped, [Order Entry5].Certed,
[Order Entry5].[To Lab], [Order Entry5].[In Age], [Order Entry5].[In Str],
[Order Entry5].Bond, [Order Entry5].Promised, [Order Entry5].[By:], [Order
Entry5].[Notes From Gordon], [Order Entry5].Invoiced, [Order Entry5].Notes,
[Order Entry5].[Additional Specifications #2], [Order Entry5].Specification,
[Order Entry5].[Backlog Note], [Order Entry5].[Tensile Test], [Order
Entry5].FurnaceCharts, [Order Entry5].[Re-Stencil], [Order Entry5].[Remove
Stencil]
FROM [Order Entry5];

The link between them is based on the field [Order Entry]. It seems to work
fine for existing records, but when I try to add a new record, I get the
message that the field cannot be updated. Any clues?

-
Why are you asking me? I dont know what Im doing!

Jaybird
Click to expand...
Click to expand...
Click to expand...
 
You are on the right track...

Your subform should only be referencing tblOrderDetails . Make sure your
subform has its Parent field and child field set to the foreign key field
(i.e. OrderID). Your tblPartNumbers table should only be used as the query
that fills your products combo box. Then make sure that your bound field in
the combo box points to the ProductID field in your tblOrderDetails table
(within your subform).

Hope this helps

Ray


Jaybird said:
I think I know what's going on... I set my subform up so that it's trying to
write to the tblPartNumbers table. Not what I want. I want to write to the
tblOrderDetails table and simply refer to the part numbers from the
tblPartNumbers table for data entry. The subform should be set up with a
combobox that refers to the tblPartNumbers fills in the rest of the fields
with the information from that table for reference only, but records the part
number , qty, and other information into the tblOrderDetails table. My set
up is dumb. I guess I didn't think about it enough.
--
Why are you asking me? I dont know what Im doing!

Jaybird


Jaybird said:
Ray,

Thanks for the response. I'm sorry if my post wasn't detailed enough. I
was pretty tired at the end of yesterday, so I forgot to mention the
structure of the tables behind the subform. One of the tables does indeed
have Order Number as a foreign key. Just to clarify... the Order Entry5
table has Order Number as its primary key. This is the table behind the main
form. The subform has a query as its record source, including two tables:
tblOrderDetails and tblPartNumbers. They are related by the field Part
Number. tblPartNumbers has a multi field index as its primary key, including
Order Number and Part Number. The query results are updatable, so I wonder
if the problem is that not all the tables included in the subform are moving
to a new record when I attempt to add a new record in the mainform. I didn't
use a wizard to create the subform, so it's entirely possible that I forgot
something in the process. I THINK it's that something that I need help
with... but then again, maybe I've overlooked something else with regards to
the table relationships.
--
Why are you asking me? I dont know what Im doing!

Jaybird


Ray C said:
This problem may occur because of several reasons:

1) It could be because the foreign key value is not set in your subform.
There has to be a field in the subform that has to be defaulted to the Order
Number. Set the default value property of the foreign key field to the number
of the Order.

2) The query may not be structured in a way that allows additions. i.e.
complicated subqueries, etc.

:

I have a form and a subform. I modeled them after the Orders/ Order Details
relationship in the Northwind database. I can't seem to enter new data into
the subform. I assume this is because the order number is not being passed
to the subform. Both forms are based on queries.

The record source for the subform is

SELECT tblPartNumbers.[Part Number], tblPartNumbers.Size,
tblPartNumbers.Thick, tblPartNumbers.CompanyName, tblPartNumbers.[Rev:],
tblPartNumbers.Alloy, tblPartNumbers.Coating, tblPartNumbers.Form,
tblPartNumbers.[Each Wt], tblPartNumbers.[Tool #], tblOrderDetails.[Order
Number], tblOrderDetails.Tool, tblOrderDetails.Coupons, tblOrderDetails.Qty,
tblOrderDetails.[Customer Lot], tblOrderDetails.ID
FROM tblPartNumbers LEFT JOIN tblOrderDetails ON tblPartNumbers.[Part
Number] = tblOrderDetails.[Part Number];

The records source for the main form is

SELECT [Order Entry5].[Written By], [Order Entry5].[Received By], [Order
Entry5].[Order Number], [Order Entry5].[Master Process], [Order
Entry5].ContactID, [Order Entry5].Received, [Order Entry5].Due, [Order
Entry5].[ID Number], [Order Entry5].[Purchase Order], [Order Entry5].[Line
Item], [Order Entry5].[Job Number], [Order Entry5].[Lot Number], [Order
Entry5].[Serial Numbers], [Order Entry5].[Shipping Method], [Order
Entry5].[Ship to Address], [Order Entry5].[Model Number], [Order
Entry5].[Material Spec:], [Order Entry5].SpecID, [Order Entry5].[Additional
Specifications], [Order Entry5].[ID Process Key], [Order
Entry5].SpecialRacking, [Order Entry5].SpecialPackaging, [Order
Entry5].[Inspection Instructions], [Order Entry5].[Start Temper], [Order
Entry5].[Finish Temper], [Order Entry5].Shipped, [Order Entry5].Certed,
[Order Entry5].[To Lab], [Order Entry5].[In Age], [Order Entry5].[In Str],
[Order Entry5].Bond, [Order Entry5].Promised, [Order Entry5].[By:], [Order
Entry5].[Notes From Gordon], [Order Entry5].Invoiced, [Order Entry5].Notes,
[Order Entry5].[Additional Specifications #2], [Order Entry5].Specification,
[Order Entry5].[Backlog Note], [Order Entry5].[Tensile Test], [Order
Entry5].FurnaceCharts, [Order Entry5].[Re-Stencil], [Order Entry5].[Remove
Stencil]
FROM [Order Entry5];

The link between them is based on the field [Order Entry]. It seems to work
fine for existing records, but when I try to add a new record, I get the
message that the field cannot be updated. Any clues?

-
Why are you asking me? I dont know what Im doing!

Jaybird
Click to expand...
Click to expand...
Click to expand...
 
Back
Top