can't add new record in main form

  • Thread starter Thread starter Rayo K.
  • Start date Start date
R

Rayo K.

I have a sales form with a sales line item subform,
linked by a "SalesID" field. The subform query is based
on a table with SalesID, ItemID, and number. Now, I can't
create a new sales record unless I manually open the
Subform underlying table and enter a record containing
the new SalesID number.

When I open the sales main form, the new record button is
inactive. Is there a property I need to change to fix
this?

Thanks
 
In which (main form or subform) is the new record button greyed out?

Is the form's AllowAdditions property (open form in Design view and check
the Data properties for the form) set to Yes?
 
Rayo,

Sounds like something incorrectly defined in the design of your tables
or queries. For example, is SalesID in th SalesLines table set as
primary key? Please post back with details of table fields, and the
query that the subform is based on.

- Steve Schapel, Microsoft Access MVP
 
I did check the AllowAdditions property on the SalesFrm
Form. It is set to yes.

My main form is called SalesFrm
My subform is called SalesDetailSubFrm

There are three tables:

Sales (primary key SalesID)
Items (primary key ItemID)
SalesDetail (junction table: two primary keys: SalesID
and ItemID)

The primary keys of my Sales and Items tables are linked
to the primary keys of the junction table

My SalesFrm has details of each sale, and a control for
the subform. The subform is shown in datasheet mode, and
lists all items for that particular sale.

Both forms use queries as thier datasources.

The subform query lists all fields of the junction table
(using the *) and name and price of the items.

The SalesFrm query has all fields of the sales table and
some related tables, not using the * because there is a
calculated total field for the value of the sale.

When I view a record on the main form, I can add items to
each sales, so the subform is accepting new records. I
cannot add a record to the Sales table. The new record
button on the main form is grayed, and using the control
buttons only cycles thourgh existing cales records.

I hope this clarifies it. I'll be trying to change
things one at a time to see if I can find the sticking
point.

thanks
 
OK, even more. I hope this is helpful:

I added a new Sales record to my sales table, but did not
change the junction table. The new record does not show
up in the form or the query unless I create records in
the junction table that reference the SalesID for that
record.

Is it the query?
Or the relationship?
 
Sounds as if the query on the main form is not updatable. Post the SQL of
the main form's recordsource query.
 
OK. Here it is. How do you determine whether it's
updatable or not?


SELECT SalesTbl.SaleID, SalesTbl.Salesperson,
SalesTbl.Customer, SalesTbl.SaleDate, Sum([ItemPrice]*
[NumberThisSale]) AS SaleTotal,
SalespeopleTbl.SalespersonCodeName,
CustomersTbl.CustomerName
FROM (SalespeopleTbl RIGHT JOIN (CustomersTbl RIGHT JOIN
SalesTbl ON CustomersTbl.CustomerID = SalesTbl.Customer)
ON SalespeopleTbl.SalespersonID = SalesTbl.Salesperson)
RIGHT JOIN (ItemsTbl RIGHT JOIN SalesDetailsTbl ON
ItemsTbl.ItemID = SalesDetailsTbl.ItemID) ON
SalesTbl.SaleID = SalesDetailsTbl.SaleID
GROUP BY SalesTbl.SaleID, SalesTbl.Salesperson,
SalesTbl.Customer, SalesTbl.SaleDate,
SalespeopleTbl.SalespersonCodeName,
CustomersTbl.CustomerName;
 
Also, the SalesQry will display the new records if the
SalesDetail table is removed from the design view of the
query...
-----Original Message-----
OK. Here it is. How do you determine whether it's
updatable or not?


SELECT SalesTbl.SaleID, SalesTbl.Salesperson,
SalesTbl.Customer, SalesTbl.SaleDate, Sum([ItemPrice]*
[NumberThisSale]) AS SaleTotal,
SalespeopleTbl.SalespersonCodeName,
CustomersTbl.CustomerName
FROM (SalespeopleTbl RIGHT JOIN (CustomersTbl RIGHT JOIN
SalesTbl ON CustomersTbl.CustomerID = SalesTbl.Customer)
ON SalespeopleTbl.SalespersonID = SalesTbl.Salesperson)
RIGHT JOIN (ItemsTbl RIGHT JOIN SalesDetailsTbl ON
ItemsTbl.ItemID = SalesDetailsTbl.ItemID) ON
SalesTbl.SaleID = SalesDetailsTbl.SaleID
GROUP BY SalesTbl.SaleID, SalesTbl.Salesperson,
SalesTbl.Customer, SalesTbl.SaleDate,
SalespeopleTbl.SalespersonCodeName,
CustomersTbl.CustomerName;




-----Original Message-----
Sounds as if the query on the main form is not updatable. Post the SQL of
the main form's recordsource query.
Click to expand...
items
to


.
Click to expand...
.
Click to expand...
 
Rako,

OK. Here it is. How do you determine whether it's
updatable or not?
Click to expand...

One giveaway that it's not updateable is the presence of "GROUP BY".
Yours has, so it's not.

You might need to just use a simple select query for your form's
recordsource, and get your totals in a calculated control in the form
Footer.

- Steve Schapel, Microsoft Access MVP

SELECT SalesTbl.SaleID, SalesTbl.Salesperson,
SalesTbl.Customer, SalesTbl.SaleDate, Sum([ItemPrice]*
[NumberThisSale]) AS SaleTotal,
SalespeopleTbl.SalespersonCodeName,
CustomersTbl.CustomerName
FROM (SalespeopleTbl RIGHT JOIN (CustomersTbl RIGHT JOIN
SalesTbl ON CustomersTbl.CustomerID = SalesTbl.Customer)
ON SalespeopleTbl.SalespersonID = SalesTbl.Salesperson)
RIGHT JOIN (ItemsTbl RIGHT JOIN SalesDetailsTbl ON
ItemsTbl.ItemID = SalesDetailsTbl.ItemID) ON
SalesTbl.SaleID = SalesDetailsTbl.SaleID
GROUP BY SalesTbl.SaleID, SalesTbl.Salesperson,
SalesTbl.Customer, SalesTbl.SaleDate,
SalespeopleTbl.SalespersonCodeName,
CustomersTbl.CustomerName;
Click to expand...
 
Okay. When I remove the groupby by deselecting totals, it
displays a separate record for each subform record. So
with 4 Sales records, it has 9 records, one for each item
sold. I want to group the main records by SalesID but be
able to add multiple items per sale. Is there a way to do
this? Also, the query will not display a sales record
that has no related Saledetails records.

-----Original Message-----
Rako,

OK. Here it is. How do you determine whether it's
updatable or not?
Click to expand...

One giveaway that it's not updateable is the presence of "GROUP BY".
Yours has, so it's not.

You might need to just use a simple select query for your form's
recordsource, and get your totals in a calculated control in the form
Footer.

- Steve Schapel, Microsoft Access MVP

SELECT SalesTbl.SaleID, SalesTbl.Salesperson,
SalesTbl.Customer, SalesTbl.SaleDate, Sum([ItemPrice]*
[NumberThisSale]) AS SaleTotal,
SalespeopleTbl.SalespersonCodeName,
CustomersTbl.CustomerName
FROM (SalespeopleTbl RIGHT JOIN (CustomersTbl RIGHT JOIN
SalesTbl ON CustomersTbl.CustomerID = SalesTbl.Customer)
ON SalespeopleTbl.SalespersonID = SalesTbl.Salesperson)
RIGHT JOIN (ItemsTbl RIGHT JOIN SalesDetailsTbl ON
ItemsTbl.ItemID = SalesDetailsTbl.ItemID) ON
SalesTbl.SaleID = SalesDetailsTbl.SaleID
GROUP BY SalesTbl.SaleID, SalesTbl.Salesperson,
SalesTbl.Customer, SalesTbl.SaleDate,
SalespeopleTbl.SalespersonCodeName,
CustomersTbl.CustomerName;
Click to expand...

.
Click to expand...
Click to expand...
 
Rako,

If I understand the situation correctly, you have the Sale data on the
main form and the Item Details data on the subform. And the query you
have showed us the query for the main form. Am I right? If so, I
can't see any need to include the SalesDetailsTbl table or the
ItemsTbl table in this query.

- Steve Schapel, Microsoft Access MVP
 
That was it! It works. Thank you!


-----Original Message-----
Rako,

If I understand the situation correctly, you have the Sale data on the
main form and the Item Details data on the subform. And the query you
have showed us the query for the main form. Am I right? If so, I
can't see any need to include the SalesDetailsTbl table or the
ItemsTbl table in this query.

- Steve Schapel, Microsoft Access MVP
Click to expand...
 
OK, I got the main form to display the sales the way I
want. Now I'm having trouble with calculating the total.

I put a control for the total on the Main Form. It has
this expression as its control source:

=Sum(SalesDetailSubFrm.Form!
NumberThisSale.Value*SalesDetailSubFrm.Form!
ItemPrice.Value)

When I display the form, it shows #Error.

Any ideas?

-----Original Message-----
Rako,

If I understand the situation correctly, you have the Sale data on the
main form and the Item Details data on the subform. And the query you
have showed us the query for the main form. Am I right? If so, I
can't see any need to include the SalesDetailsTbl table or the
ItemsTbl table in this query.

- Steve Schapel, Microsoft Access MVP
Click to expand...
 
Rako,

You will have to do the calculation in the footer of the subform (make
it hidden if you like), with an unbound textbox, let's call in
TotalPrice, with controlsource...
=Sum([NumberThisSale]*[ItemPrice])
.... and then the controlsource of the textbox on the main form will
be...
=[SalesDetailSubFrm]![TotalPrice]

- Steve Schapel, Microsoft Access MVP
 
Thank you Steve. The form works great.
-----Original Message-----
Rako,

You will have to do the calculation in the footer of the subform (make
it hidden if you like), with an unbound textbox, let's call in
TotalPrice, with controlsource...
=Sum([NumberThisSale]*[ItemPrice])
.... and then the controlsource of the textbox on the main form will
be...
=[SalesDetailSubFrm]![TotalPrice]

- Steve Schapel, Microsoft Access MVP
Click to expand...
 
Back
Top