Parameter-based update query

[Guest]

I've run into a problem doing a parameter query I received help from this
forum in developing. I've used the structure of this parameter query in
several circumstances, but this time it won't work. I have a subform called
"fHDHP", which has a subform called "fSalaryBand". I have an update query
which identifies the salary bands without an "end date" for each HDHP based
on a parameter, and then updates the end date to the date I enter in response
to the parameter. What is unique this time is that each HDHPID can be
associated with more than one salary band. The query only identifies, and
thus updates, the first instance of the salary band associated with each
HDHP. Here is the SQL:

PARAMETERS [What is end date for current salary band?] DateTime,
[Forms]![fClientFile]![fBenefitDesignSelect]![fBenefitDesignView]![fHDHPView]![fSalaryBand].Form![SalaryBandID] Long;
UPDATE [Salary Band] SET EndDate = [What is end date for current salary band?]
WHERE
SalaryBandID=Forms!fClientFile!fBenefitDesignSelect!fBenefitDesignView!fHDHPView!fSalaryBand.Form!SalaryBandID;

When I remove the WHERE statement, the query identifies the correct number
of salary bands to be updated, so I know that is where something is wrong.
I've tried adding:

AND
HDHPID=Forms!fClientFile!fBenefitDesignSelect!fBenefitDesignView!fHDHPView!.Form!HDHPID

to the WHERE statement, but that doesn't change anything. The 2 forms are
linked on HDHPID. Any suggestions on what I'm doing wrong would be greatly
appreciated. Thanks.

 

[John Spencer]

I believe the problem is that you cannot reference a subform and its control(s)
in a query. The query only is able to interpret down to the first level.

The solution to the problem eludes me. You might be able to use a custom vba
function to grab the appropriate value. OR (WILD guess, you may be able to put
a control on the main form that references the value in the subform's control.
If you succeeded in that, you could then reference the control on the main form
in the query.

 

[John Vinson]

I've run into a problem doing a parameter query I received help from this
forum in developing. I've used the structure of this parameter query in
several circumstances, but this time it won't work. I have a subform called
"fHDHP", which has a subform called "fSalaryBand".

[Forms]![fClientFile]![fBenefitDesignSelect]![fBenefitDesignView]![fHDHPView]![fSalaryBand].Form![SalaryBandID]

This is a suitable syntax for a sub-sub-sub-sub-subform (though it may
need some more Form! reference). If your mainform is named fClientFile
the correct syntax would be

[Forms]![fClientFile]![fHDHPView].Form![fSalaryBand].Form![SalaryBandID]

I don't know where [fBenefitDesignSelect] and [fBenefitDesignView]
came from, but they do not reflect your verbal description of the
form!

John W. Vinson[MVP]

 

[Guest]

Thanks for your suggestion. Sorry- I should have indicated I have a series of
subforms- that is the syntax listed in my original post. I changed the syntax
to your suggestion, but the query worked the same way. It only "sees" the
first occurence of salary band and thus only deletes one record. Without the
WHERE statement, the query recognizes there are multiple records in the
salary band table to be deleted, but when I put the WHERE statement back in,
it only "sees" and deletes the first occurrence of salary band. The HDHP
table is linked through its primary key HDHPID to the salary band table
(primary key SalaryBandID) by having HDHPID as a foreign key in the salary
band table. It is a one-to-many relationship. Any other thoughts?

I've run into a problem doing a parameter query I received help from this
forum in developing. I've used the structure of this parameter query in
several circumstances, but this time it won't work. I have a subform called
"fHDHP", which has a subform called "fSalaryBand".

[Forms]![fClientFile]![fBenefitDesignSelect]![fBenefitDesignView]![fHDHPView]![fSalaryBand].Form![SalaryBandID]

This is a suitable syntax for a sub-sub-sub-sub-subform (though it may
need some more Form! reference). If your mainform is named fClientFile
the correct syntax would be

[Forms]![fClientFile]![fHDHPView].Form![fSalaryBand].Form![SalaryBandID]

I don't know where [fBenefitDesignSelect] and [fBenefitDesignView]
came from, but they do not reflect your verbal description of the
form!

John W. Vinson[MVP]

 

[John Vinson]

I changed the syntax
to your suggestion, but the query worked the same way. It only "sees" the
first occurence of salary band and thus only deletes one record.

Well... yes. Exactly! A form criterion retrieves the currently
"active" record from whichever form you're referencing. Referencing a
single control on a subform does NOT automagically loop through all
the records displayed on that subform.

If you want to delete multiple records, you cannot use this type of
criterion. Instead, join to the relevant table and simply execute a
delete query using that Join to select the records.

John W. Vinson[MVP]