So imagine I have data in a MS Access table that looks like this:

Is there any way that I can show the data in a datasheet form (which needs to be editable) which would look like this?

I guess I could always create an unbound form and populate it from the table when it loads with some VBA and then use more VBA to write changes back to the table whenever anyone updates the form, but I'm kind of hoping there is a simpler way of doing it.

Is there?

Thanks in advance,

Adam

CodePudding user response：

I guess I could always create an unbound form and populate it from the table when it loads with some VBA and then use more VBA to write changes back to the table whenever anyone updates the form, but I'm kind of hoping there is a simpler way of doing it.

Nope.

Pivots are not editable, and you can't have unbound multiline datasheet forms either.

I've got a project where upon opening the form I create a temporary table through VBA, which the user can edit and upon changing I also update the actual data, and upon close ( periodically to account for crashes) the table is removed again. It's a pain, but I'm fairly certain it's the only reliable way (you can bind a form to an editable unbound ADO recordset in theory, I never got it to work reliable enough though).

CodePudding user response：

A pivot table is essentially like a spreadsheet layout. You can generate such a pivot table using TRANSFORM ... PIVOT as outlined in another answer.
The easiest way to render this pivot table into a form you can edit is to use the "Analyze it with Excel" choice under Office Links. This will write the pivot table out to an excel spreadsheet, where you can edit it with excel.

This requires you to use Excel, and to invoke it form a menu button. This may not be available to you or may not be automatic enough. I don't know how to invoke Analyze it with Excel from VBA.