That doesn't work either. The equals sign actually needs to be there
fo
the
"Less than or equal to" criteria. Access didn't add the square
brackets
as
you stated it should. Could there be some access problem? I'm also
having a
problem with a dialog form. See my post "can't find field" in Forms
Design.
Sorry for all the trouble.
Walter
:
Walter,
try it like this: remove the second Forms, and remove the equals sign
as
well
forms!frmOrders!OrderDetailsForm!LoadedDate
is all that goes in the criteria row of the query.
After you save the query, access will put the square brackets in for
you
(easier)
Jeanette Cunningham
Thanks for catching that. However I'm still getting the error.
Walter
:
You've got Forms, rather than Form, in Query 2 and Query 3.
(The .Form is correct: I assume Jeanette's use of ![form] was a
typo.)
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
I've changed the syntax as suggested and am still getting the
same
error.
I
tried using "." instead of "!" and with and without brackets with
no
results.
I assume since the reference is in the queries the correct
reference
is
to
refer to a control when you are not in these forms. ie:
Forms!Mainform!Subform1.Form!ControlName
Is this correct?
Here is what I now have.
Query 1
<=[forms]!frmOrders!OrderDetailsForm.Form!LoadedDate));
Query 2
<=[forms]![frmOrders]![OrderDetailsForm].[Forms]![LoadedDate]));
Query 3
<=[forms]![frmOrders]![OrderDetailsForm].[Forms]![LoadedDate]));
You notice the first query doesn't have brackets. I included
them
as
in
the
others, saved and closed. When I reopened the query they were
not
there.
Thanks!
Walter
:
Walter,
If you are trying to read the value of LoadedDate from a
subform,
you
need
to change the syntax.
A subform is not open by itself, but is part of the main form,
so
you
must
you use the main form to set a reference to the subform.
This link explains how to reference controls on subforms
http://www.mvps.org/access/forms/frm0031.htm
You will need something like
<=[forms]![frmOrders]![NameOfSubformControl]![form]![LoadedDate]))
Jeanette Cunningham
Since I have no need for the cost to be on the form, I was
trying
to
set
that
value in the underlying table. I wasn't sure if that's
possible
so
I
did
try
adding that control to the form, setting it's visible property
to
false
and
then using the Me.Cost reference and still received the same
error.
The
query finds the correct price based on the loaded date and
works
independantly if you supply the date when prompted.
The main form's PK is OrderID - autonumber
The subform's PK is OrderDetailsID - autonumber
The subform control Link Child Fields = OrderID; Link Master
Fields
=
OrderID
The NetWeight control's name is NetWeight
There are 3 queries to find the proper value. Their SQL are:
qryLimePrice
SELECT tblLimeCost.Price, tblLimeCost.EffectiveDate
FROM tblLimeCost
WHERE
(((tblLimeCost.EffectiveDate)<=[forms]![frmOrderDetails]![LoadedDate]));
qryLimePrice2
SELECT Max(qryLimePrice.EffectiveDate) AS MaxOfEffectiveDate
FROM qryLimePrice
HAVING
(((Max(qryLimePrice.EffectiveDate))<=[forms]![frmOrderDetails]![LoadedDate]));
qryLimePrice3
SELECT tblLimeCost.EffectiveDate, tblLimeCost.Price
FROM qryLimePrice2 INNER JOIN tblLimeCost ON
qryLimePrice2.MaxOfEffectiveDate = tblLimeCost.EffectiveDate
WHERE
(((tblLimeCost.EffectiveDate)<=[forms]![frmOrderDetails]![LoadedDate]));
Thanks for your help!
Walter
:
Walter,
I would expect the after update event code to be more like
this
Me.TheControlName = DLookup("Price", "qryLimePrice3") *
Me.NetWeight
The expression [Tables]![tblOrderDetails]![Cost] is written
incorrectly - I
can't guess what you are trying to do.
Please post back with details of your main form, primary key
and
TheControlName you are trying to set after user chooses the
NetWeight
It would also help if we knew details of your subform, the
name
of
the
control for NetWeight, the link master and child fields.
And what you are trying to do with = DLookup("Price",
"qryLimePrice3") *
Me.NetWeight
Jeanette Cunningham
I have the following code in the AfterUpdate event of a
control.
It
produces
the error.
"The expression you entered as a query parameter produced
this
error.
The
object doesn't contain the Automation Object
Forms!frmOrderDetails!LoadedDate"
Both controls are on the same subform.
[Tables]![tblOrderDetails]![Cost] = _
DLookup("Price", "qryLimePrice3") * Me.NetWeight
What am I doing wrong?
Thanks
Walter
