If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Textbox control source problem
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 |
#2
|
|||
|
|||
Textbox control source problem
On Fri, 5 Feb 2010 16:16:57 -0500, "Ron"
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 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 |
#3
|
|||
|
|||
Textbox control source problem
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 |
#4
|
|||
|
|||
Textbox control source problem
On Sat, 6 Feb 2010 17:13:04 -0500, "Ron" wrote:
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 [MVP] |
#5
|
|||
|
|||
Textbox control source problem
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 [MVP] |
#6
|
|||
|
|||
Textbox control source problem
On Sun, 7 Feb 2010 16:49:03 -0500, "Ron" wrote:
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. -- John W. Vinson [MVP] |
#7
|
|||
|
|||
Textbox control source problem
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 |
#8
|
|||
|
|||
Textbox control source problem
On Sun, 7 Feb 2010 23:59:28 -0500, "Ron" wrote:
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. -- John W. Vinson [MVP] |
#9
|
|||
|
|||
Textbox control source problem
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." 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! 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. 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. 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 |
#10
|
|||
|
|||
Textbox control source problem
On Mon, 8 Feb 2010 17:38:36 -0500, "Ron" wrote:
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. 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. 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. 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]) -- John W. Vinson [MVP] |
|
Thread Tools | |
Display Modes | |
|
|