Subforms

[Guest]

I have created a subform from a query. The main form is Purchase Orders and
there are different products on each Purchase Order. I have created a query
where I group by Purchase Order No. and sum the total of the different
products for each Purchase Order. I have also included other fields that are
used in entering information about the purchase orders in this query. All of
that works okay, but when I am in the form/subform and I want to add an item
onto a particular purchase order, it won't let me add another record for that
purchase order. I have tried using continuous and datasheet default views.
This is what the datasheet view looks like:

1 2 3 4
5 6
12496 4/30/2005 Index Tabs Legal 4 $3.49 $13.96
12496 4/30/2005 Binder - Accounting 5 $12.99 $64.95
12496 4/30/2005 Extension Cable VGA 1 $11.39 $11.39
12496 4/30/2005 Fellowes Smart Stack 1 $59.99 $59.99

The first column in the purchase order number. The second column is the
date of the purchase order. The third column in the product, fourth column
is the quantity, fifth column is the price per item and the sixth column is
the quantity * price per item.

Why can't I add another product for this purchase order in the subform? I
am (obviously ) not very savy regarding subforms, but I thought the purpose
was that you could add data without having to go back out to a different main
form.

Any help is sincerely appreciated.



S

 

[John Vinson]

I have created a subform from a query. The main form is Purchase Orders and
there are different products on each Purchase Order. I have created a query
where I group by Purchase Order No. and sum the total of the different
products for each Purchase Order. I have also included other fields that are
used in entering information about the purchase orders in this query. All of
that works okay, but when I am in the form/subform and I want to add an item
onto a particular purchase order, it won't let me add another record for that
purchase order. I have tried using continuous and datasheet default views.
This is what the datasheet view looks like:

1 2 3 4
5 6
12496 4/30/2005 Index Tabs Legal 4 $3.49 $13.96
12496 4/30/2005 Binder - Accounting 5 $12.99 $64.95
12496 4/30/2005 Extension Cable VGA 1 $11.39 $11.39
12496 4/30/2005 Fellowes Smart Stack 1 $59.99 $59.99

The first column in the purchase order number. The second column is the
date of the purchase order. The third column in the product, fourth column
is the quantity, fifth column is the price per item and the sixth column is
the quantity * price per item.

Why can't I add another product for this purchase order in the subform? I
am (obviously ) not very savy regarding subforms, but I thought the purpose
was that you could add data without having to go back out to a different main
form.

If you're using a Totals query to sum the value, as the recordsource
for the subform, it won't be updateable - totals queries never are!

What is the Recordsource for the subform? What's the table, or the SQL
of the query? and what are the Master and Child Link Field properties
of the subform?


John W. Vinson[MVP]

 

[Guest]

I am going to try and answer your questions and then restate what I have done
in the mean time (between my question and your reply) and see if you can
follow where I am now.

I have refigured the query that I asked about yesterday because I realized
that the total part of the query would not allow me to add new records to the
subform. In the query, I now have a line total for each line item for each
record instead of a total for the entire purchase order. I have named this
query qryPODetails. I have then pulled this query onto the main form
(frmPurchaseOrder) as a subform and I can add records to the subform and it
works great!

The qryPODetails subform has a product field that is a lookup table in the
products table and I can pull the dropdown and choose an item from the
product table to populate the product field in the qryPODetail subform.
However, when I go to add a new record, even though I don't have a total
field anymore, it won't pull down the dropdown list to add the product. Does
this make any more sense?

 

[John Vinson]

I am going to try and answer your questions and then restate what I have done
in the mean time (between my question and your reply) and see if you can
follow where I am now.

I have refigured the query that I asked about yesterday because I realized
that the total part of the query would not allow me to add new records to the
subform. In the query, I now have a line total for each line item for each
record instead of a total for the entire purchase order. I have named this
query qryPODetails. I have then pulled this query onto the main form
(frmPurchaseOrder) as a subform and I can add records to the subform and it
works great!

The qryPODetails subform has a product field that is a lookup table in the
products table and I can pull the dropdown and choose an item from the
product table to populate the product field in the qryPODetail subform.
However, when I go to add a new record, even though I don't have a total
field anymore, it won't pull down the dropdown list to add the product. Does
this make any more sense?

Well... a bit of clarification might help.

Table lookup fields are misleading, confusing, and not really very
useful. See http://www.mvps.org/access/lookupfields.htm for a
critique. Using lookup *TABLES* is fine - just using lookup fields
*in* a table isn't, since it conceals the actual content of the table
and causes this kind of problem.

So now you have a combo box (a "lookup" if you will) on the subform.
What are that combo's relevant properties - RowSource (post the SQL if
it's a query)? Control Source (the field into which it will put its
data)? Column Count? Column Widths? And what happens when you try to
select the combo?

John W. Vinson[MVP]