MS Access Forms drill down

Posted on 2006-04-11
Last Modified: 2006-11-18
Is it possible to display records in a Form - 1 record per line so that the user clicks on one of the records and an underlying screen pop ups with more detail. The user would need to be either navigate up or down the tabular detail with a command bar across the screen or directly click on the record to activate the subform. I have seen the Selector Add In which partially does this, but it does not allow for fields within the records to be formatted differently (different colours etc) which I also need. I also need the ability to have a command button displayed along each record down the page which would invoke more functionality for that particular record.
Question by:garryv
    LVL 61

    Expert Comment

    Use a form in continuous forms view.  You can set the default view to continuous forms by opening your form in design view, opening the croperties window, and setting the default view (under the format tab).   You can select different colors for the controls either through the controls property sheet.  You can also use conditional formatting (select a textbox or other control in design view and go to format -> conditional formatting).  This wil allow you to set text or background color if certain conditions are met.

    To open the other form, you can use a command button, like you said, or the double click event (or another event) of a text box,etc with the following VBA:


    Author Comment

    Thanks for this.

    Is there a way to make this more consistent with how this functionality works in other applications like having a highlighted command bar that is displayed right across all fields in the record. The user can up and down arrow the bar and either click or hit enter to open the underlying form.

    Further to this is changing the mouse pointer to indicate that a click is possible over the line. Just using the on click event doesn't present the user with this.
    LVL 44

    Accepted Solution

    Or indeed - it sounds like the Current event in the continuous form (or datasheet if you preferred) would be appropriate - responding to navigation or row clicking.

    Not entirely sure what you mean by "having a highlighted command bar that is displayed right across all fields in the record".
    It sounds like a selected record in a listbox.
    But then you'd lose all your formatting options (and a host of events).
    You could try simulating that - but it would have to be a datasheet rather than a continuous form and using a line of code like

    DoCmd.RunCommand acCmdSelectRecord

    (But then in a datasheet you'd lose your command button on each row... but a single command button outside of the form used as a subform would work)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now