Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2009
    Posts
    9

    Unanswered: Copy text (value) from form, search table and display results in subform

    Ok, so hopefully the title is descriptive enough. And as a preface, many thanks again to user pbaldy for his invaluable help in creating cascading menus. Now on to a new issue.

    What I'm trying to do is take the text from a listbox in the main form that is populated, search a table for this value and display all relevant information in a subform. The way it is setup is thusly:

    Mainform contains boxes Cycle, Macroprocess, Process, Activity, Role, Role Code. Cycle, Macroprocess and Process are cascading menus that return results based on the previous selection. After the final selection (Process), Activity, Role, and Role Code are displayed in a listbox.

    Subform contains Role Code, Role (English) and Role Description.

    So what I'm trying to do (rather unsuccessfully) via macros is take the value displayed in Role Code, copy it, search a table that contains Role Code information and display it in a subform that will display relevant information for that Role Code.

    Any suggestions?

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Apparently I'm the only one foolish enough to tackle these!

    As I recall, the listbox displayed items related to the last combo selection? Are you saying you want the user to select something from the listbox and search on that, or search on all the items displayed in the listbox? If the former, single or multiple selections? Either way I'd set the subform recordsource:

    Me!SubformControlName.Form.RecordSource = "SELECT..."

    but the nature of that SQL would change based on your answer to the above.
    Paul

  3. #3
    Join Date
    Jan 2009
    Posts
    9
    you're a brave, kind soul sir

    and that's mostly what the issue is...you're correct in recalling that the listbox displayed data based on the last combo box selection. The displayed information was Activity, Role and Role Code.

    We have another form that contains Role Code information. I simply added an ondblClick event that when you double-click in that listbox, it will open the other form (frm Roles) that displays default Role Code information and opens the Find function to search for a Role Code.
    (As more background information, the frm Roles form simply displays relevant information for a Role Code)

    So what I'm trying to do is copy the value that is in the Role Code column of the listbox, query the table that contains the Role Code and when it finds a matching role code in that table to display all relevant information in the other form. So instead of currently where you have to remember the Role Code, type it in the search box and search that process will be done automatically simply by double-clicking on the lstActivityRole listbox

    I'll attach what I've got so far so you can view the wreckage of your rather elegant solution.
    Attached Files Attached Files

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Try this code instead of the macro:

    DoCmd.OpenForm "frm Roles", , , "[role code] = '" & Me.lstActivityRoleCode.Column(2) & "'"

    which should open the form displaying only the relevant record(s).
    Paul

  5. #5
    Join Date
    Jan 2009
    Posts
    9
    (Sigh)

    You're a modern-day Access Prometheus. Only minus the whole liver-being-eaten-by-giant eagle thing. I added the line in there and it works like a champion.

    Once again I can do nothing more than heap many, many thanks upon you....THANK YOU SO MUCH!!!!

    Many thanks,
    Michael

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    LOL! Glad it worked for you, Michael.
    Paul

  7. #7
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Spam reported
    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
  •