Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    SierraJuliet's Avatar
    SierraJuliet is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Dec 2017
    Location
    Earth
    Posts
    211

    Unbound Calculated Text Box Error #Name?

    I decided to change how an unbound text box calculated itself. I moved the DCount from the text box Control Source under Property Sheet into VBA. It works perfectly in the Property Sheet but in the VBA it is half backwards (see images). The following is the code, where somethings would be commented out and swapped just trying to figure out why #Name? keeps appearing when in form view. I have also tried switching String to Integer with same results. Note the GotFocus is merely to know it should populate values when I click on the text field (i.e. troubleshooting). I know it is something simple but figure some Access ninja knows the issue offhand.

    Code:
    Private Sub txtTotalCasesToDate_GotFocus()
    Dim strSQL As String strSQL = "SELECT Count(CaseNum) As CaseCount FROM tblCase " 'strSQL = Nz(DCount("CaseNum", "tblCase"), 0) Me![txtTotalCasesToDate].ControlSource = strSQL 'Me![txtTotalCasesToDate].Requery
    End Sub
    The first image (top left) is when in the form Design View (the actual value, subsequently deleted due to VBA for some reason auto adding that to Property Sheet Control Source). The second image (top right) is the current Design View (Unbound) look after deleting the auto added value. The third image (bottom) is in the original (image one) and current (image two) Form View (#Name?).



    Click image for larger version. 

Name:	Capture 1.PNG 
Views:	15 
Size:	743 Bytes 
ID:	32377Click image for larger version. 

Name:	Capture 3.PNG 
Views:	15 
Size:	875 Bytes 
ID:	32379

    Click image for larger version. 

Name:	Capture 2.PNG 
Views:	15 
Size:	909 Bytes 
ID:	32378

  2. #2
    Minty is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Try just setting the control source in code to

    SELECT Count(CaseNum) As CaseCount FROM tblCase

    Then put an = in front of it.

    By which time you may as well have used the DCount.


    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    It needs to be an expression using field names from the record source, such as
    =Count(CaseNum) or =Count(*)
    which would be the control source of the unbound textbox

  4. #4
    SierraJuliet's Avatar
    SierraJuliet is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Dec 2017
    Location
    Earth
    Posts
    211
    I have tried many variations to no avail. The following = expression is exactly the way it is setup in the Control Source of the Property Sheet for the text box and it works perfectly; however, trying the same thing using VBA gives me all sorts of frustration. Essentially this should be working the same way as before but no dice. Even tried specifying the form and sub form.

    Me![txtTotalCasesToDate].ControlSource = Nz(DCount("*", "tblCase"), 0)

    Now the reason for switching is so I may add a filter by date range that is untenable using the Property Sheet route.

  5. #5
    SierraJuliet's Avatar
    SierraJuliet is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Dec 2017
    Location
    Earth
    Posts
    211
    Quote Originally Posted by Minty View Post
    Try just setting the control source in code to

    SELECT Count(CaseNum) As CaseCount FROM tblCase

    Then put an = in front of it.

    By which time you may as well have used the DCount.


    Tried that, no dice.

  6. #6
    SierraJuliet's Avatar
    SierraJuliet is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Dec 2017
    Location
    Earth
    Posts
    211
    If we are unable to figure out a solution using Private Sub then I may just create a Public Function where I can add the WHERE clause for date range filtering, then set that Public Function as the Control Source for the unbound field. The problem now becomes extra work, if there is not a way to get an unbound text box on a form to use a designated Control Source.

  7. #7
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    All you should need to assign a value to the unbound textbox is the following:

    Code:
    Me.txtTotalCasesToDate=DCount("CaseNum", "tblCase")
    1. The .ControlSource isn't required
    2. Nz is superfluous with DCount - if there are no records, the value is 0

    Normally this code would be placed in the Form_Load event or similar
    Placing it in the GotFocus event is unlikely to work at all or at least may need a requery

    Something this simple certainly doesn't need a separate function
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  8. #8
    SierraJuliet's Avatar
    SierraJuliet is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Dec 2017
    Location
    Earth
    Posts
    211
    A SOLUTION TO PROBLEM

    So the best solution I could contrive for an unbound text box when not placing the code in the Property Sheet Control Source is the first and second set of code below, which was actually easier than doing it the other way (i.e. Private Sub).

    First I created a Module for the Public Function then added either code below (tested first code to mitigate troubleshooting variables, then changed it to the second set permanently). Then I added the function name into the Control Source of the unbound text box (=TotalCasesToDate).

    Now in keeping with the entire reason why I did not just paste the first set of code into the unbound text box Control Source is the second set of code actually evaluates whether or not the date range filter fields are empty. I wanted to mitigate any potential errors arising later if nothing was available for dates, which means all records should be counted.

    Forgive the "iSQL" I did not come up with a more creative name

    Code:
    Public Function TotalCasesToDate()
        TotalCasesToDate = DCount("[CaseNum]", "tblCase", "([DateCreated] Between #" & [Forms]![frmViewStats]![txtBeginDateFilterCase] & "# And #" & [Forms]![frmViewStats]![txtEndDateFilterCase] & "#)")
    End Function
    
    
    Public Function TotalCasesToDate()
        Dim iSQL As Long
        If [Forms]![frmViewStats]![txtBeginDateFilterCase] & "" = "" Or [Forms]![frmViewStats]![txtEndDateFilterCase] & "" = "" Then
             iSQL = DCount("[CaseNum]", "tblCase")
        Else
             iSQL = DCount("[CaseNum]", "tblCase", "([DateCreated] Between #" & [Forms]![frmViewStats]![txtBeginDateFilterCase] & "# And #" & [Forms]![frmViewStats]![txtEndDateFilterCase] & "#)")
        End If
        TotalCasesToDate = iSQL
    End Function

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Setting the ControlSource property with VBA would mean setting it to a string, like:

    Me.txtTotalCasesToDate.ControlSource = "=DCount('*', 'tblCase')"

    But as ridders52 points out, really should be setting Value property with result calculated in VBA.

    Or a function call as I now see in your recent post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    SierraJuliet's Avatar
    SierraJuliet is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Dec 2017
    Location
    Earth
    Posts
    211
    Quote Originally Posted by ridders52 View Post
    All you should need to assign a value to the unbound textbox is the following:

    Code:
    Me.txtTotalCasesToDate=DCount("CaseNum", "tblCase")
    1. The .ControlSource isn't required
    2. Nz is superfluous with DCount - if there are no records, the value is 0

    Normally this code would be placed in the Form_Load event or similar
    Placing it in the GotFocus event is unlikely to work at all or at least may need a requery

    Something this simple certainly doesn't need a separate function
    Thanks for the feedback ridders52. I know certain things are not required all the time but for some reason when using DCount directly in this unbound text box Control Source when I originally created it, the DCount would not function until I added the "Nz" function portion. Just one of those things were Access does not always cooperate.

    ^ UPDATE to above ^

    Disregard, I just realized why I needed the Nz Function (Face Palm). I do not permit Zero Length fields; therefore, any blank fields are NULL. Therefore, I must convert NULL fields to Zero Length with Nz function in order to calculate with DCount. So that was the reason why I needed it... otherwise it would spit an #Error

    Not superfluous
    Last edited by SierraJuliet; 01-25-2018 at 07:06 PM.

  11. #11
    SierraJuliet's Avatar
    SierraJuliet is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Dec 2017
    Location
    Earth
    Posts
    211
    Quote Originally Posted by June7 View Post
    Setting the ControlSource property with VBA would mean setting it to a string, like:

    Me.txtTotalCasesToDate.ControlSource = "=DCount('*', 'tblCase')"

    But as ridders52 points out, really should be setting Value property with result calculated in VBA.

    Or a function call as I now see in your recent post.
    Thanks for the feedback June7. Before I came here to post about issue I tried that and did not work, including "Me." and so forth. I flipped and flopped all sorts of variables and methods to no avail. Other than =Nz(DCount("*", "tblCase"), 0) directly in the Control Source for unbound text box was the Function call. Not to rehash everything but at one point it would show the expected value in the text box when in Design View but not Form View. Access is wacky sometimes.

  12. #12
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Just to make it absolutely clear

    Code:
    Me.txtTotalCasesToDate=DCount("CaseNum", "tblCase")
    means EXACTLY the same as

    Code:
    Me.txtTotalCasesToDate.Value=DCount("CaseNum", "tblCase")
    Value is the default item for the textbox so it can be omitted

    When I said .ControlSource isn't required ... it would have been more accurate to say 'isn't appropriate here'
    You are setting the value not the source of the data
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  13. #13
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The syntax I describe is valid. Must be an issue with the calculation itself.

    Examples of how I have used it:

    Code:
            If Not IsNull(.ctrFASB!flowrt) Then
                .ctrFASB.Form.tbxFlwRt.ControlSource = "FlowRt"
                .ctrFASB.Form.tbxFlwRt.SetFocus
            End If
    .tbxMassR.ControlSource = "=[Forms]![DataSoilsAgg]![tbxCSW]"
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  14. #14
    SierraJuliet's Avatar
    SierraJuliet is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Dec 2017
    Location
    Earth
    Posts
    211
    Quote Originally Posted by June7 View Post
    The syntax I describe is valid. Must be an issue with the calculation itself.

    Examples of how I have used it:

    Code:
            If Not IsNull(.ctrFASB!flowrt) Then
                .ctrFASB.Form.tbxFlwRt.ControlSource = "FlowRt"
                .ctrFASB.Form.tbxFlwRt.SetFocus
            End If
    .tbxMassR.ControlSource = "=[Forms]![DataSoilsAgg]![tbxCSW]"
    I have used a similar fashion before, I think you are right with it being an issue with processing the calculation. Might be missing some library or something somewhere.

  15. #15
    SierraJuliet's Avatar
    SierraJuliet is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Dec 2017
    Location
    Earth
    Posts
    211
    Quote Originally Posted by ridders52 View Post
    Just to make it absolutely clear

    Code:
    Me.txtTotalCasesToDate=DCount("CaseNum", "tblCase")
    means EXACTLY the same as

    Code:
    Me.txtTotalCasesToDate.Value=DCount("CaseNum", "tblCase")
    Value is the default item for the textbox so it can be omitted

    When I said .ControlSource isn't required ... it would have been more accurate to say 'isn't appropriate here'
    You are setting the value not the source of the data
    I understand and appreciate the help. It is just one of those anomaly things where sometimes known working things just do not behave. My original Nz code worked in the Property Sheet Control Source and it was a challenge getting that to work.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 22
    Last Post: 01-16-2018, 07:18 AM
  2. Replies: 6
    Last Post: 05-03-2016, 05:21 PM
  3. Unbound text boxes show #ERROR on data entry line
    By GraeagleBill in forum Forms
    Replies: 4
    Last Post: 01-07-2015, 12:44 AM
  4. Force update of unbound calculated control
    By LillMcGill in forum Forms
    Replies: 7
    Last Post: 04-15-2013, 06:51 PM
  5. Replies: 2
    Last Post: 06-11-2012, 09:37 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