Continous Forms Problem (Access XP)

Topic

New Reply

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

Reply | Quote

Replies

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).

Reply | Quote

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

Ian

Reply | Quote

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 HOWTO: Create Hierarchical Recordsets Programmatically [Q196029] if you’re interested.

Reply | Quote

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

Ian

Reply | Quote

Any little thing I can do to be of help …

Reply | Quote

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!

Reply | Quote

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

Ian

Reply | Quote