Textbox control source problem

[Ron]

I have a feeling my problem is easy, but I'm hung up.

I have a many to many relation between tblItems (of jewelry) and tblStatus
("under construction," "consigned," "sold," etc.). I have a main form based
on tblItems and a properly functioning subForm based on the join table for
tblItems and tblStatus. In addition to the foreign keys, the join table
contains a date field - the date the status changed. IOW, for each item of
jewelry in the main form, the subform shows the status history.

The record source for the subForm is:

SELECT jtblItemsStatus.ItemsID, jtblItemsStatus.StatusID,
jtblItemsStatus.DateStatusChange
FROM tblStatus INNER JOIN jtblItemsStatus ON tblStatus.ID =
jtblItemsStatus.StatusID;

The subform consists of a combobox and a text box. The combobox bound
column is jtblItemsStatusID and the displayed text is the status field from
tblStatus. The text box shows the correspoinding date.

Now, I've placed two text boxes on the main form. (The idea is to display
current status at a glance.) In the first, I show the date for the *most
recent* status change. The control source is:

=DMax("[datestatuschange]","jtblItemsStatus","[jtblItemsStatus.ItemsID]=forms.frmMainForm.ID")

This works as intended. frmMainForm.ID is the PK for the current record in
the main form.

In the second box, I wish to show that same status field text from the
subform that corresponds to the latest date. I've played with DLookup (on
tblStatus) and with a query-subquery. No success, but I'm not sure whether
it's a syntax issue or completely wrong approach. (I've read that the third
expression of DLookup should be thought of as a query where clause, but I'm
confused about whether it can contain "join-like" references to other
tables.)

Would appreciate advice on the proper control source for the second textbox.
One thing I'd rather not do is derive it from the first textbox (in case I
change design later).

Many thanks, Ron

 

[Tom van Stiphout]

On Fri, 5 Feb 2010 16:16:57 -0500, "Ron" <[email protected]>
wrote:

Your subform recordsource does not need to join with tblStatus.

I would create a query that returns the StatusName, then use it in a
DLookup:
=DLookup("StatusName", "myQuery")

The query would be like this:
SELECT StatusName
FROM tblStatus INNER JOIN jtblItemsStatus ON tblStatus.ID =
jtblItemsStatus.StatusID
WHERE jtblItemsStatus.ItemsID=forms.frmMainForm.ID

-Tom.
Microsoft Access MVP

 

[Ron]

Thank you for responding. But, unless I miss something, that query does
nothing to return ONLY the record corresponding to the latest date.
Remember, for any item in the current main form, there can be a bunch of
statuses and corresponding dates in the subform. I want only the status
with the latest date.

To generalize my problem, the question becomes: how to select for *only* the
record (in a subform whose record source is the query I gave above) with the
latest date, so that I can relate a FK in that record to a lookup in a table
whose PK equals that FK?

After some heavy googling, turns out "latest date" questions have been
asked. One approach appears to involve using the Max(fldStatusDate) and
grouping on the ItemsID, so that only a single record is returned. For my
case, however, I haven't figured out just how to do this. (Not that much
experience with SQL design.) When I try it with some test data, there's a
group for every Item in the main table, even though I've got the form open
and pointing to a single record. And even if that were not an issue, I
still couldn't include StatusID as a selected field, since there are several
of those and there'd be a group for each.

As you see, I'm confused. Any additional help much appreciated. -Ron

I would create a query that returns the StatusName, then use it in a
DLookup:
=DLookup("StatusName", "myQuery")

The query would be like this:
SELECT StatusName
FROM tblStatus INNER JOIN jtblItemsStatus ON tblStatus.ID =
jtblItemsStatus.StatusID
WHERE jtblItemsStatus.ItemsID=forms.frmMainForm.ID

-Tom.
Microsoft Access MVP

I have a feeling my problem is easy, but I'm hung up.

I have a many to many relation between tblItems (of jewelry) and tblStatus
("under construction," "consigned," "sold," etc.). I have a main form
based
on tblItems and a properly functioning subForm based on the join table for
tblItems and tblStatus. In addition to the foreign keys, the join table
contains a date field - the date the status changed. IOW, for each item
of
jewelry in the main form, the subform shows the status history.

The record source for the subForm is:

SELECT jtblItemsStatus.ItemsID, jtblItemsStatus.StatusID,
jtblItemsStatus.DateStatusChange
FROM tblStatus INNER JOIN jtblItemsStatus ON tblStatus.ID =
jtblItemsStatus.StatusID;

The subform consists of a combobox and a text box. The combobox bound
column is jtblItemsStatusID and the displayed text is the status field
from
tblStatus. The text box shows the correspoinding date.

Now, I've placed two text boxes on the main form. (The idea is to display
current status at a glance.) In the first, I show the date for the *most
recent* status change. The control source is:

=DMax("[datestatuschange]","jtblItemsStatus","[jtblItemsStatus.ItemsID]=forms.frmMainForm.ID")

This works as intended. frmMainForm.ID is the PK for the current record
in
the main form.

In the second box, I wish to show that same status field text from the
subform that corresponds to the latest date. I've played with DLookup (on
tblStatus) and with a query-subquery. No success, but I'm not sure
whether
it's a syntax issue or completely wrong approach. (I've read that the
third
expression of DLookup should be thought of as a query where clause, but
I'm
confused about whether it can contain "join-like" references to other
tables.)

Would appreciate advice on the proper control source for the second
textbox.
One thing I'd rather not do is derive it from the first textbox (in case I
change design later).

Many thanks, Ron

 

[John W. Vinson]

To generalize my problem, the question becomes: how to select for *only* the
record (in a subform whose record source is the query I gave above) with the
latest date, so that I can relate a FK in that record to a lookup in a table
whose PK equals that FK?

If you want an editable recordset, you will need to use a Subquery. Put a
criterion on the datefield resembling

=(SELECT Max([datefield]) FROM tablename AS X WHERE X.FK = tablename.FK)

This correlated subquery will narrow the selection to only the row(s) with the
largest date value.

 

[Ron]

Thank you . Using that suggestion, I constructed a query that works when I
run it from the SQL code window. It prompts for the value of the FK
corresponding to the main record PK, then gives the (dataset with single
record) result I want.

However. When I paste that query into the control source of the textbox,
the form shows #Name? . I've read about this, but so far haven't discovered
the cause in my instance. I'm not sure what you mean by "editable"
recordset, and am wondering if that has something to do with this problem.
All I want to do is display a small text string - the status - in the
textbox. And all I've done is, using the expression builder, paste a
functioning query into a textbox control source.

I know this is a dumb noob issue. Unfortunately, that's what I am.

Anyway, thanks for help so far. -Ron

To generalize my problem, the question becomes: how to select for *only*
the
record (in a subform whose record source is the query I gave above) with
the
latest date, so that I can relate a FK in that record to a lookup in a
table
whose PK equals that FK?

If you want an editable recordset, you will need to use a Subquery. Put a
criterion on the datefield resembling

=(SELECT Max([datefield]) FROM tablename AS X WHERE X.FK = tablename.FK)

This correlated subquery will narrow the selection to only the row(s) with
the
largest date value.

 

[John W. Vinson]

Thank you . Using that suggestion, I constructed a query that works when I
run it from the SQL code window. It prompts for the value of the FK
corresponding to the main record PK, then gives the (dataset with single
record) result I want.

However. When I paste that query into the control source of the textbox,
the form shows #Name? . I've read about this, but so far haven't discovered
the cause in my instance. I'm not sure what you mean by "editable"
recordset, and am wondering if that has something to do with this problem.
All I want to do is display a small text string - the status - in the
textbox. And all I've done is, using the expression builder, paste a
functioning query into a textbox control source.

A Control Source needs to be a single value - NOT a Query. A Query can be the
Recordsource of a form, and could have 255 fields and millions of rows; it's
not going to work in a textbox's Control Source.

You could use a DLookUp expression based on your query as the Control Source:

=DLookUp("[SomeField]", "[SomeQuery]", "optional criteria")

If your query only returns one row you can leave off the third argument.

 

[Ron]

A Control Source needs to be a single value - NOT a Query. A Query can be
the
Recordsource of a form, and could have 255 fields and millions of rows;
it's
not going to work in a textbox's Control Source.

You could use a DLookUp expression based on your query as the Control
Source:

=DLookUp("[SomeField]", "[SomeQuery]", "optional criteria")

If your query only returns one row you can leave off the third argument.

Well I meant I was specifying MyQuery!Status as the control source - ie. one
field of the dataset. (I've been trying to construct a single query that
would solve the problem described above.) Is that still a no-no?

I found a typo in the original query which explains why I was being prompted
for a parameter value before running it. But having fixed that, the query
is back to yielding multiple rows - one for each FK corresponding to the PK
of the parent table. Perhaps I don't actually need a *correlated* subquery
after all, maybe just an ordinary one. Not sure, need to try more stuff.

I am experimenting with DLookUp, as you say, using it on that correlated
query. The trick is to get that third argument constructed properly. Well,
for me it's a trick. Because I thought I'd already had the optional
criteria built into the query.

I'm not a stranger to SQL, but obviously I need deeper grasp of concepts.
Thanks for the advice. -Ron

 

[John W. Vinson]

A Control Source needs to be a single value - NOT a Query. A Query can be
the
Recordsource of a form, and could have 255 fields and millions of rows;
it's
not going to work in a textbox's Control Source.

You could use a DLookUp expression based on your query as the Control
Source:

=DLookUp("[SomeField]", "[SomeQuery]", "optional criteria")

If your query only returns one row you can leave off the third argument.

Well I meant I was specifying MyQuery!Status as the control source - ie. one
field of the dataset. (I've been trying to construct a single query that
would solve the problem described above.) Is that still a no-no?

Yes. You can't just reference an unbound query in this way, you need a
DLookUp.

I found a typo in the original query which explains why I was being prompted
for a parameter value before running it. But having fixed that, the query
is back to yielding multiple rows - one for each FK corresponding to the PK
of the parent table. Perhaps I don't actually need a *correlated* subquery
after all, maybe just an ordinary one. Not sure, need to try more stuff.

If you want the query to return a value pertaining to the record shown on the
current form, then you need to somehow tell Access which record that is!

I am experimenting with DLookUp, as you say, using it on that correlated
query. The trick is to get that third argument constructed properly. Well,
for me it's a trick. Because I thought I'd already had the optional
criteria built into the query.

Could you please post your current query SQL and the text of the DLookup? It's
not clear to me.

 

[Ron]

A Control Source needs to be a single value - NOT a Query. A Query can
be
the
Recordsource of a form, and could have 255 fields and millions of rows;
it's
not going to work in a textbox's Control Source.

You could use a DLookUp expression based on your query as the Control
Source:

=DLookUp("[SomeField]", "[SomeQuery]", "optional criteria")

If your query only returns one row you can leave off the third argument.

Well I meant I was specifying MyQuery!Status as the control source - ie.
one
field of the dataset. (I've been trying to construct a single query that
would solve the problem described above.) Is that still a no-no?

Yes. You can't just reference an unbound query in this way, you need a
DLookUp.

Ok, understood. When I think about the query as an instantiated recordset,
I guess it makes sense that it wouldn't have a "value property."

If you want the query to return a value pertaining to the record shown on
the
current form, then you need to somehow tell Access which record that is!

That much I understand I thought the fact that I have established
persistent relations between the tables establishes just that?! At any
rate, I've tried to explicitly incorporate the relations, but it still
doesn't work for me.

Could you please post your current query SQL and the text of the DLookup?
It's
not clear to me.

Here's the query built (in design view with manual entry of the where
clause) on your suggestion of using a correlated subquery (although I got
the same dataset by adding the parent table ID, tblItems.ID to the selection
and substituting it for the jtblItemsStatus.ItemsID in that last assignment
of the where clause ie. with a conventional (uncorrelated) subquery) :

SELECT tblStatus.fldStatus, [jtblItemsStatus.DateStatusChange] AS Expr1,
jtblItemsStatus.StatusID, jtblItemsStatus.ItemsID
FROM tblStatus INNER JOIN (tblItems INNER JOIN jtblItemsStatus ON
tblItems.ID = jtblItemsStatus.ItemsID) ON tblStatus.ID =
jtblItemsStatus.StatusID
WHERE ((([jtblItemsStatus.DateStatusChange])=(select max(DateStatusChange)
from jtblItemsStatus as X where X.ItemsID = jtblItemsStatus.ItemsID)));

Expr1 does give the latest date for each value of ItemsID (as you indicated
it would.) My problem is that I want it to give a record for only the
*current* value of Items.ID. But ok, I accept the multiple recordset for
now, and try DLookup on it. Here's the function:

DLookUp("[QryforCurrentStatusTxtBox]![fldStatus]","[QryforCurrentStatusTxtBox]","[QryforCurrentStatusTxtBox]![ItemsID]=[tblItems]![ID]")

Doesn't work. Something wrong with the criterion part.

John, many thanks for your time and patience. -Ron

 

[John W. Vinson]

That much I understand I thought the fact that I have established
persistent relations between the tables establishes just that?! At any
rate, I've tried to explicitly incorporate the relations, but it still
doesn't work for me.

Well... no.

The relationship says that every record in jtblItemsStatus must have a
StatusID field which matches some record in tblStatus. That's ALL THAT IT
MEANS. It doesn't automagically tell a form *which* record that might be.

Could you please post your current query SQL and the text of the DLookup?
It's
not clear to me.

Here's the query built (in design view with manual entry of the where
clause) on your suggestion of using a correlated subquery (although I got
the same dataset by adding the parent table ID, tblItems.ID to the selection
and substituting it for the jtblItemsStatus.ItemsID in that last assignment
of the where clause ie. with a conventional (uncorrelated) subquery) :

SELECT tblStatus.fldStatus, [jtblItemsStatus.DateStatusChange] AS Expr1,
jtblItemsStatus.StatusID, jtblItemsStatus.ItemsID
FROM tblStatus INNER JOIN (tblItems INNER JOIN jtblItemsStatus ON
tblItems.ID = jtblItemsStatus.ItemsID) ON tblStatus.ID =
jtblItemsStatus.StatusID
WHERE ((([jtblItemsStatus.DateStatusChange])=(select max(DateStatusChange)
from jtblItemsStatus as X where X.ItemsID = jtblItemsStatus.ItemsID)));

Expr1 does give the latest date for each value of ItemsID (as you indicated
it would.) My problem is that I want it to give a record for only the
*current* value of Items.ID. But ok, I accept the multiple recordset for
now, and try DLookup on it. Here's the function:

DLookUp("[QryforCurrentStatusTxtBox]![fldStatus]","[QryforCurrentStatusTxtBox]","[QryforCurrentStatusTxtBox]![ItemsID]=[tblItems]![ID]")

Doesn't work. Something wrong with the criterion part.

Take the criterion out of the quotemarks. Since the only recordset referenced
inside the DLookUp is the query, there's no need to qualify the fieldnames:

DLookUp("[fldStatus]", "[QryforCurrentStatusTxtBox]", "[ItemsID]=" &
[tblItems]![ID])

 

[Ron]

Well... no.

The relationship says that every record in jtblItemsStatus must have a
StatusID field which matches some record in tblStatus. That's ALL THAT IT
MEANS. It doesn't automagically tell a form *which* record that might be.

John, the subform based on the query given in my OP works. If I change a
record on the MainForm, the data in the subform (whose recordsource is a
query built from jtblItemsStatus) change accordingly. It's the ID for *that
parent table* (ItemsID in the join table) for which I want the latest date
record in the jointable so that I may use the FK of tblStatus to lookup the
value of fldStatus.

DLookUp("[QryforCurrentStatusTxtBox]![fldStatus]","[QryforCurrentStatusTxtBox]","[QryforCurrentStatusTxtBox]![ItemsID]=[tblItems]![ID]")

Doesn't work. Something wrong with the criterion part.

Take the criterion out of the quotemarks. Since the only recordset
referenced
inside the DLookUp is the query, there's no need to qualify the
fieldnames:

DLookUp("[fldStatus]", "[QryforCurrentStatusTxtBox]", "[ItemsID]=" &
[tblItems]![ID])

Still doesn't work. Get #Name? error in the control and when I "?" the
function in the debug immediate window I get "External reference not
defined." Tried various syntactical mods, all with same result. When you
say "only recordset referenced inside" you do see that I'm referencing
tblItems, the parent of the join table?

I have a feeling I'm making a very fundamental error here. Forest for trees
kinda deal.

-Ron