Selecting correct records for parameter update query

[Tracey]

I want to use a macro to trigger an update query from a subform called
fBenefitDesignView. The update query has a parameter which requires the user
to enter a date. The date is then updated to the underlying table Benefit
Design. However, I need the query to be filtered for the current
BenefitDesignID on the subform fBenefitDesignView so that the date will only
be updated for that record. Currently my update query updates all the records
with the date from the parameter. I've tried to put in a criteria on the
update query for BenefitDesignID as
[Forms]![fBenefitDesignView]![BenefitDesignID], but the query treats that as
another parameter to prompt me for.

Here is my current SQL:

UPDATE [Benefit Design] SET [Benefit Design].BenefitDesignEndDate = [What is
current Benefit Design's end date?]
WHERE ((([What is current Benefit Design's end date?])=[What is current
Benefit Design's end date?]));

How do I filter the query by the BenefitDesignID from the current subform
fBenefitDesignView?

Thanks for any help!

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The SQL should be like this:

PARAMETERS [What is current Benefit Design's end date?] Date,
[Forms]![fBenefitDesignView]![BenefitDesignID] Long;

UPDATE [Benefit Design]

SET BenefitDesignEndDate = [What is current Benefit Design's end date?]

WHERE BenefitDesignID = [Forms]![fBenefitDesignView]![BenefitDesignID]


I'm assuming the BenefitDesignID is a Long (numeric) data type. If it
is a string then change "Long" to TEXT(255), or whatever the correct
data type is.

If the name of the column in the table Benefit Design is other than
BenefitDesignID, be sure to change it, in the query, to the correct
column name.

The form "fBenefitDesignView" has to be open when the query runs;
otherwise, you'll be prompted with the
[Forms]![fBenefitDesignView]![BenefitDesignID] prompt, again.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ9anvIechKqOuFEgEQJO8ACaA9np7n4FsZpKQOy4ylADMERKyBwAnRvc
faaSphXPRm8IVDuPaniKEMOW
=49LO
-----END PGP SIGNATURE-----

 

[Tracey]

That works perfectly. Thanks!

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The SQL should be like this:

PARAMETERS [What is current Benefit Design's end date?] Date,
[Forms]![fBenefitDesignView]![BenefitDesignID] Long;

UPDATE [Benefit Design]

SET BenefitDesignEndDate = [What is current Benefit Design's end date?]

WHERE BenefitDesignID = [Forms]![fBenefitDesignView]![BenefitDesignID]


I'm assuming the BenefitDesignID is a Long (numeric) data type. If it
is a string then change "Long" to TEXT(255), or whatever the correct
data type is.

If the name of the column in the table Benefit Design is other than
BenefitDesignID, be sure to change it, in the query, to the correct
column name.

The form "fBenefitDesignView" has to be open when the query runs;
otherwise, you'll be prompted with the
[Forms]![fBenefitDesignView]![BenefitDesignID] prompt, again.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ9anvIechKqOuFEgEQJO8ACaA9np7n4FsZpKQOy4ylADMERKyBwAnRvc
faaSphXPRm8IVDuPaniKEMOW
=49LO
-----END PGP SIGNATURE-----

I want to use a macro to trigger an update query from a subform called
fBenefitDesignView. The update query has a parameter which requires the user
to enter a date. The date is then updated to the underlying table Benefit
Design. However, I need the query to be filtered for the current
BenefitDesignID on the subform fBenefitDesignView so that the date will only
be updated for that record. Currently my update query updates all the records
with the date from the parameter. I've tried to put in a criteria on the
update query for BenefitDesignID as
[Forms]![fBenefitDesignView]![BenefitDesignID], but the query treats that as
another parameter to prompt me for.

Here is my current SQL:

UPDATE [Benefit Design] SET [Benefit Design].BenefitDesignEndDate = [What is
current Benefit Design's end date?]
WHERE ((([What is current Benefit Design's end date?])=[What is current
Benefit Design's end date?]));

How do I filter the query by the BenefitDesignID from the current subform
fBenefitDesignView?

Thanks for any help!