Trouble creating new records in my subform

[HBomb]

I have a main form for ORDERS (based on my orders table)
and a subform for ORDER_DETAILS (based on a query). When
I try to enter new order details in the subform, it works
only for the orders that already have at least one order
detail record. When a given order has no pre-existing
details, the subform is blank (obviously, since it is
based on a query), but I am also unable to create a new
order detail record for that order. Is there a form
command I can use to force the creation of a new blank
record for the subform regardless of wether it has a
related detail record or not? Am I going about this all
wrong?

 

[tina]

check the subform's form properties - is AllowAdditions set to Yes?
if that doesn't take care of it, suggest you post the SQL statement from
the subform's RecordSource query. if it's a multi-table query, i'm thinking
the problem may be in the type of JOIN, but we'd need to see the statement.

 

[Guest]

Yes the Allow Additions property is yes, everything else
is normal. HERE's my SQL script for the Query...

SELECT [Event Detail].[Event ID], [Event Detail].[Product
Code], Products.Prod_Name, [Event Detail].Quantity,
Products.Price, [Event Detail]!Quantity*Products!Price AS
Total
FROM Products RIGHT JOIN [Event Detail] ON
Products.Prod_Code = [Event Detail].[Product Code]
GROUP BY [Event Detail].[Event ID], [Event Detail].
[Product Code], Products.Prod_Name, [Event
Detail].Quantity, Products.Price, [Event Detail]!
Quantity*Products!Price;

I changed to this...but did not change things at all

SELECT [Event Detail].[Event ID], [Event Detail].[Product
Code], Products.Prod_Name, [Event Detail].Quantity,
Products.Price, [Event Detail]!Quantity*Products!Price AS
Total
FROM Products INNER JOIN [Event Detail] ON
Products.Prod_Code = [Event Detail].[Product Code]
GROUP BY [Event Detail].[Event ID], [Event Detail].
[Product Code], Products.Prod_Name, [Event
Detail].Quantity, Products.Price, [Event Detail]!
Quantity*Products!Price;

Any further suggestions? I'm stuck and it's the LAST part
of the database program I need to fix for my project work
proplerly.

 

[tina]

the "GROUP BY" makes it a Totals query, which is not updateable.
if ORDERS and ORDER_DETAILS enjoy a standard one-to-many relationship (one
ORDER can have many DETAILS), and you have defined that relationship in the
Relationships window, then a form for the purpose of adding/updating records
should reflect that relationship. the main form should be bound to ORDERS
and the subform should be bound to ORDER_DETAILS.
why are you using a query that pulls in data from the Products table?
if your purpose is to display the Prod_Name rather than the Prod_Code in the
subform record, then use a combobox for the Prod_Code field in the subform,
based on table Products. *but leave table Products out of the subform's
SourceObject. *

hth

Yes the Allow Additions property is yes, everything else
is normal. HERE's my SQL script for the Query...

SELECT [Event Detail].[Event ID], [Event Detail].[Product
Code], Products.Prod_Name, [Event Detail].Quantity,
Products.Price, [Event Detail]!Quantity*Products!Price AS
Total
FROM Products RIGHT JOIN [Event Detail] ON
Products.Prod_Code = [Event Detail].[Product Code]
GROUP BY [Event Detail].[Event ID], [Event Detail].
[Product Code], Products.Prod_Name, [Event
Detail].Quantity, Products.Price, [Event Detail]!
Quantity*Products!Price;

I changed to this...but did not change things at all

SELECT [Event Detail].[Event ID], [Event Detail].[Product
Code], Products.Prod_Name, [Event Detail].Quantity,
Products.Price, [Event Detail]!Quantity*Products!Price AS
Total
FROM Products INNER JOIN [Event Detail] ON
Products.Prod_Code = [Event Detail].[Product Code]
GROUP BY [Event Detail].[Event ID], [Event Detail].
[Product Code], Products.Prod_Name, [Event
Detail].Quantity, Products.Price, [Event Detail]!
Quantity*Products!Price;

Any further suggestions? I'm stuck and it's the LAST part
of the database program I need to fix for my project work
proplerly.

-----Original Message-----
check the subform's form properties - is AllowAdditions set to Yes?
if that doesn't take care of it, suggest you post the SQL statement from
the subform's RecordSource query. if it's a multi-table query, i'm thinking
the problem may be in the type of JOIN, but we'd need to see the statement.





.

 

[Guest]

Great insight, but I actually use the query for a few
reasons...first, it's used to automatically display the
product name once a simple product code is entered (for
the purpose of quick and accurate data entry); second,
it's used to calculate a Line Total based on the Quantity
of a product (entered in the ORDER_DETAILS table)
multiplied by the product's Price (from the PRODUCTS
table). I then use the sum of the Line Totals for each
order to calcualte an Oder Subtotal in the subform, which
i then reference in the main form. THAT total is then run
through more calculations in the main form (ie. product
discounts, service surcharges, and miscellaneous fees) to
create a Grand Total that is used for and invoice report.
The question is, if the query is NOT updateable, then why
am I able to add ORDER_DETAIL records to the ORDERS that
already have one or more ORDER_DETAIL lines?

H

-----Original Message-----
the "GROUP BY" makes it a Totals query, which is not updateable.
if ORDERS and ORDER_DETAILS enjoy a standard one-to-many relationship (one
ORDER can have many DETAILS), and you have defined that relationship in the
Relationships window, then a form for the purpose of adding/updating records
should reflect that relationship. the main form should be bound to ORDERS
and the subform should be bound to ORDER_DETAILS.
why are you using a query that pulls in data from the Products table?
if your purpose is to display the Prod_Name rather than the Prod_Code in the
subform record, then use a combobox for the Prod_Code field in the subform,
based on table Products. *but leave table Products out of the subform's
SourceObject. *

hth

Yes the Allow Additions property is yes, everything else
is normal. HERE's my SQL script for the Query...

SELECT [Event Detail].[Event ID], [Event Detail]. [Product
Code], Products.Prod_Name, [Event Detail].Quantity,
Products.Price, [Event Detail]!Quantity*Products!Price AS
Total
FROM Products RIGHT JOIN [Event Detail] ON
Products.Prod_Code = [Event Detail].[Product Code]
GROUP BY [Event Detail].[Event ID], [Event Detail].
[Product Code], Products.Prod_Name, [Event
Detail].Quantity, Products.Price, [Event Detail]!
Quantity*Products!Price;

I changed to this...but did not change things at all

SELECT [Event Detail].[Event ID], [Event Detail]. [Product
Code], Products.Prod_Name, [Event Detail].Quantity,
Products.Price, [Event Detail]!Quantity*Products!Price AS
Total
FROM Products INNER JOIN [Event Detail] ON
Products.Prod_Code = [Event Detail].[Product Code]
GROUP BY [Event Detail].[Event ID], [Event Detail].
[Product Code], Products.Prod_Name, [Event
Detail].Quantity, Products.Price, [Event Detail]!
Quantity*Products!Price;

Any further suggestions? I'm stuck and it's the LAST part
of the database program I need to fix for my project work
proplerly.

-----Original Message-----
check the subform's form properties - is

AllowAdditions
set to Yes?

if that doesn't take care of it, suggest you post the SQL statement from
the subform's RecordSource query. if it's a multi-

table
query, i'm thinking

the problem may be in the type of JOIN, but we'd need

to
see the statement.

I have a main form for ORDERS (based on my orders table)
and a subform for ORDER_DETAILS (based on a query). When
I try to enter new order details in the subform, it works
only for the orders that already have at least one order
detail record. When a given order has no pre- existing
details, the subform is blank (obviously, since it is
based on a query), but I am also unable to create a new
order detail record for that order. Is there a form
command I can use to force the creation of a new blank
record for the subform regardless of wether it has a
related detail record or not? Am I going about this all
wrong?


.

.

 

[tina]

that's a good point, and a reasonable question, and my answer is "beats the
heck out of me!" time for someone else who has better ideas to step in, so
i added the NOT SOLVED to the subject line hoping to attract attention to
the thread.

good luck!

Great insight, but I actually use the query for a few
reasons...first, it's used to automatically display the
product name once a simple product code is entered (for
the purpose of quick and accurate data entry); second,
it's used to calculate a Line Total based on the Quantity
of a product (entered in the ORDER_DETAILS table)
multiplied by the product's Price (from the PRODUCTS
table). I then use the sum of the Line Totals for each
order to calcualte an Oder Subtotal in the subform, which
i then reference in the main form. THAT total is then run
through more calculations in the main form (ie. product
discounts, service surcharges, and miscellaneous fees) to
create a Grand Total that is used for and invoice report.
The question is, if the query is NOT updateable, then why
am I able to add ORDER_DETAIL records to the ORDERS that
already have one or more ORDER_DETAIL lines?

H

-----Original Message-----
the "GROUP BY" makes it a Totals query, which is not updateable.
if ORDERS and ORDER_DETAILS enjoy a standard one-to-many relationship (one
ORDER can have many DETAILS), and you have defined that relationship in the
Relationships window, then a form for the purpose of adding/updating records
should reflect that relationship. the main form should be bound to ORDERS
and the subform should be bound to ORDER_DETAILS.
why are you using a query that pulls in data from the Products table?
if your purpose is to display the Prod_Name rather than the Prod_Code in the
subform record, then use a combobox for the Prod_Code field in the subform,
based on table Products. *but leave table Products out of the subform's
SourceObject. *

hth

Yes the Allow Additions property is yes, everything else
is normal. HERE's my SQL script for the Query...

SELECT [Event Detail].[Event ID], [Event Detail]. [Product
Code], Products.Prod_Name, [Event Detail].Quantity,
Products.Price, [Event Detail]!Quantity*Products!Price AS
Total
FROM Products RIGHT JOIN [Event Detail] ON
Products.Prod_Code = [Event Detail].[Product Code]
GROUP BY [Event Detail].[Event ID], [Event Detail].
[Product Code], Products.Prod_Name, [Event
Detail].Quantity, Products.Price, [Event Detail]!
Quantity*Products!Price;

I changed to this...but did not change things at all

SELECT [Event Detail].[Event ID], [Event Detail]. [Product
Code], Products.Prod_Name, [Event Detail].Quantity,
Products.Price, [Event Detail]!Quantity*Products!Price AS
Total
FROM Products INNER JOIN [Event Detail] ON
Products.Prod_Code = [Event Detail].[Product Code]
GROUP BY [Event Detail].[Event ID], [Event Detail].
[Product Code], Products.Prod_Name, [Event
Detail].Quantity, Products.Price, [Event Detail]!
Quantity*Products!Price;

Any further suggestions? I'm stuck and it's the LAST part
of the database program I need to fix for my project work
proplerly.

-----Original Message-----
check the subform's form properties - is AllowAdditions
set to Yes?
if that doesn't take care of it, suggest you post the
SQL statement from
the subform's RecordSource query. if it's a multi- table
query, i'm thinking
the problem may be in the type of JOIN, but we'd need to
see the statement.


I have a main form for ORDERS (based on my orders
table)
and a subform for ORDER_DETAILS (based on a query).
When
I try to enter new order details in the subform, it
works
only for the orders that already have at least one
order
detail record. When a given order has no pre- existing
details, the subform is blank (obviously, since it is
based on a query), but I am also unable to create a new
order detail record for that order. Is there a form
command I can use to force the creation of a new blank
record for the subform regardless of wether it has a
related detail record or not? Am I going about this all
wrong?


.

.