Results 1 to 14 of 14
  1. #1
    virgilio is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Posts
    81

    Display results of calculation in textbox

    I'm porting an Excel front end that was written by someone else into an Access front end using forms. I'm stuck on trying to calculate a value based on other table values and display the results in a textbox. I will list out what I have tried so far at the end, but these are the specifics of my problem:

    I need to calculate whether a case is 'open' or 'closed' based on the values of the fields in that record. This was accomplished in the Excel front end by referencing a query that contained a calculation that provided the open/closed status that I want. That calculation is something like:
    Code:
    OPEN_STATUS: IIf([Private_TRUE]=False And [Private_Status]<>"DISPOSITIONED","OPEN","CLOSED") AS OPEN_STATUS
    (private field names removed)

    I have tried:
    1. using DLookup to access this field from a global module, but it is returning empty values.


    2. looking for a built in method that could help parse and/or display the results of my calculation, but I'm new to VBA and not sure what to look for. My form is based on a table and [Private_TRUE] and [Private_Status] are fields of that table.

    It would be nice if I could perform this IIF calculation in the code of the form and display the value in a TextBox, because I have other values that I would like to parse, simplify and display.

    Why would DLookup return empty values?
    How can I make the Control Source of a textbox refer to a variable returned from a function/sub in the same form based on Table fields?

    Thanks in advance

  2. #2
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Perhaps easiest is to make the recordsource for the form a query. Then add the calculated OPEN_STATUS field to the query.


    Select * from ThatTable, IIf([Private_TRUE]=False And [Private_Status]<>"DISPOSITIONED","OPEN","CLOSED") AS OPEN_STATUS

    Then just bind the Open_status textbox to the new field.

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    This may help:

    http://www.baldyweb.com/Function.htm

    On a form you could have a textbox with a control source of:

    =FunctionName([Private_TRUE], [Private_Status])

    presuming those fields were part of the form's record source.

    Hard to say why the DLookup would return an empty value without seeing it. Generally, it probably didn't find a record matching the criteria.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by davegri View Post
    Perhaps easiest is to make the recordsource for the form a query. Then add the calculated OPEN_STATUS field to the query.


    Select * from ThatTable, IIf([Private_TRUE]=False And [Private_Status]<>"DISPOSITIONED","OPEN","CLOSED") AS OPEN_STATUS

    Then just bind the Open_status textbox to the new field.
    That would probably be easiest, but the syntax would be

    Select *, IIf([Private_TRUE]=False And [Private_Status]<>"DISPOSITIONED","OPEN","CLOSED") AS OPEN_STATUS from ThatTable
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    virgilio is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Posts
    81
    Quote Originally Posted by pbaldy View Post
    This may help:

    http://www.baldyweb.com/Function.htm

    On a form you could have a textbox with a control source of:

    =FunctionName([Private_TRUE], [Private_Status])

    presuming those fields were part of the form's record source.

    Hard to say why the DLookup would return an empty value without seeing it. Generally, it probably didn't find a record matching the criteria.
    Would you mind amplifying on your 'Function' guide?

    Code:
    If you were getting a value returned from the function for testing, it might look like this:
    If YourFunctionName("test", 23) = False Then
    If I understand VBA correctly, the next line would be something like
    Code:
    YourFunctionName = "False"
    to return a false value, right?

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Well, setting the return value would take place inside the function. Something like:

    Code:
    Public Function YourFunctionName(strVariable As String, lngVariable As Long) As Boolean
      If strVariable = "Blah" And lngVariable = 7 Then
        YourFunctionName = True
      Else
        YourFunctionName = False
      End If
    End Function
    The code to test/set the variables and return could be far more complex, as determined by your situation. For something simple and static, davegri's solution is simpler.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    virgilio is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Posts
    81
    Thanks, I'm getting #Name? and I wasn't sure if that meant that my function was wrong or my reference was wrong

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You can test the function from the Immediate window. Using my example above

    ?YourFunctionName("test", 123)

    Make sure the function is in a standard module that doesn't have the same name. If you're still stuck, can you attach the db here?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    virgilio is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Posts
    81
    I got it working but I lost some functionality that I built (from a tutorial).

    My new function is as follows:
    Code:
    Function getOpenStatus() As String    If Me.Private_TRUE = False And Private_STATUS <> "DISPOSITIONED" Then
            getOpenStatus = "Open"
        Else
            getOpenStatus = "Closed"
        End If
    
    
    End Function
    This is now populating a continuous form with Open or Closed, thank you for your help! However, it only populates the visible 'cells' until I scroll up, which I could live with but the functionality that I lost was the ability to sort by clicking on the header.
    My code for doing this was in the "On Click" event:

    Code:
    Private Sub OpenStat_Label_Click()
    If OrderBy = "OPEN_STATUS" Then
       OrderBy = "OPEN_STATUS desc"
    Else
       OrderBy = "OPEN_STATUS"
    End If
    End Sub
    Now that it is a calculated field, this sort doesn't work. Is there a work around?

    Other info: I arrived at this because a previous iteration of this form wouldn't accept input and was displaying "This Recordset is Not Updateable", which I think was because I was using multiple sources to populate that form. I realize that there are a lot of reasons that can happen so I started over, only using DLookup to reference other tables. BTW, I can't "column sort" the fields built with DLookup either, but one problem at a time!

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Moving it to the source query as davegri suggested would make it a sort-able field. It could be a call to a function or the IIf() shown. As is, Access is "helping" you by not bothering to calculate values you haven't asked to look at yet.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    virgilio is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Posts
    81
    Thanks for your help on this everyone.

  12. #12
    virgilio is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Posts
    81
    Quote Originally Posted by davegri View Post
    Perhaps easiest is to make the recordsource for the form a query. Then add the calculated OPEN_STATUS field to the query.


    Select * from ThatTable, IIf([Private_TRUE]=False And [Private_Status]<>"DISPOSITIONED","OPEN","CLOSED") AS OPEN_STATUS

    Then just bind the Open_status textbox to the new field.
    Would you mind giving some advice on setting up a query? I'm losing data as though an Inner Join is being performed, ie I'm only showing fields that have matches in both tables. I want a Left Join that shows all values from the primary table, and ignores blanks in the second.

  13. #13
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Would you mind giving some advice on setting up a query? I'm losing data as though an Inner Join is being performed, ie I'm only showing fields that have matches in both tables. I want a Left Join that shows all values from the primary table, and ignores blanks in the second.
    To get the desired join results, do this in query design view:
    Click image for larger version. 

Name:	first.png 
Views:	7 
Size:	17.4 KB 
ID:	40397

    Then:

    Click image for larger version. 

Name:	second.png 
Views:	7 
Size:	28.9 KB 
ID:	40398

  14. #14
    virgilio is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Posts
    81
    Thank you, very much appreciated!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 08-21-2019, 07:17 AM
  2. Replies: 9
    Last Post: 08-26-2015, 11:03 AM
  3. Date calculation in textbox
    By Bosworth in forum Access
    Replies: 7
    Last Post: 02-18-2015, 06:50 PM
  4. Replies: 3
    Last Post: 05-02-2013, 10:36 AM
  5. Replies: 3
    Last Post: 08-13-2012, 08:04 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums