Results 1 to 10 of 10
  1. #1
    baulrich is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    23

    Label based on Query result - returns #Name?

    I have a label on form that I'd like to populate with the result of the query. That query is defined so that it uses the value of another label on the form as criteria. It seems that I've used this method before and it's worked.

    The query is based on a Linked SQL table (didn't think that should matter). I nosed around enough to see that the problem could be with field names, so I tried using field name aliases in the query and then referencing those in the label.

    Any ideas what is wrong? Values in use are listed below.

    thanks....


    Label Control Source = "=[qry_frm_job_edit_job_lookup]![Field1]" (Field1 is the alias I'm testing with)



    qry_frm_job_edit_job_lookup = "SELECT TCM_JCJob.JobStructureRID AS Field1, TCM_JCJob.JobStructure1 AS Field2, TCM_JCJob.Name AS Field3
    FROM TCM_JCJob
    WHERE (((TCM_JCJob.JobStructureRID)=[Forms]![frm_job_Edit]![Text_TCM_jobstructureRID]));"

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I assume you mean a textbox. You can't get the value that way unless the form is bound to the query. If not, look at DLookup().
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    Paul, as well as myself, are assuming that you're actually talking about Textboxes, rather than Labels, because Labels have neither Values nor ControlSources. Also, it's not possible that this method has ever worked for you. As Paul said, you'd have to use the DLookup() function for this kind of thing.

    We really need to have the Label/Textbox thing clarified, before getting further into this thing for you.

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    baulrich is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    23
    Thanks guys. I actually am using a label, not a textbox. Just because the info being displayed in the control is intended to not be edited by the user. But I had already tried using a textbox instead and get the exact same error. If it's possible to make the textbox work that's fine, I'll just change the visual properties of it to grey it out. For what it's worth, when I create a label, it does have a "ControlSource" just like the textbox.

    I tried the DLookup() function but can't get it to work.

    It seems I'm basically accomplishing the same thing successfully on another form, but the results of the query are displayed in a list box rather than a label or text box. My next step was going to be trying to create a listbox with only 1 row and see if I could display the desired field from the query.

    I'll attach a screenshot of the form, as it may help to visualize what I'm trying to do.

    thanks....
    Attached Thumbnails Attached Thumbnails access screenshot.jpg  

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Is this appropriate to the situation?

    http://www.baldyweb.com/Autofill.htm
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    baulrich is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    23
    Thanks... I think I'm getting close to figuring this out. What you suggested... using hidden fields from a combobox column based on a query is exactly what I need to do... In fact I'm doing that very thing on the version of this form that allows users to create a new record. This is the "edit" version of that form and in this case the "linked job" ID field in the record has (optionally) already been populated. This means I have the ID that I need to look up the job info from the other table (on which this form is NOT based). Right now, that combobox is based on a query that shows the users ALL of the records in the remote table. And it needs to be that way in case they want to use the combobox to change the linked job ID.

    So, the thing I don't know how to do is... leave that combobox so it shows all the records as it does now, but have it's default value (on form load) be based on the existing linked job ID, which may already exist. I know how to use the values from controls on the form as filter criteria for the query on which the combox is based... but if I do that in this case, the user will only see the one entry that matches the linked job that's already assigned.

    Thinking out loud... I guess I could create a second, hidden combobox based on a query that did use control's value as criteria. And the original combobox would allow the user to change that ID. Surely there's a better way than that.

    thanks....

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Since when does Label control have a ControlSource? Labels have a Caption property on the Format tab. Under the Data tab I see only the SmartTags property.
    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.

  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
    If the combo is bound, it should already come up with the selected value. The row source can still be all values.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    baulrich is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    23
    Thanks pbaldy!! That combobox was not bound and that was the problem. I was making that WAY harder than it needed to be!!

  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
    Happy to help!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Replies: 1
    Last Post: 06-08-2012, 11:14 AM
  2. combo box query based on different query result?
    By mejia.j88 in forum Queries
    Replies: 10
    Last Post: 02-15-2012, 02:00 PM
  3. Query result based upon two field conditions
    By diane802 in forum Access
    Replies: 35
    Last Post: 01-08-2010, 06:31 PM
  4. Replies: 3
    Last Post: 11-02-2009, 04:33 AM
  5. Query returns null..based on two tables
    By shsh_shah in forum Queries
    Replies: 1
    Last Post: 03-08-2009, 01:45 PM

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