Results 1 to 13 of 13
  1. #1
    Join Date
    Sep 2005
    Posts
    29

    Unanswered: Control source for an unbound textbox

    I have a form that uses a query as its control source. The problem I have is that on this form I have an unbound textbox that I need to show the result of another query in. I thought I could do this by using =[Query]![QueryName]![Field name] in its control source, but this isn't working.

    What is the right way to go about this?

    Cheers

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Neil_Pattison
    I have a form that uses a query as its control source. The problem I have is that on this form I have an unbound textbox that I need to show the result of another query in. I thought I could do this by using =[Query]![QueryName]![Field name] in its control source, but this isn't working.

    What is the right way to go about this?

    Cheers
    Hi Neil

    Check DLookup in help for low down and dirty solution
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Sep 2005
    Posts
    29
    Thanks for getting back quickly Pootle. I tried using

    =Dlookup("[Field Name]","[Query Name]")

    but it is just bringing up an error. Is this the right kind of format?

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Indeed. The Domain aggs are really like SQL Statements without the Clause key words.

    What's the error?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Sep 2005
    Posts
    29
    It justs shows as #Error

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    That means no data is returned. Open a new query and convert your domain aggregate function call into SQL and see what happens:

    Code:
    =Dlookup("[Field Name]","[Query Name]")
    Becomes
    Code:
    SELECT [Field Name] FROM [Query Name]
    Run the query and see what you get.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Textboxes can't store entire recordsets. You need to use a combo/listbox if that's your goal.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by pootle flump
    That means no data is returned.
    ...or the return is null.
    Code:
     =NZ(Dlookup("[Field Name]","[Query Name]"), "")
    However Teddy is spot on - if this query is intended to return 2+ records then you don't want a textbox.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Sep 2005
    Posts
    29
    That is still bringing up the error, but the query is bringing the correct result so it shouldn't show as Null

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    What is the query result (all of them) and the data type?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Sep 2005
    Posts
    29
    I've decided to change the way I'm doing this.

    What I've done now is use a subform to display the results of the query and would now like to reference this in a calculation using an unbound textbox on the main form. is this possible and how would I do it?

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    An alternative to using the abomination that is the domain functions is to open a recordset as part of the forms on open event, (remembering to close it in the forms on close event to avoid memory leakages).

    use the appropriate expressions if you are using ADO or DAO to retrieve the value you want from your query as the datasource to the recordset.

    you will need to consider in what places you think you will need to refresh the data - ie what should cause the unbound control to change. An obvious candidate would be the 'on current' event - which is triggered when a new record is shown. but you may also need to trigger the function when other values are entered into your form - depends on your requirement.

    HTH

  13. #13
    Join Date
    Sep 2005
    Posts
    29
    Thanks. i've sorted this now

Posting Permissions

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