Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Jan 2003
    Location
    Manchester, Lancashire, England
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Continous Forms Problem (Access XP)

    I store client details in one table and payment details in another linked by a unique client ID. The payments table has three fields CLIENTID, YEAR and PAYMENT; there is only one payment per year. How can I display the data in a continuous form format showing fields CLIENTID, PAYMENT for 1999, PAYMENT for 2000 and a field awaiting my input that will store the payment for 2001?. Next year, of course, the form should show 2000 and 2001 payments with the input field pointing to a YEAR of 2002. I can

  2. #2
    Star Lounger
    Join Date
    Sep 2001
    Location
    Pleasanton, California, USA
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Continous Forms Problem (Access XP)

    Sounds to me like you will have to settle for continous main form with all the details showing. Maybe a datasheet would be better for this. Just bring everything together with a query and sort by client and year. Then everything for each client will be together in order. You could use a popup form to enter new payment info for a client and then requery the main form to show the new entry (you can even go right back to the records you were looking at).

  3. #3
    Star Lounger
    Join Date
    Jan 2003
    Location
    Manchester, Lancashire, England
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Continous Forms Problem (Access XP)

    Tim
    Thanks for your comments - depressing though they were! I can't believe it's impossible. I live in hope.

    Ian

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Continous Forms Problem (Access XP)

    You might consider using the TreeView control that is in the Common Controls to display just the client name, or ID number or something like that. It gives you the ability to click on a name and display the details about that name very quickly. Then you could use a subform to display the payment details for any years that are of interest.

    I should hasten to add that it is a complex Active-X control, and unless you are pretty comfortable with VBA, you may not want to take that tack. It also gets to be cumbersome and slow to open if you have several thousand clients to display.

    Another possibility would be to use a combo box for record navigation - i.e. find the client by typing in the ID number in the combo, letting the user verify they have the right person, and then displaying payments in a subform. This can also get slow if you have tens of thousands of clients, but doesn't involve much in the way of VBA as the wizard will do it for you.

    A third possibility would be to use an unbound text box to type in the client ID and then do a filter on the client table to display the correct client and display the payment info in a subform. This will work with almost any number of clients you would want to consider in Access. It will however require you to write a bit of VBA to apply a filter to the recordset behind the client form.

    The only other possibility would be a continuous form with name clientID, payment99, payment00, payment01 and scroll up and down to find the client. That works with a few clients, but if you have several hundred it can also be a challenge. It would also require a good bit of VBA to display payment info on a single line and then be able to update 2001 data - right join queries in general are not updateable when you try to add a record. If you are looking for the path of least resistance, I would try the second option to start with. Good luck!
    Wendell

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Continous Forms Problem (Access XP)

    It's possible to display the information using a hierarchical flexgrid control, but the SQL to populate it is extremely tricky and you won't be able to directly edit the records from the flexgrid. I've built one application in Access 2000 using it, and there have been articles on it in Access-VB-SQL Advisor. Take a look at MS knowledgebase article <A target="_blank" HREF=http://support.microsoft.com/support/kb/articles/q196/0/29.asp>HOWTO: Create Hierarchical Recordsets Programmatically [Q196029]</A> if you're interested.
    Charlotte

  6. #6
    Star Lounger
    Join Date
    Jan 2003
    Location
    Manchester, Lancashire, England
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Continous Forms Problem (Access XP)

    Thanks Wendell. I've used the unbound combo box as part of the solution.

    Ian

  7. #7
    Star Lounger
    Join Date
    Jan 2003
    Location
    Manchester, Lancashire, England
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Continous Forms Problem (Access XP)

    Thanks Charlotte. You certainly know how to frighten a man!!

    Ian

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Continous Forms Problem (Access XP)

    Any little thing I can do to be of help ... <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Charlotte

Posting Permissions

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