Edit a pivoted query on a form (1 Viewer)

[Bob]

Hi,

Excel is fantastic for what I am trying to do with Access, but this is a fun challenge.

Creating a budget tool, where each row (continuous form) is an account (general ledger) and each column is all months of the year (Jan..Dec).
The budget table is normalised table of budget info

tblBudget(PLAccount, FiscalYear, FiscalMonth, Budget)

The pivot query is easy with columns returning monthly data and each row an account.

Challenge begins as this recordset is not updateable. I have tried adding unbound text fields to form (transferring the data to the unbound text boxes in the Current Event) and enabling them only for the current record. That works in so far as updating the data at the right time. The final hurdle seems to be that unbound fields will show it's value on all records (Continuous form). Perhaps conditional formatting can help, but I haven't cracked that one yet.

Any ideas? perhaps someone has experience with updating pivoted data on a form?

(Importing/Exporting from/to Excel is probably the easier solution but I'd like to crack this one.)

Thanks.

 

[plog]

1. Does it need to be like that when you enter data? Why not have a form that closer mimics your table (each row has it's own month and year) so that data just goes in and then use a report on the pivoted data when you need to present the data?

2. An unbound form and a lot of VBA. Essentially you will manually be doing what Access does for you when you use a bound form. You will have to write VBA to populate the form, to add data from the form, to update data from the form. Lots of coding.

3. Denormalized temporary table with with a process into your table(s). Set up your form like you want, then build a table to house that data--one field for each month. The form would be bound to the denormalized table and hold the input values. Then you have a 'Save' button on the form that moves the data from that table into tblBudget and clears out the temporary denormalized one. If you need to edit existing data you would need a reverse process as well.

Honestly, all 3 are hacks and Excel is probably the way to go. But if this is more a learning exercise those are the 3 ways to use Access for this.

 

[CJ_London]

In the past I have used an ado disconnected recordset for editing data in a continuous form. Parse your xtab recordset into the ado recordset and assign to the form recordset. Edited data can then be applied to the underlying tables, either on a change by change basis, on moving to another row or by a use of an ‘update’ button (useful for what if type scenarios

Ado recordsets use different filter and sort methods used by forms so if this is a requirement, you will need to write your own shortcut menus

 

[MajP]

Here is an example of "editable" form based on a cross tab.

I need the minimum value among 5 different fields in one record

Unfortunately, there's no way to show you ahead of time what the consequences are going to be. As I posted earlier, it's a matter of the number of future billable hours of work that will be required when Safeco goes out of business and you have to add a new field for the replacement company...

www.access-programmers.co.uk

I did a presentation on this with other methods to do this.

Access Europe User Group - Wed 2 July: Faking ‘Non-Normal’ User Interfaces with Normal Data (Pete Poppe)

The next Access Europe User Group meeting will be on Wednesday 2 July 2025 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). For local times, please check World Time Buddy In this session, we are very pleased to welcome back Pete Poppe (AKA MajP) who will cover several...

www.access-programmers.co.uk

 

[MadPiet]

I'd just do the whole thing in Excel. If you have a hierarchy of accounts in a table, then it works out. I've seen YouTube videos showing how to do it. Kinda depends on your goals though.

 

[Bob]

plog, CJ_London, MajP, MadPiet,

Thanks for your ideas. Impressed by the quality and speed of your replys.