Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2008
    Posts
    58

    Unanswered: DBLookup can't return multiple values. Can I use RecordSet instead?

    Hello,

    I've just successfully used the DBLookup function as the control source for a text box on one of my forms. Each record from my "Projects" table has one or more corresponding records from my "Measures" table, and I simply want the Measures for each project to be displayed (read-only) on the form I use for Projects. Unfortunately, the two tables are not linked and cannot be linked because of the way the fields were designed (ah, the joy of taking over someone else's mess), hence my need for the DBLookup. That works, but most of my Projects have more than one Measure associated with them, and DBLookup cannot display more than one result. I was told I can use a RecordSet function to accomplish this, but I haven't been able to find any information on using it as a Control Source.

    So I have a few questions: Can RecordSet do what I need? If so, how do I code it? If not, what other method should I undertake?

    Any help would be appreciated. Thanks.

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Sounds to me like you are looking for a Measures subform to be placed on your Project mainform.

    Bit hard to say much without seeing the structure of the tables.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    yes you can use a recordset to retrieve data.. effectively behind the scenes Im pretty certain thats what a Dlookup does (but just retrieves the first record that matches).

    the problem is what do you do with that data, how do you display it, how do you make it meaningful to the user. I'd suspect 'slamming' it into a combo or list box is probably the answer

    a recordset is a source of data, suitable (bound) controls form values from a recordset are combo or list boxes. however you can do anythign you want with a recordset as the source.... for example populate a series of check boxes, text boxes whatever.. effectively the limit is your imagination and the applications needs

    if you do go down the route of a recordset, and you allow th euser to make changes you need to populate the relevant controls using the forms on current event, and apply any changes back to the db in the forms before update or after update event (after update may be safer as it means the updates elsewhere have been processed. if you do put your code int he after update event , remember to do any validations (if required) in the before update event

    HTH
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Aug 2008
    Posts
    58
    Thanks for the replies. We already have a Measures subform embedded. Our "Projects" form is tabbed and "Measures" is one of the tabs. I just want a list of the measures for each project to be displayed on the main tab. It's purely a visual thing. This text box will be locked.

    Essentially we have a query to combine information from the Projects table and the Measures table, with more or less the following fields:

    qryMeasureOverview

    ProjectNumber [tblProjects]
    ProjectName [tblMeasures]
    MeasureNumber [tblMeasures]
    MeasureDescription [tblMeasureNames]
    (several fields of measure-specific information)

    The DLookup expression I built looks as follows:

    Code:
    =DLookUp("[Measure Description]","qryMeasureOverview","[ProjectName]='" & [Forms]![frmProjects]![ProjectName] & "'")
    If any of this doesn't make sense, let me know and I'll try to explain it better.

  5. #5
    Join Date
    Aug 2008
    Posts
    58
    Ok, so I've acquired some code for a RecordSet.

    Code:
    dim db as database
    rs as recordset
    set db = currentdb()
    set rs = db.openrecordset ("SELECT [TheField] FROM [TheTable] WHERE
    TheWhereCondition")
    with rs
    while not .EOF
    debug.print ![the field]
    .MoveNext
    wend
    end with
    Now my question is, how do I make my text box display the results of this code?

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    dim db as database
    rs as recordset
    set db = currentdb()
    set rs = db.openrecordset ("SELECT [TheField] FROM [TheTable] WHERE
    TheWhereCondition")
    with rs
    while not .EOF
    mytextbox.text= mytextbox.text & ";" & ![the field]
    .MoveNext
    wend
    end with
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Aug 2008
    Posts
    58
    Quote Originally Posted by healdem
    dim db as database
    rs as recordset
    set db = currentdb()
    set rs = db.openrecordset ("SELECT [TheField] FROM [TheTable] WHERE
    TheWhereCondition")
    with rs
    while not .EOF
    mytextbox.text= mytextbox.text & ";" & ![the field]
    .MoveNext
    wend
    end with
    Thanks, healdem. What type of procedure should this be set as? Also, "dim db as database" is coming back with an error of "User-defined type not defined".

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    What type of procedure should this be set as?
    Not quite sure what you are asking, but I'd answer with "a normal sub". You'd put this somewhere like inside your OnCurrent event procedure for the form.

    Also, "dim db as database" is coming back with an error of "User-defined type not defined".
    This is a references problem. You'll need to go to a code window, go to Tools - References and get rid of any missing or broken ones... and make sure you have the normal libraries... which I can't remember the list of offhand
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  9. #9
    Join Date
    Aug 2008
    Posts
    58
    Quote Originally Posted by StarTrekker
    Not quite sure what you are asking, but I'd answer with "a normal sub". You'd put this somewhere like inside your OnCurrent event procedure for the form.


    This is a references problem. You'll need to go to a code window, go to Tools - References and get rid of any missing or broken ones... and make sure you have the normal libraries... which I can't remember the list of offhand
    Thanks StarTrekker. I'll see if that helps.

  10. #10
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You're welcome
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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