Trouble creating new records in my subform

  • Thread starter Thread starter HBomb
  • Start date Start date
H

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?
 
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.
 
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.
 
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.





.
Click to expand...
Click to expand...
 
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
Click to expand...
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-
Click to expand...
table
query, i'm thinking
the problem may be in the type of JOIN, but we'd need
Click to expand...
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?


.
Click to expand...
Click to expand...


.
Click to expand...
 
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?


.
Click to expand...


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