Results 1 to 15 of 15
  1. #1
    Nikki Harry is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Apr 2018
    Location
    Manila
    Posts
    13

    #Name error on a subform field

    Originally I created a form/subform from two linked tables using the wizard. Everything worked just fine. My next step was to add a field to the subform using data from a third table. I did this by opening the subform and adding a text box. After researching DLookUp and placing the correct code into the field's control source everything worked well.



    The wheel fell off when I viewed the main form, which contains the subform and its new field. I get accurate records and data for all fields that take data from the the original two tables but a #Name error in the newly added field (data from third table).

    The Control Source code that works while the subform is open as a stand-alone form is this:
    =DLookUp("[Product_Price]","[03Products]","[ID_Products]=" & [Forms]![02Transactions Subform]![Product_Id])

    In order for the main form to display the subform showing correct data value in the new field; do I have to change the code above in the subform, or insert more code on the main form controls? If so, how is the main form control on the subform amended?

    Confused? I am.

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    If the code is in the subform, just use the Me. prefix.

    Otherwise, you need the full Forms!mainformname.subformlname.form.[Product_ID]

    OR substitute with Me. For first part if in main form
    Or substitute first part with Parent. If in subform

    Personally, I'd use the first of these four options if code is in the subform
    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

  3. #3
    Nikki Harry is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Apr 2018
    Location
    Manila
    Posts
    13
    Hi Colin,

    Thanks for the really fast answer.

    'Me' is a concept I haven't learned (yet), so I could do with a bit of clarification. You are correct, the code is in the subform (in the control source for the field, which has the inspiring name of 'Text 23'.

    If I get you right, all I need to do is alter the code in the subform and it will affect the main form in such a way that the field 'Text 23' will show the values while being viewed via the main form. If so, that would be stupendous.

    Now this is bit where I show my true level of ignorance; what does 'Me' replace in the code:
    =DLookUp("[Product_Price]","[03Products]","[ID_Products]=" & [Forms]![02Transactions Subform]![Product_Id])

    Thanks for looking at this.

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by Nikki Harry View Post
    Hi Colin,

    Thanks for the really fast answer.

    'Me' is a concept I haven't learned (yet), so I could do with a bit of clarification. You are correct, the code is in the subform (in the control source for the field, which has the inspiring name of 'Text 23'.

    If I get you right, all I need to do is alter the code in the subform and it will affect the main form in such a way that the field 'Text 23' will show the values while being viewed via the main form. If so, that would be stupendous.

    Now this is bit where I show my true level of ignorance; what does 'Me' replace in the code:
    =DLookUp("[Product_Price]","[03Products]","[ID_Products]=" & [Forms]![02Transactions Subform]![Product_Id])

    Thanks for looking at this.
    The Me. notation is used to refer to controls in the current form (or subform, report, subreport.
    Parent. can be used to refer to the main form from a subform

    In this case, replace all the text to the right of the = sign with
    Code:
    " & Me.Product_ID)
    BTW the [] can all be removed. Only needed if table, field or control names include a space or a special character such as %.

    I'm not quite sure what your second paragraph means but, if I understand you correctly, the answer is yes
    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

  5. #5
    Nikki Harry is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Apr 2018
    Location
    Manila
    Posts
    13
    Had high hope Colin, but fell at the last hurdle. All is good with the subform, using either the full or 'Me' code in the Control Source field of the subform property sheet. Just fails to load that one text box when the subform is loaded on opening the main form. Everything else loads correctly.

    Never mind, new day. The adventure continues!

  6. #6
    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 clarify, you used this as the control source for a textbox in your subform:
    Code:
    =DLookUp("Product_Price","03Products","ID_Products=" & Me.Product_Id)
    This means you are looking up the Product_Price field in the table 03Products where the field ID_Products is equal to the value of the Product_Id control in the subform.
    Also that ID_Products is a number datatype field

    All true?

    When you say, it fails to load, do you get an error or is the textbox empty?
    If the latter, have you checked there is a matching record in the table?
    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

  7. #7
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    The Me. notation is used to refer to controls in the current form (or subform, report, subreport.
    Would it be fair to say that Me refers to the form or report in which an object or property being referenced is on the same report/form? "Current" implies a different thing to me. I cannot think of a Me.method so I didn't include methods.

    When trouble shooting references, try replacing them with literal values temporarily as a test. The value has to be valid of course, so in your case, pick one from the domain being looked up that should work. So you replace [Forms]![02Transactions Subform]![Product_Id] with a valid value. If it works, the problem is the reference. I can't figure out if your subform is looking for a value contained on the main form or not, since you didn't provide the names of the forms involved or enough info about what's being referenced. If it is, know that a subform loads first, and the main form second. If the sub is looking for a value on the main by way of a function or calculation, it can't find it because it's not there - and you are using a function (DLookup)
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by Micron View Post
    Would it be fair to say that Me refers to the form or report in which an object or property being referenced is on the same report/form? "Current" implies a different thing to me. I cannot think of a Me.method so I didn't include methods.
    Point accepted. That was indeed what I meant but with hindsight I can see it was ambiguous & potentially misleading
    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

  9. #9
    Nikki Harry is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Apr 2018
    Location
    Manila
    Posts
    13
    Many Thanks to both ridders52 (Colin) and Micron, with your help the issue is solved.
    Took me a while playing around with the various lines of code. Couldn't get the 'Me.' thing to work, so it was the full version that worked directly in the Control Source field. I haven't tried the VBA, macro etc methods (I don't know enough and I'm lost enough).
    A useful exercise as it also highlighted several issues with my table relationships, which I have now addressed.
    Once again, a big thank you.

  10. #10
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You can't use Me in a control to refer to the form or report. Me is only for code (procedures). I think what threw us off was the word "code in your prior post. In a textbox you can enter expressions, not code. After reading your last post I reviewed your earlier ones. Now I see where that clue was missed - at least by me.

  11. #11
    Nikki Harry is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Apr 2018
    Location
    Manila
    Posts
    13
    Understood Micron. It is my inability to phase the question. At least I now get the 'Me' concept. Coding will be an adventure for another time. Thanks for the verification.

  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
    Glad you've got it working now...or have you?
    Suggest you read my comments in post 2 again
    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
    alexsmith91 is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2023
    Posts
    17
    Hi @isladogs,
    I have somewhat a same issue with respect to #name error. Can you help me what would be an ideal solution for this?

    I have a form which populates records. For each record, there is a button on the right which, on click would take the ID from the record and populate all the other columns into a 2nd form (frm_CPS_EDIT).
    But when I run the code, the new form that opens always gets the #name error.

    Below is my code.

    Code:
    strSQL = "SELECT P.CPS_REP_ID FROM table AS P WHERE P.CPS_REP_ID = '" & Me.CPS_REP_ID & "' ;"
             
    r.Open strSQL, DC.DATA, adOpenKeyset, adLockOptimistic
    
    
    
    DoCmd.OpenForm "frm_CPS_EDIT", , , "[CPS_REP_ID] = '" & Me.CPS_REP_ID & "'"

    Quote Originally Posted by isladogs View Post
    If the code is in the subform, just use the Me. prefix.

    Otherwise, you need the full Forms!mainformname.subformlname.form.[Product_ID]

    OR substitute with Me. For first part if in main form
    Or substitute first part with Parent. If in subform

    Personally, I'd use the first of these four options if code is in the subform

  14. #14
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Office 365
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    What datatype is CPS_REP_ID?
    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

  15. #15
    alexsmith91 is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2023
    Posts
    17
    nvarchar (4)

    Quote Originally Posted by isladogs View Post
    What datatype is CPS_REP_ID?

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

Similar Threads

  1. Replies: 3
    Last Post: 06-24-2017, 06:39 PM
  2. Replies: 7
    Last Post: 09-10-2014, 12:33 PM
  3. Replies: 6
    Last Post: 07-24-2014, 08:18 AM
  4. Replies: 8
    Last Post: 04-29-2013, 12:11 PM
  5. Replies: 2
    Last Post: 02-12-2013, 11:23 AM

Tags for this Thread

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