Add multiple records in a form with calendar type design.

Brass Contributor

Hello and thank you for anyone who can assist me with this problem. I'm trying get a form that brings up data in a calendar type design. To record data based on what date they input on. the following is the design of the form. 

AccessExample.jpg

Under each date you would input how many minutes. The table Structure base is ID, Name, TimeValue, TimeDate.

 

not sure how to get the TimeDate and display Multiple TimeValues on one Form.

 

Thanks again for any assistance.

 

11 Replies
Not quite sure what you are asking.
Is the form based on a crosstab query? If so, are you aware it will be read only.
By crosstab query you mean a query that pulls data from multiple Tables? If so then yes it is using a crosstab query and no I wasn't aware it would make it read only. If that's the case I'm not sure what the solution will be. From the picture above each square would be a numerical value (Minutes) the DateValue would be the date the square is associated to . if you follow the column down 1 would be august 1st 2 would be august 2nd ..... Do you see any solution?

No. A crosstab query changes a normalised Access table into a spreadsheet type layout.

See Make summary data easier to read by using a crosstab query - Access (microsoft.com)

 

A query based on several tables may be editable. It depends on the query SQL.

Please post the SQL for the form record source as I don't have enough info to give any advice.

To do this requires quite a bit of code, so step one is to be sure you are prepared for the work that will be required.

In a 20+ year career I only had to do something like this one time. It was a lot of work, but it can be done.

You need a temp table with fields for each of the days (1 through 31) in a month. The form is bound to that temp table.

You populate the temp table when the form opens by converting the values in the normalized table to those in the temp table. Then, when the user has entered whatever new data is needed, you have to run code to copy those values back into the source, normalized tables. A lot of work, but doable. If the interface design is critical enough to your business to justify that, you can do it. However, it's also worthwhile to look at a more conventional, less work-intense, interface.

What amount of effort are you willing to invest?
Wow I think this might be what i have to do. Im willing to put in the work. Do you have any examples of this cause other than creating the temp table I will need some assistance in how it is done. What do you mean by more conventional, less work interface. If something like this would get me the same out come I would definitely be interested in those. Thanks for your assistance with this.
I'll go into my archives and see what I can find that might be helpful.
I'm sorry, I no longer have the data for the app I was thinking of so it's impossible to set it up as a demo. I'll see if I can create one, but it'll be quite simplistic.

@George Hepworth 

I also created several similar forms a few years ago and can vouch for the fact that it was a lot of work. One example was for viewing/editing student attendance data and another for calendar events in a school:

 

CalendarMonth.gif

The monthly calendar could also be viewed as a weekly and daily calendar in which time slots were included

 

CalendarWeek.gif

 

In each case, just as George described earlier, I created a crosstab query based on a normalised table. Next I made a 'temp table from the crosstab so the data could be edited on the form. Likewise, on closing the form, all the data was saved back to the original table and the 'temp table' was emptied.

 

I do still have these examples but they are part of a commercial app for schools and it would be far too lengthy a process to extricate.

 

However Peter Hibbs at Utter Access forum has been developing an Outlook style calendar for Access for many years and it is still freely available. It should certainly be useful to you and it may well be that you can adapt it for your own needs: https://www.utteraccess.com/topics/1969978.

For info, it is a very lengthy thread with over 370 posts.

 

@isladogs 

Thanks for pointing to Peter Hibb's calendar. I'd forgotten it.

Nice work on the attendance and events calendars.

 

I'm working on a simplified demo, a bit at a time. It might be ready to share soon.

@George Hepworth 

My demo "works" as is, but I surfaced a serious flaw during testing. The approach I took works satisfactorily if there is ONE event per day. However, it can't adequately handle multiple events on a single day. I'm not sure I want to put in any more time on it, as a matter of fact.

 

If the demos and downloads previously cited don't meet your needs, I'd say a traditional main form/subform design is in order. 

 

If I feel more ambitious later, I might tackle this again, but right now not so much.

Wow that calendar form looks amazing. What I am trying to create is a attendance database for our therapist. I think that calendar form is pointing me in the correct direction. I will try to take from it and make mine work. I will keep you updated.