NOT SOLVED - Subform restricts initial record creation

[Hbomb]

Here goes again...

I have a main form for ORDERS and a subform for ORDER
DETAILS (based on a query). These forms work harmoniously
together...i can enter an order and add and delete detail
records at will. New ORDER DETAILS are automatically
populated with the corresponding order number from the
current record in the main ORDERS form. Everything saves
correctly and everything is just peachy.

UNTIL:
I discovered i cannot add a new detail record to an
order that has no existing order details. No new
blank ORDER DETAILS line pops up waiting for me to fill
in the missing info... nothing happens. the subform is
dead and i can't even click the new record button.

I understand that if there are no corresponding records,
the query that my subform is based upon will return
nothing...but isn't there some way i can force the ORDER
DETAILS subform to allow an initial detail record to be
added to a virgin ORDER?

[P.S. this is NOT a problem with saving the ORDER first.
This happens with ANY Order that doesnt already have one
or more corresponding ORDER DETAILS]

 

[tina]

if Order records are linked to OrderDetail records in a one-to-many
relationship - at the table level - then that relationship is easily carried
over into the mainform/subform setup. so my first question is: why do you
need to base the subform on a query?

 

[Hbomb]

Hi Tina, you tried to help me last time i posted this...
here are my reasons for the query:

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); 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.

-----Original Message-----
if Order records are linked to OrderDetail records in a one-to-many
relationship - at the table level - then that relationship is easily carried
over into the mainform/subform setup. so my first question is: why do you
need to base the subform on a query?

Here goes again...

I have a main form for ORDERS and a subform for ORDER
DETAILS (based on a query). These forms work harmoniously
together...i can enter an order and add and delete detail
records at will. New ORDER DETAILS are automatically
populated with the corresponding order number from the
current record in the main ORDERS form. Everything saves
correctly and everything is just peachy.

UNTIL:
I discovered i cannot add a new detail record to an
order that has no existing order details. No new
blank ORDER DETAILS line pops up waiting for me to fill
in the missing info... nothing happens. the subform is
dead and i can't even click the new record button.

I understand that if there are no corresponding records,
the query that my subform is based upon will return
nothing...but isn't there some way i can force the ORDER
DETAILS subform to allow an initial detail record to be
added to a virgin ORDER?

[P.S. this is NOT a problem with saving the ORDER first.
This happens with ANY Order that doesnt already have one
or more corresponding ORDER DETAILS]

.

 

[tina]

comments inline.

Hi Tina, you tried to help me last time i posted this...

ok. i don't recall the previous exchange, so maybe i'll have fresh ideas

here are my reasons for the query:

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);

you can probably do that with a combo box in the form, or just an unbound
textbox with a DLookup expression.

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

again, a combo box or DLookup, with calculation, can do that in the subform.

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.

and again, an unbound control in the subform with a suitable calculation.

i'm assuming you have some sort of criteria in the subform's underlying
query that is restricting the records, and that the criteria is needed
because of the calculations being done in the query. or perhaps there is
simply a problem with the type of join being used in the query.
i may be re-plowing old ground here, but i don't remember...

hth

-----Original Message-----
if Order records are linked to OrderDetail records in a one-to-many
relationship - at the table level - then that relationship is easily carried
over into the mainform/subform setup. so my first question is: why do you
need to base the subform on a query?

Here goes again...

I have a main form for ORDERS and a subform for ORDER
DETAILS (based on a query). These forms work harmoniously
together...i can enter an order and add and delete detail
records at will. New ORDER DETAILS are automatically
populated with the corresponding order number from the
current record in the main ORDERS form. Everything saves
correctly and everything is just peachy.

UNTIL:
I discovered i cannot add a new detail record to an
order that has no existing order details. No new
blank ORDER DETAILS line pops up waiting for me to fill
in the missing info... nothing happens. the subform is
dead and i can't even click the new record button.

I understand that if there are no corresponding records,
the query that my subform is based upon will return
nothing...but isn't there some way i can force the ORDER
DETAILS subform to allow an initial detail record to be
added to a virgin ORDER?

[P.S. this is NOT a problem with saving the ORDER first.
This happens with ANY Order that doesnt already have one
or more corresponding ORDER DETAILS]

.

 

[tina]

ok, i went back and found the original post from 5/24/04. i did a test,
based on your setup, but not including the main table, only tblOrderDetails
and tblProducts. i RIGHT JOINed tblProducts to tblOrderDetails in the query,
as you did originally, and included a Total: column to calculate Quantity *
Price. i wasn't able to add any records to my query when i added a Group By
clause, so i still have no idea how you got that to work. but by removing
the Group By clause, i was able to add records and the Total column updated
automatically, as expected.
i wizard-built a form for the query, as Continuous Forms view. in the form's
footer section, i added an unbound textbox and set the ControlSource to
=Sum(Total)
it summed correctly and updated automatically as each new detail record was
added.
suggest you try this setup, starting with removing the Group By clause in
your query, and see how it works for you.

hth

comments inline.

Hi Tina, you tried to help me last time i posted this...

ok. i don't recall the previous exchange, so maybe i'll have fresh ideas

here are my reasons for the query:

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);

you can probably do that with a combo box in the form, or just an unbound
textbox with a DLookup expression.

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

again, a combo box or DLookup, with calculation, can do that in the subform.

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.

and again, an unbound control in the subform with a suitable calculation.

i'm assuming you have some sort of criteria in the subform's underlying
query that is restricting the records, and that the criteria is needed
because of the calculations being done in the query. or perhaps there is
simply a problem with the type of join being used in the query.
i may be re-plowing old ground here, but i don't remember...

hth

-----Original Message-----
if Order records are linked to OrderDetail records in a one-to-many
relationship - at the table level - then that relationship is easily carried
over into the mainform/subform setup. so my first question is: why do you
need to base the subform on a query?


Here goes again...

I have a main form for ORDERS and a subform for ORDER
DETAILS (based on a query). These forms work harmoniously
together...i can enter an order and add and delete detail
records at will. New ORDER DETAILS are automatically
populated with the corresponding order number from the
current record in the main ORDERS form. Everything saves
correctly and everything is just peachy.

UNTIL:
I discovered i cannot add a new detail record to an
order that has no existing order details. No new
blank ORDER DETAILS line pops up waiting for me to fill
in the missing info... nothing happens. the subform is
dead and i can't even click the new record button.

I understand that if there are no corresponding records,
the query that my subform is based upon will return
nothing...but isn't there some way i can force the ORDER
DETAILS subform to allow an initial detail record to be
added to a virgin ORDER?

[P.S. this is NOT a problem with saving the ORDER first.
This happens with ANY Order that doesnt already have one
or more corresponding ORDER DETAILS]


.

 

[Van T. Dinh]

Post details of your Table Structure (similar to NorthWind?), the SQL String
(of the Query) being used as the RecordSource for the Subform and the Link
Fields.

 

[Guest]

Tina, you got it! Many thanks for your patience with me.

H

-----Original Message-----
ok, i went back and found the original post from 5/24/04. i did a test,
based on your setup, but not including the main table, only tblOrderDetails
and tblProducts. i RIGHT JOINed tblProducts to tblOrderDetails in the query,
as you did originally, and included a Total: column to calculate Quantity *
Price. i wasn't able to add any records to my query when i added a Group By
clause, so i still have no idea how you got that to work. but by removing
the Group By clause, i was able to add records and the Total column updated
automatically, as expected.
i wizard-built a form for the query, as Continuous Forms view. in the form's
footer section, i added an unbound textbox and set the ControlSource to
=Sum(Total)
it summed correctly and updated automatically as each new detail record was
added.
suggest you try this setup, starting with removing the Group By clause in
your query, and see how it works for you.

hth

comments inline.

this...

ok. i don't recall the previous exchange, so maybe i'll have fresh ideas


you can probably do that with a combo box in the form, or just an unbound
textbox with a DLookup expression.


again, a combo box or DLookup, with calculation, can

do that in the
subform.

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.

and again, an unbound control in the subform with a suitable calculation.

i'm assuming you have some sort of criteria in the subform's underlying
query that is restricting the records, and that the criteria is needed
because of the calculations being done in the query. or perhaps there is
simply a problem with the type of join being used in the query.
i may be re-plowing old ground here, but i don't remember...

hth

-----Original Message-----
if Order records are linked to OrderDetail records in a
one-to-many
relationship - at the table level - then that
relationship is easily carried
over into the mainform/subform setup. so my first
question is: why do you
need to base the subform on a query?


message
Here goes again...

I have a main form for ORDERS and a subform for ORDER
DETAILS (based on a query). These forms work
harmoniously
together...i can enter an order and add and delete
detail
records at will. New ORDER DETAILS are automatically
populated with the corresponding order number from the
current record in the main ORDERS form. Everything
saves
correctly and everything is just peachy.

UNTIL:
I discovered i cannot add a new detail record to an
order that has no existing order details. No new
blank ORDER DETAILS line pops up waiting for me to fill
in the missing info... nothing happens. the subform is
dead and i can't even click the new record button.

I understand that if there are no corresponding
records,
the query that my subform is based upon will return
nothing...but isn't there some way i can force the
ORDER
DETAILS subform to allow an initial detail record to be
added to a virgin ORDER?

[P.S. this is NOT a problem with saving the ORDER
first.
This happens with ANY Order that doesnt already have
one
or more corresponding ORDER DETAILS]


.

.

 

[tina]

you're very welcome!

Tina, you got it! Many thanks for your patience with me.

H

-----Original Message-----
ok, i went back and found the original post from 5/24/04. i did a test,
based on your setup, but not including the main table, only tblOrderDetails
and tblProducts. i RIGHT JOINed tblProducts to tblOrderDetails in the query,
as you did originally, and included a Total: column to calculate Quantity *
Price. i wasn't able to add any records to my query when i added a Group By
clause, so i still have no idea how you got that to work. but by removing
the Group By clause, i was able to add records and the Total column updated
automatically, as expected.
i wizard-built a form for the query, as Continuous Forms view. in the form's
footer section, i added an unbound textbox and set the ControlSource to
=Sum(Total)
it summed correctly and updated automatically as each new detail record was
added.
suggest you try this setup, starting with removing the Group By clause in
your query, and see how it works for you.

hth

comments inline.

Hi Tina, you tried to help me last time i posted this...

ok. i don't recall the previous exchange, so maybe i'll have fresh ideas
this time <g>

here are my reasons for the query:

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);

you can probably do that with a combo box in the form, or just an unbound
textbox with a DLookup expression.

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

again, a combo box or DLookup, with calculation, can

do that in the
subform.

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.

and again, an unbound control in the subform with a suitable calculation.

i'm assuming you have some sort of criteria in the subform's underlying
query that is restricting the records, and that the criteria is needed
because of the calculations being done in the query. or perhaps there is
simply a problem with the type of join being used in the query.
i may be re-plowing old ground here, but i don't remember...

hth



-----Original Message-----
if Order records are linked to OrderDetail records in a
one-to-many
relationship - at the table level - then that
relationship is easily carried
over into the mainform/subform setup. so my first
question is: why do you
need to base the subform on a query?


message
Here goes again...

I have a main form for ORDERS and a subform for ORDER
DETAILS (based on a query). These forms work
harmoniously
together...i can enter an order and add and delete
detail
records at will. New ORDER DETAILS are automatically
populated with the corresponding order number from the
current record in the main ORDERS form. Everything
saves
correctly and everything is just peachy.

UNTIL:
I discovered i cannot add a new detail record to an
order that has no existing order details. No new
blank ORDER DETAILS line pops up waiting for me to fill
in the missing info... nothing happens. the subform is
dead and i can't even click the new record button.

I understand that if there are no corresponding
records,
the query that my subform is based upon will return
nothing...but isn't there some way i can force the
ORDER
DETAILS subform to allow an initial detail record to be
added to a virgin ORDER?

[P.S. this is NOT a problem with saving the ORDER
first.
This happens with ANY Order that doesnt already have
one
or more corresponding ORDER DETAILS]


.

.