I have a subform within a subform.

The main subform is linked to the second subform by CostReducProjtD
The second subform has multiple records with the CostReducProjtID repeating for each record.

What I want to be able to do is to be able to delete all the records in the second subform that match the
CostReducProjID in the Main subform.

When I run the query below it asks for a value to be entered yet both forms have the CostReducProjtID showing.

Is the query correct the way I have it or is there a different way to do this?

DELETE CostReducProjDetailsTbl.*, CostReducProjDetailsTbl.CostReducProjtID
FROM CostReducProjDetailsTbl
WHERE (((CostReducProjDetailsTbl.CostReducProjtID)=[Forms]![CostReductProjSubFrm]![Cos
tReducProjtID]));

When referring to a control on a subform, you have to refer to the mainform first, like:

Forms!Mainform!Subform1.Form!ControlName

Here is a great link showing various ways to refer to controls if they reside on subforms.

DELETE CostReducProjDetailsTbl.*
FROM CostReducProjDetailsTbl
WHERE (((CostReducProjDetailsTbl.CostReducProjtID)=[Forms]![CostReductProjFrm]![CostReducProjtID]));

(considering [CostReductProjFrm] as the main form's name)
To delete subform related records, all you need is informing the main form's id.

I do not quite understand how this works. Do you put those referances in the query?

The Main form has ProjID (autonumber)
SubForm1 is linked back to the mainform by ProjID and has CostReducProjID (autonumber)
SubForm2 is linked back to Subform1 by CostReducProjID and has MonthCostID (autonumber)

The mainform is called CostReductionDataEntryFrm
Subform1 is called CostReducProjSubFrm
Subform2 is called CostReducDetailsSubFrm

I tried
DELETE CostReducProjDetailsTbl.*, CostReducProjDetailsTbl.CostReducProjtID
FROM CostReducProjDetailsTbl
WHERE (((CostReducProjDetailsTbl.CostReducProjtID)=[Forms]![CostReducProjDataEntryForm
]![CostReductProjSubFrm]![CostReducProjtID]));

And it still saying enter paramter value [Forms]![CostReducProjDataEntryForm]![CostReductProjSubFrm]![CostReducProjtID]

Edited by: TitinaLorey on Wed Nov 2 10:20:19 EST 2005.

Try

DELETE *
FROM CostReducProjDetailsTbl
WHERE CostReducProjtID=[Forms]![CostReducProjDataEntryForm]![CostReductProjSubFrm].[Fo
rm]![CostReducProjtID];

Did and still getting same message.

This works as long as I do not put the subforms on the main form:

DELETE CostReducProjDetailsTbl.*, CostReducProjDetailsTbl.CostReducProjtID
FROM CostReducProjDetailsTbl
WHERE (((CostReducProjDetailsTbl.CostReducProjtID)=[Forms]![CostReducProjSubFrm].[Cost
ReducProjtIDtxt]));

Attaching Database with sensative material removed for you to look at and see what is going on.

Appreciate all the help




Edited by: TitinaLorey on Wed Nov 2 11:37:49 EST 2005.

I'm afraid I can't open the DB because it is in 2003 format. spy may be able to help or you could convert it to 2000 and repost.

Ok well with the query I show above the delete query works if I open subform1 with subform2 in it.

But when I add the Subform1 to the main form the message appears.

I did alter the query as you have it however the same message shows up with it wanting a parameter entered
[Forms]![CostReducProjDataEntryForm]![CostReductProjSubFrm].[Form]![CostReducPro
jtID];

Maybe this example will help you out.

MainForm Form17
SubForm Form18
Query21 Refers to field Text0 on Subform

Got it working

DELETE CostReducProjDetailsTbl.*, CostReducProjDetailsTbl.CostReducProjtID
FROM CostReducProjDetailsTbl
WHERE (((CostReducProjDetailsTbl.CostReducProjtID)=[Forms]![CostReducProjDataEntryFrm]
![CostReducProjSubFrm].[Form]![CostReducProjtIDtxt]));

Great! Glad to help.