Input in pivot like form

[boerbende]

Dear reader

I have a table like
DATE SOURCE MEASUREMENT
date1 sensor1 value
date1 sensor2 value
date2 sensor1 value
date2 sensor2 value
date3 sensor1 value
date3 sensor2 value
etc..

Has somebody a tip to create a pivot like input form with for example

SOURCE date1 date2 date3 ... date7
sensor1 value value value ... value
sensor2 value value value ... value

So that we can enter all the details for one week on one form
Thanks for thinking with me

Regards,

Ben

 

[JHB]

Instead of a long explanation, I've made an example for you in the attached database.
Open the only form in it, input some date in the "Headline", chose a sensor form the combobox and type in sensor values in the other controls, then click the "Save" button.
The result is stored in the table "tblMeasurement".

 

[boerbende]

Hello,

I see what you mean. Thank you very much
This is OK for new input, I have to think more because the user have to change input as well and it is a multi-user environment (I did not mention it, I know, I wanted to keep the example simple).
I think I first have to start easy with a simple datasheet form

Regards,

Ben

 

[stopher]

I did an example of a grid-like input here

Let me know if you need help implementing it.

hth

 

[boerbende]

Wow... that is a great piece of work.
You write there is an autosave, but the table sales is for example not modified when I change numbers in the grid (I work with Office 2010)
when I look inthe events of the textboxes, there is an event afterupdate, but when I click in this, Office starts a new blank one.

Further, this looks just as I need. Am I allowed to use it?

Many thanks

Ben

By the way, what a powerful Forum this is. Till now there is always someone with an answer, no matter how difficult I think my question is

 

[stopher]

You write there is an autosave, but the table sales is for example not modified when I change numbers in the grid (I work with Office 2010)
when I look inthe events of the textboxes, there is an event afterupdate, but when I click in this, Office starts a new blank one.

The table is updated on the AfterUpdate event. The afterUpdate code is in the class clsTxtBoxEventHandler. Don't create a new code tab as this will cause problems.

The afterUpdate event is done this way to avoid having to code every control - one class handles the code for all the textboxes.

I'll test in Access 2010 to see if there is a problem.

Further, this looks just as I need. Am I allowed to use it?

Sure, you can use it freely but you should reference back to the forum link. Also, be great to hear how you get on with it.

Note you can add more code to clsTxtBoxEventHandler. For example, you could highlight or colour individual cells depending on some criteria.

 

[stopher]

Seems to work fine in Access 2010.

However, please note that the underlying table only gets updated when the form closes so you won't see the table updates until after you close the form. In other words:

• the form opens and loads a copy of the table data into a clsData grid object (a class for storing our data virtually)
• Changes made in the form are stored in the clsData grid object
• When the form closes, the data from the clsData grid object is saved back to the table

The above method was a simple way to get the grid running. There are issues with this depending on your requirements.

There's the issue of the data being out of sync if the table is shared (or a server) and therefore has multiple users accessing it. If this is the case then some work needs to be done to lock records to ensure exclusive access to the data in the grid.

Another issue is that you could edit the data in the grid for 10 mins then maybe your pc crashes and the data is not saved back to the table. One option is to provide a "Save" button which runs the same code line as closing the form. The user would save as they saw fit. Another option is to add some code to clsData that saved the data after each "cell" update.

Another thing to be aware of is that if data does not exists in the table for a given cell, then I think entering data into the rogue cell and saving (closing) will probably cause the process to fall over because the code will try to update a record in the table that does not exist. My example was based on a complete set of data. If this is not the case then this needs to be rectified. One way would be to keep a record in clsData of which cells exist in the table and which are new.

hth
Chris