Full Version: Assign a value to an unbound control
UtterAccess Forums > Microsoft® Access > Access Forms
would like to dynamically assign a value to an unbound control on a form. The value is looked up from another table and is dependant on the record currently displayed. I need the value to update as the user moves through the records, using the ONCurrent event. I have tried DLookup but that doesn't work as the query requires a parameter (the ID of the record currently being displayed). Currently the form is based on a table but I have tried basing the form on a query and binding the control to a field in the query but the problem with that is I am not able to edit or add records (see code below).
Does anyone have any advise for me as to the best way to get the value for the control? Is the best way to do something like this in code OR is there a better way to do it directly from the form OR a better way to do it using a query?

SELECT tblChild.*, (select fname + '  ' + lname
                        from tblcaseworker
                        where caseworkerid in (select top 1 tblchildCaseworkerhist.caseworkerid
                                                                 from tblchildcaseworkerhist
                                                                 where tblChildCaseworkerHist.childid = tblChild.ChildID
                                                                 order by startdate desc)) AS caseworkername,
                      (select top 1 tblChildstatus.entrydate
                       from tblchildstatus
                       where tblchildstatus.childid = tblChild.childID
                       order by entrydate desc) AS ChildCurrentStatusEntry,
                      (select top 1 tblChildstatus.exitdate
                       from tblchildstatus
                       where (tblchildstatus.childid = tblChild.childID)
                       order by entrydate desc) AS ChildCurrentStatusExit
FROM tblChild;
Larry Larsen
Using either the DLookup() or some sql should work, does the above sql return a single value..!!
If so the sql is doing all the hard work which will allow you to use the DLookup() to extract that value from it..
e.TextBoxName = DLookup("QueryFieldName","QueryName")
Alternatively some code:
Dim rs As DAO.Recordset
'/ Create your recordset..
Set rs = CurrentDb.OpenRecordset("Your SQL statement goes here")
'/ Populate your form control..
Me.TextBoxName = rs!FieldNameGoesHere
'/ Close shop..
set rs = nothing

Thanks for the reply.
don't see how DLookup can work if the query is different every time. My understanding is that if the query
requires a parameter DLookup is not effective. It would seem that the only way to update the value of the
unbound control is through code.
And yes, the query is designed to only return one value. The query is returning the most recent caseworker for
the child. The '419' is just a placeholder I am using to test the query and would have to be updated dynamically as the user moves throughout the recordset.

SELECT fname+' '+lname AS caseworker
FROM tblcaseworker
WHERE caseworkerid in (select top 1 caseworkerid from tblchildcaseworkerhist where childid = 419 order by startdate desc);

This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.