Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Sep 2007
    Posts
    34

    Unanswered: #Name? error on unbound text box

    Hi all,

    I'm using Access 2007 right now.

    I've tried searching through the forum in order to find what I'm looking for and wasn't able to so I'm asking the community for help.

    I have an unbound textbox in frmMain where I would like it to load a ranking total from qryRanking.

    So far I've tried these two things:

    1) changing control source of the txtbox to = [Query_Ranking]![Ranking]

    2) loading a subform and doing =[Forms]![frmRanking]![Ranking]

    I receive a #Name? error for both.

    Thanks in advance for any assistance.

    Cheers.

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    If that's the only record in the query try:

    =DLookup("Ranking", "Query_Ranking")
    Paul

  3. #3
    Join Date
    Sep 2007
    Posts
    34
    Thanks Paul,

    What would I need to do if I need to add a where function so that the ID matches from the main form the data in the query?

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Paul

  5. #5
    Join Date
    Sep 2007
    Posts
    34
    Thanks - tried a bunch of different things but still unable to get any value in the text box yet. I'll continue to try different things hoping for the best

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    If you get stuck, post your effort and the data type of the field being filtered on.
    Paul

  7. #7
    Join Date
    Sep 2007
    Posts
    34
    Here's the most current one i've tried so far ..


    Private Sub txtRanking_BeforeUpdate(Cancel As Integer)

    Dim Ranking As Variant

    Ranking = DLookup("[Ranking]", "Query_Ranking", _
    "[ID]=’ " & Forms![Forms_Main]![ID] & " ' ")

    End Sub

  8. #8
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Is it throwing an error? All that's doing is populating a variable; you'll never see the result. For starters, I'd get rid of the extra spaces:

    Ranking = DLookup("[Ranking]", "Query_Ranking", "[ID]=’" & Forms![Forms_Main]![ID] & "'")

    I also put it on one line, as I prefer to debug one problem at a time (though the line continuation looked fine). To check the result, either

    Msgbox Ranking

    or

    Debug.Print Ranking

    which will print the result out to the VBA Immediate window. If you want it on the form:

    Me.TextboxName = Ranking

    though I don't know about the before update event of a control. What exactly are you trying to accomplish?
    Paul

  9. #9
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    One of the 'fun' things that Access will do is when you are typing in the ControlSource, it will also make the textbox's name the same as what you typed for the ControlSource. And basically that makes the #Name error when the ControlSource is a function. I am not sure if it is a circular reference or just it gets confused.

    Try changing the textbox's Name to txtSomething with your original ControlSource.

  10. #10
    Join Date
    Sep 2007
    Posts
    34
    Thanks for both the suggestions =)

    So here's what I've got so far but I'm unable to populate the msgbox or get any results


    Private Sub txtRanking_BeforeUpdate(Cancel As Integer)


    Dim Ranking As variant

    Ranking = DLookup("[Ranking]", "Query_Ranking", "[ID]=’" & Forms![Forms_Main]![ID] & "'")

    Me.txtRanking = Ranking
    MsgBox Ranking

    End Sub

    ++++++++++

    The Query_ranking is a query that has 10 different categories from a table and the total ranking is calculated using an expression. I'm trying to populate the value of the total of the categories on the form if that helps at all.

    Thx.

  11. #11
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    What is the data type of ID?
    Paul

  12. #12
    Join Date
    Sep 2007
    Posts
    34
    The data type of the ID is Integer (Automatically generated)

  13. #13
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    You might check that link again. Integer is a numerical value.
    Paul

  14. #14
    Join Date
    Sep 2007
    Posts
    34
    Thanks Paul

    I'm able to get the value I'm looking for now via the message box but it does not dynamically change in the textbox for some reason.

    So right now I have my code as this:

    ++++++++++++
    Private Sub txtRanking_BeforeUpdate(Cancel As Integer)

    Dim Ranking As Variant

    Ranking = DLookup("[Ranking]", "Query_Ranking", "[ID]=" & Forms![Forms_Main]![ID])

    If Ranking = Null Then

    Ranking = "No rank"
    Me.txtRanking.Text = Ranking

    Else

    Me.txtRanking.Value = Ranking

    End If

    End Sub

    ++++++++++++

    Thanks in advance =)

  15. #15
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    For starters, nothing is ever equal to Null; you want:

    If IsNull(Ranking) Then

    As mentioned earlier, I'm not sure this will work in the before update event. Try it in the after update event. Either requires you to be changing the value in that textbox, which doesn't really make sense if your intention is to set the value programmatically. I'd probably use the current event of the form or some other event to trigger this.

    By the way, simpler would be:

    Me.txtRanking = Nz(DLookup(...), "No ranking")
    Paul

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •