Proper Database Design??

[jondavidf]

Thanks again for all the help. I'll have to wait to see the difference between overlapping windows and tabbed windows. I could just google it, I guess. The scrolling list is fine with me. The key thing for entry is having it set up by day. I do know how to make the date field autopopulate with today's date. I think that will be important so that data doesn't get mistakenly put into the database with the wrong info.



Thank you for taking your time with this. I do like to learn also. I have actually taken a database concepts and an Access class but Form Creation and more advanced features are still a little out of my reach. I have also taken a VB class, so I do understand some of that code, as well. I think it's actually easier to use than the macros Access offers.

I am definitely not ready for a split implementation yet, as I will be the one doing maintenance on the database when needed.

The variable level is an attribute of the attendee. This will be implemented at an assisted living facility so they like to describe how active an attendee is at their activities.

I am also attaching a database I started to build. I like everything except the fact that it's a little clunky and it would be next to impossible for the average user to add or remove activities from the database. You can look at it to see where I was trying to go with this one.

ResidentAttendance - Copy.zip

[jondavidf]

I just googled overlapping windows. I actually prefer that also. What I'll probably ending up doing is creating buttons on the forms so that if a user wants to move from, say, the Activity Form (where they can enter activity levels) to the Resident Form (where they can add or delete residents) they click the button and it will close the current form and open the new one. That will just be polish though. We've still got a little ways to go before we get to that point.

[Rod]

Db received.

If you want to change to overlapping windows, do the following.

1. Open the database.
2. Click on the Office Button (large button in top left-hand corner).
3. Click on 'Access Options' button at the bottom of the pop-up.
4. Select 'Current Database' in the left-hand pane.
5. Click on the 'Overlapping Windows' circular check mark.
6. Click 'OK.'
7. Close and reopen the database.

[Rod]

I do know how to make the date field autopopulate with today's date

This gives you the system date

= Date()

This gives you the system date and time

= Now()

I have actually taken a database concepts and an Access class

It shows!

The variable level is an attribute of the attendee. This will be implemented at an assisted living facility so they like to describe how active an attendee is at their activities.

I have assumed so and also assumed the level is known at the time of assigning candidates to the activity.




Please tell me if the level is entered after the assignation. (Form still under development of course.)

I'll take a peek at the other db asap.

[Rod]

Jon,

In case you're developing in parallel, I have found it expedient to further normalise your db. Here is the schematic of the new tables and relationships.

[jondavidf]

The levels of activity stand for Active, Passive, Semi-Active, Refused, and Unable to attend. They will not know the level of activity until during the activity unless they Refuse or are Unable to attend.

[jondavidf]

The new design makes since to me, although I'm not sure what the purpose of EventID is. I'm sure I'll see shortly.

[Rod]

A couple of attachments for your consideration.

MSAFJon.zip

Resident Activities.zip

[jondavidf]

Looks pretty amazing from the few minutes I have had to look at it so far. I'll look a little more in depth tomorrow when I have a little more time. Again fantastic job!

[jondavidf]

I have taken a little longer to look at the database in more detail. The first thing I have to say is "Wow!". There is a lot more going on in this database than realized at first glance. The VBA code is a little over my head at the current time, but with a little studying I'll eventually understand it all. I'll comment first on the switchboard since that is the opening form.

1 - Perfect design. It's simple and easy to navigate. Adding code to open other forms and closing the database will be straightforward.
2 - The one thing I would like to change is to create a button from the switchboard that will create a "New Event", doing essentially the same thing as clicking the "Add New" button on the Event Form. I tried doing that myself by adding a command button that will open the events form. I then copied and pasted the VB code from the "Add New" button below the DoCmd.OpenForm code on the "New Event" code but I am getting an error message. If you'd like me to post specifics I can.
3 - I would then rename the "Event" button to "Previous Events" (although a new event could be added from here if desired).

Now for the Events form:

1 - Again, the design is simple and easy to navigate. This is a definite plus considering the lack of Access knowledge from the database users.
2 - There are only two things I would like to change on the Events form. The first is to have the activities sort in alphabetic order from the Activity Name drop down. I tried doing that by sorting the activity tables alphabetically, but it did not effect the order of the drop down.
3 - The second thing that I would like to change is on the subform to have the activities sort by date, descending. Again, I am not sure exactly where to do this from. I tried modifying the form with no luck.

I am very impressed with the db so far. The couple things I mention above are minor, but would make the usability of the forms even better.

I'll look at it more again later.

[Rod]

All feasible. Get back to you asap with instructions not ready-made solutions!

[jondavidf]

Sounds great. I tinkered with the New Event button on the switchboard form. I got it to open the Event form and highlight the Add New button, but couldn't figure out how to select the button or to call the btdAdd_Click() event.

[Rod]

Let's take your requirements one by one. The first is to additionally have a switchboard button to open the event form in add mode.

First, as you have already done, is to include an additional button on the switchboard. Name it what you want and set the caption to be something meaningful to your users. In my case I set the caption to '&New Event.' The ampersand means that the button may now be clicked by depressing [Alt]+N. While in design mode for the form you may wish to click on 'Arrange' in the ribbon and then click on 'Tab Order.' The order is set by dragging and dropping.

OK, now you need some code behind this new button. Just one line if you take out all the error handling. It uses the open arguments to pass a text string of "New Event" to frmEvent.



Now you need to trap the condition as frmEvent is loaded. So go to the VBA code for frmEvent and find the Form_Load procedure.



You need to alter the code to look like the above. If this is a new record then trigger the add button; if it's not, then call up the 'browse' mode synchronisation.

Let me know when this is working for you.

[jondavidf]

Got it. The "New Event" button is working like a charm now. That's interesting that you use the If statement to call the new record. I never thought of that. I assumed there had to be a command to "Run" the "Add New" command from the "New Event" code. It is much simpler the way you have done it.

After looking at it again it makes perfect sense to me what is happening in the code. I'm curious how you knew to do that, but I guess you have quite a bit of experience with this.

Not to get ahead of myself, but I messed around with trying to create some reports today. My initial assumption is that we'll need to set up a query of which the report will be based on. I know we still need to set up the alphabetizing and the reverse chronological ordering of a couple fields, but I am at a loss as to where to start with that so I started trying to create reports today. I think I'll wait a little while for some help with that before I continue on with that.

[Rod]

1 - Again, the design is simple and easy to navigate. This is a definite plus considering the lack of Access knowledge from the database users.
2 - There are only two things I would like to change on the Events form. The first is to have the activities sort in alphabetic order from the Activity Name drop down. I tried doing that by sorting the activity tables alphabetically, but it did not effect the order of the drop down.
3 - The second thing that I would like to change is on the subform to have the activities sort by date, descending. Again, I am not sure exactly where to do this from. I tried modifying the form with no luck.

Always there are more ways than skinning a cat to achieve something. The solutions here are kinda Access solutions rather than VBA. Once again I remind you to use your names where applicable rather than mine.

1. Open frmEvent in design mode.
2. Click on the field ActivityID to select it.
3. Make sure the property sheet is showing.
4. Select the data tab.
5. In the row called row source type, "SELECT ActivityID, ActivityName FROM tblActivity ORDER BY ActivityName;" (note: do not type the quotation marks; see also comment following picture).
6. Close the form to save the changes.
7. Test.

Regarding all property fields in the property sheet: you can open a larger window by typing [Shift]+F2.

1. Open qryEventList in design view.
2. Ensure that 'Descending' is selected in the sort line for ActivityDate.
3. Close and test.

I thought I had already set this. BTW, [Shift]+F2 works for the design grid cells as well.

Ready for that report?