None of my form fields are editable...why?

[logan3975]

I've attached my database to this post in case I don't explain it perfectly. I haven't built a database in many years.

I have a volunteers form that uses qry_volunteer_info.
There are six history tables that each have it's own tab on the form.


I created a topvalue and current query for each history table so when a volunteer is selected from the drop down, it populates the form with the most current record.

This all seems to work fine.

I have come to realize that none of my form fields are editable. I've researched this and found nothing. Did I set up my queries wrong?

Volunteers.zip

[pbaldy]

Yes:

http://allenbrowne.com/ser-61.html

I would expect the main form bound to the volunteers table and subforms bound to the related tables.

[logan3975]

If I set it up that way and lets say the exercise history table has four records for person A, how would the form know to display the most recent?
That's why I had to do topvalue queries for each of those tables. Is there another way to do this?

[June7]

Yes, the query that form is based on is issue.

Cannot use a single form to enter/edit data into multiple tables. Use form/subform arrangement.

You have one to many relationships set up. Each volunteer can have multiple history records in each history table? Why would there be more than one smoking history record for each volunteer? I think the way these fields are set up, the Volunteers and History-x tables could all be one big table. Normalization would be more like:

tblSmokingHistory
VolunteerInfoID
Product (cigarette, cigar, pipe)
DateStarted
DateStopped (or a Yes/No field StillUsing)

tblHistoryHealth
VolunteerInfoID
SymptomID
DateExperienced

Advise no spaces in object and field names.

Record birthdate and calculate age.

[logan3975]

Yes, a volunteer could have multiple smoking history records.

Scenerio:
Volunteer #1 comes in, fills out this form today.
Volunteer #1 comes in six months from now, reviews this form and makes any changes to the history tabs. If his exercise history has changed from 6 months ago, we want that to be a separate record - historical data.
Volunteer #1 comes in two months later, we pull up his data in the form, we want it to show all his history, but his most current history. So in this scenario all the information on the form would have a date of today, whereas the exercise tab would have a date of six months from now.

Does that make better sense?

[June7]

Okay, yes. And the normalization I describe still applies. Add another field for EntryDate.

Regarding the health history example. You can have a field for each and every possible symptom (Yes/No fields) but searching multiple Yes/No fields that are really same type (symptoms) of data can be frustrating. Also, if you want to add symptoms then have to add another field and modify queries, forms, reports. And if you ever get to more than 255 symptoms the table won't be big enough.

Why would you want form to show only the most recent record? Have form sort by descending date and the most recent will be the first record but all records are available for viewing.

[logan3975]

I'm so confused since I've been out of the Access world for a while.

The reason I only want the most recent record to display is so the volunteer has the most recent data to review and modify if needed. The historical data will only be used for running reports, etc. I haven't finished building out the other component to this but there will be a volunteer associated with a test ID. So if they volunteered for test A last year, we want to see what there exercise habits were, their smoking habits, etc, were at the time of that test.

So if I create my form using subforms, would the tables and queries work okay the way I have them setup? Or do I need to redesign everything? I just don't see how keeping historical data can work if all the information is in one table. With it divided up like this, if the only change is to their exercise habits, then a new record would only be added to the exercise table, without having to duplicate all their personal info along with everything else. Right?

[Dal Jeanis]

On the main form, you can do a Dlookup to determine the latest date, then include that value in the other query to display the most recent, without causing it to lose update capability. The other thing you could do is just sort the order of the other query descending, so that the most recent would show up first, and ignore the fact that others existed. Just a thought.

[June7]

I suggested one table only if the related tables will have only one record for each volunteer. Since you say there will be multiple records then the parent/child table structure is appropriate. You just haven't taken data normalization as far as it could be. It is a balancing act between normalization and ease of data entry/output. Perhaps this is far enough for your needs. Just be aware of possible issues if it isn't. I described one in my previous post.

[logan3975]

I've googled and researched and still am so confused. I also tried looking for a sample database to see how it was setup and had no success. I might just have to make this a messy database for now with duplicated information until I can get something better up and running. I'm in new territory with the historical data and the form showing the most recent (using Max, etc). I thought this database had pretty good normalization so I'm not sure how to improve that. Oy.

[June7]

Unless you specify exactly what it is about the given suggestions you don't understand, we can't advise further.

Use form/subform for data entry - main form bound to Volunteers, subforms bound to the history tables, each subform on a page of the tab control. Get this taken care of then we can work on the display of only the latest history records.

The history tables are not fully normalized. For instance, History-Family allows only one relationship for each medical condition. What if mom, dad, and brother all had heart trouble? Only one can be documented. And there can be a lot of blank cells in the history tables.

[logan3975]

I reworked my form query and I'm able to edit all the fields now. Thanks for everyone's help!