Delete Problems

  • Thread starter Thread starter Derek Brown
  • Start date Start date
D

Derek Brown

Hi All

When I delete a record from the main form, a query that selects data for the
subform asks for a parameter value that is normally obtained from a toggle
button within the subform. How can I stop this?

This is the SQL of the sub-subform

SELECT Item.InvoiceCode, Item.ItemNumber, Item.ProductID, Item.Product,
Item.Description, Item.Quantities, Item.RetailPrice, Item.WholesalePrice,
Item.Category, Item.Quantity, [Quantity]*[RetailPrice] AS TotalItem,
Item.InvoiceNumber, Item.Paid
FROM Item
WHERE (((Item.Paid)=0 Or
(Item.Paid)=[Forms]![SchoolsForm]![InvoiceForm].[Form]![ItemForm].[Form]![ActivityFrame]));
 
[Forms]![SchoolsForm]![InvoiceForm].[Form]![ItemForm].[Form]![ActivityFrame]
would appear to be referring to a control on a subform on a subform on a
form.

Your main form is SchoolsForm. On that form is a control named InvoiceForm
(note that the name of the control that holds the subform may or may not be
the same name as the form that's being used as a subform). On whatever form
is contained in the control named InvoiceForm is a control named ItemForm.
On whatever form is contained in the control named ItemForm is a control
named ActivityFrame, and you're trying to use the value of ActivityFrame in
your query. Is that what you're expecting, or something else?

If ActivityFrame is a toggle button, its value is going to be 0 or -1. Is
that what you're expecting?
 
Precisely

All works great until I delete a complete record from the main form then the
query asks for parameters
[Forms]![SchoolsForm]![InvoiceForm].[Form]![ItemForm].[Form]![ActivityFrame]
.. Curiously enough it asks for them Twice?

Douglas J. Steele said:
[Forms]![SchoolsForm]![InvoiceForm].[Form]![ItemForm].[Form]![ActivityFrame]
would appear to be referring to a control on a subform on a subform on a
form.

Your main form is SchoolsForm. On that form is a control named InvoiceForm
(note that the name of the control that holds the subform may or may not
be the same name as the form that's being used as a subform). On whatever
form is contained in the control named InvoiceForm is a control named
ItemForm. On whatever form is contained in the control named ItemForm is a
control named ActivityFrame, and you're trying to use the value of
ActivityFrame in your query. Is that what you're expecting, or something
else?

If ActivityFrame is a toggle button, its value is going to be 0 or -1. Is
that what you're expecting?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Derek Brown said:
Hi All

When I delete a record from the main form, a query that selects data for
the
subform asks for a parameter value that is normally obtained from a
toggle
button within the subform. How can I stop this?

This is the SQL of the sub-subform

SELECT Item.InvoiceCode, Item.ItemNumber, Item.ProductID, Item.Product,
Item.Description, Item.Quantities, Item.RetailPrice, Item.WholesalePrice,
Item.Category, Item.Quantity, [Quantity]*[RetailPrice] AS TotalItem,
Item.InvoiceNumber, Item.Paid
FROM Item
WHERE (((Item.Paid)=0 Or
(Item.Paid)=[Forms]![SchoolsForm]![InvoiceForm].[Form]![ItemForm].[Form]![ActivityFrame]));
Click to expand...
Click to expand...
 
Are you certain you haven't got a typo in the name of what you've typed as a
parameter?

Asking for them twice would imply that the parameter appears twice in the
SQL. What's the actual SQL you're using?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Derek Brown said:
Precisely

All works great until I delete a complete record from the main form then
the query asks for parameters
[Forms]![SchoolsForm]![InvoiceForm].[Form]![ItemForm].[Form]![ActivityFrame]
. Curiously enough it asks for them Twice?

Douglas J. Steele said:
[Forms]![SchoolsForm]![InvoiceForm].[Form]![ItemForm].[Form]![ActivityFrame]
would appear to be referring to a control on a subform on a subform on a
form.

Your main form is SchoolsForm. On that form is a control named
InvoiceForm (note that the name of the control that holds the subform may
or may not be the same name as the form that's being used as a subform).
On whatever form is contained in the control named InvoiceForm is a
control named ItemForm. On whatever form is contained in the control
named ItemForm is a control named ActivityFrame, and you're trying to use
the value of ActivityFrame in your query. Is that what you're expecting,
or something else?

If ActivityFrame is a toggle button, its value is going to be 0 or -1. Is
that what you're expecting?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Derek Brown said:
Hi All

When I delete a record from the main form, a query that selects data for
the
subform asks for a parameter value that is normally obtained from a
toggle
button within the subform. How can I stop this?

This is the SQL of the sub-subform

SELECT Item.InvoiceCode, Item.ItemNumber, Item.ProductID, Item.Product,
Item.Description, Item.Quantities, Item.RetailPrice,
Item.WholesalePrice, Item.Category, Item.Quantity,
[Quantity]*[RetailPrice] AS TotalItem, Item.InvoiceNumber, Item.Paid
FROM Item
WHERE (((Item.Paid)=0 Or
(Item.Paid)=[Forms]![SchoolsForm]![InvoiceForm].[Form]![ItemForm].[Form]![ActivityFrame]));
Click to expand...
Click to expand...
Click to expand...
 
Hi Douglas

The SQL is as below. I cannot see how there could be a typo as during the
normal use of the forms clicking the toggle button with a requery in the
code instantly shows or hides certain of the records in the sub-subform.I
have checked all of the code in the entire project and it is not used
elsewhere. It seems that I must be asking for the info twice but for the
life of me i cannot see where? in the Query's Criteria I have:
0 Or
[Forms]![SchoolsForm]![InvoiceForm].[Form]![ItemForm].[Form]![ActivityFrame].

Someone sugested that another way would be To create a new query field with:
Expr1:[Forms]![SchoolsForm]![InvoiceForm].[Form]![ItemForm].[Form]![ActivityFrame].
and put -1 in the second row of the criteria. Something realy wierd happens.
When you run this, in each row of the query results in the field that has
the "Expr1", two small boxes apear where you would expect to find values of
0 or -1. When you save and re-open the form The new field "Expr1" is still
there but access has created a new field with just:
[Forms]![SchoolsForm]![InvoiceForm].[Form]![ItemForm].[Form]![ActivityFrame]
with no field name and the criteria "-1" has moved from the original new
field "Expr1"to the new field creatred by access. I am offering Gold Stars
to anyone that can figure that out.

Just in case you think I have gone insane I have been creating databases for
some 10 years, you would have thought by now that I knew what I was doing,
Well I THOUGHT I did!!!

Douglas J. Steele said:
Are you certain you haven't got a typo in the name of what you've typed as
a parameter?

Asking for them twice would imply that the parameter appears twice in the
SQL. What's the actual SQL you're using?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Derek Brown said:
Precisely

All works great until I delete a complete record from the main form then
the query asks for parameters
[Forms]![SchoolsForm]![InvoiceForm].[Form]![ItemForm].[Form]![ActivityFrame]
. Curiously enough it asks for them Twice?

Douglas J. Steele said:
[Forms]![SchoolsForm]![InvoiceForm].[Form]![ItemForm].[Form]![ActivityFrame]
would appear to be referring to a control on a subform on a subform on a
form.

Your main form is SchoolsForm. On that form is a control named
InvoiceForm (note that the name of the control that holds the subform
may or may not be the same name as the form that's being used as a
subform). On whatever form is contained in the control named InvoiceForm
is a control named ItemForm. On whatever form is contained in the
control named ItemForm is a control named ActivityFrame, and you're
trying to use the value of ActivityFrame in your query. Is that what
you're expecting, or something else?

If ActivityFrame is a toggle button, its value is going to be 0 or -1.
Is that what you're expecting?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Hi All

When I delete a record from the main form, a query that selects data
for the
subform asks for a parameter value that is normally obtained from a
toggle
button within the subform. How can I stop this?

This is the SQL of the sub-subform

SELECT Item.InvoiceCode, Item.ItemNumber, Item.ProductID, Item.Product,
Item.Description, Item.Quantities, Item.RetailPrice,
Item.WholesalePrice, Item.Category, Item.Quantity,
[Quantity]*[RetailPrice] AS TotalItem, Item.InvoiceNumber, Item.Paid
FROM Item
WHERE (((Item.Paid)=0 Or
(Item.Paid)=[Forms]![SchoolsForm]![InvoiceForm].[Form]![ItemForm].[Form]![ActivityFrame]));
Click to expand...
Click to expand...
Click to expand...
 
Back
Top