Clinical trial with 900 unique questions for 200 unique patients

[Rod]

Still have no time to answer you fully but ...

I could not make one-one relations for this field, since it isn't an autonumber in tblPatients, ...

No you are under a slight misconception here; one-to-one relationships have nothing to do with whether one or both sides are autonumbers, rather you should specify that both sides of the relationship are indexed with 'unique values' = true, Access will then create a one-to-one, and only a one-to-one.



One-to-one relationships are a signal to data analysts that perhaps the data from both sides of the relationship should be merged. However this may not be the best physical solution. As an example consider a market research survey where 20,000 people are interviewed but only 250 people proceed to take a second interview. It is wasteful (but not necessarily impractical) to cater for the second interview responses in one entity/table so the two data sets could be split with a one-to-one linking them. Similar situations occur frequently in data analysis, e.g. job applicants - employees.

I anticipate that end-users will never have to see the values of the PatientID field, since it is created and handled by the database.

Agreed. Why should your users have yet another number to remember/manipulate. This is 'situation normal!'

Maybe I should delete the field CPR for each table other than tblPatients, and only use the field PatientID as the identifier between the tables.

This is the usual practice. If you want to find the CPR for this data then link back to the entity/table that contains it; do not duplicate CPR values across your db.

OK, the rest of your questions are too complex to answer in this quick and dirty reply. Hang in there, I shall get back to you.

[Rod]

I seem to be answering your post in tranches; here's the next one - but before proceeding let me stress that the example db I gave you is very crude and lacks validation and error handling. I excluded these to make the structure more obvious.

I like the way you made a dropdown box for switching between records, how did you do that?

I shall assume you know how to set up a combo box dropdown list. I just made sure there is a non null value in the combo box as the form is opened...

Private Sub Form_Load()
    Me.cboPatientID = Me.PatientID
End Sub

... and then I set the combo box's 'Limit To List' to Yes, thus ensuring that it always contains a valid patient id. The bound column is column 1, the patient id. The bound column determines which of a selection of values is the value of the combo box. The bound column may be hidden but in this case, is not.

Now for the 'trick.' Access will synchronise a main/parent form with a subform provided that the main form is a single form. This is done through the Link Master/Child Fields of the subform control. (Terminology is a pain here since we now have at least three terms for the controlling form - Master, Main, Parent - and at least two for the following form - Child, Sub.) Anyway, although it is possible to code your own synchronisation, why reinvent the wheel? So I have mounted the combo box on a single form bound to the patients' table which will act as the Master form. Because I do not want the users to navigate inside the patients' table except via the combo box, I have removed all the form's usual navigation. I have included a bound textbox for patient id (that I would probably hide in the final implementation as I don't want users messing with it and it merely repeats the data shown in the combo box).

Because I've removed the form's navigation I need to substitute navigation based on the combo box value. For this I use the After Update event of the combo box.

Private Sub cboPatientID_AfterUpdate()
    Dim rstClone As DAO.Recordset
    
    Set rstClone = Me.RecordsetClone
    rstClone.FindFirst "PatientID = " & Me.cboPatientID
    Me.Bookmark = rstClone.Bookmark
    Set rstClone = Nothing
    Select Case Me.tabQuestionnaires.Value
    Case 0
        Me.sfrQuestionnaire01.SetFocus
    Case 1
        Me.sfrQuestionnaire02.SetFocus
    End Select
End Sub

There are in fact two following subforms, one for each questionnaire, placed on separate pages of a tab control.

There's actually quite a lot to explain and I fear I've only done a partial job. I recommend you study my setup and code. Try searching the help for anything you don't understand and if you are still confused then get back to me.

I have been looking at your sample design, and it looks like you use modules. These look complex.

They're not really - well, they're as complex as you want to make them.

[trolleri]

Again thank you very much for helping me!


I have completed most of the seperate forms, and then tried to put them together as panes according to the structure described in post #14. I however found this approach to be way too confusing, with three levels being mainform->subform->sub-subform. If you see frmAll you might agree ;-)
Link: dropbox.com/s/pq00765agrejnnh/22%20-%20frmAll%20and%20cboPatientID.accdb


I then searched the web for navigation, and found that from Access 2010 and later, MS created something called "Navigation Forms". This is my new route, since it can have horizontal tabs with two levels of rows! I understand however that you cannot help me further this path. FYI it is free to try Office 2013 for 60 days (up to 180 days if you perform the legal rearm-feature), look here:
technet.microsoft.com/en-us/evalcenter/jj192782.aspx


Even though I decided to use the navigation form, I still need the subforms to sync with fields in the header. That is why I continued working on the form frmVisit1, which only have two levels in the parent-child chain being:
frmVisit1->frmVisit1Crf1
frmVisit1->frmVisit1Holter1
frmVisit1->frmVisit1Qol1


This is no navigation form, so I should be able to implement your code. But when I try to implement your code into a combobox in frmVisit1, I get this error:
"You entered an expression that has an invalid reference to the RecordsetClone property"


See cboPatientID in the top of frmVisit1 for reference of you code.


Searched the web and got this:
bytes.com/topic/access/answers/799580-help-error


It seems that this error can occur if code from older versions of Access is used on newer ones?


Hope you are not too busy

[Rod]

Interesting about the Navigation Form. It could of course be simulated in Access v2007 (or earlier) but without the fancy cosmetics. Microsoft are moving toward what I call a 'paint by numbers' facility for Access with each successive release. No development or enhancement has been invested in VBA for some years now and I suspect that MS want to shed it completely. Instead there is an increasing number of ill-documented 'functions' that do exactly and only what is claimed (no flexibility) and do not necessarily work in all situations. As an example, I have yet to get the 'Import From Outlook' to work. Even if I had, the complexity of Outlook is such that what exactly is this function going to import? I'm not an Outlook power user but even I have four or five contact lists/address books. Microsoft's attitude is why many of the Access experts have remained with earlier versions; the later version offer us nothing new in the way of flexibility.

OK, that's the gripe. Multi-level subforms should cause you no problem. The earliest versions of Access limited to (I think) three levels; nowadays it's more. Actually I can't yet see why you even need three levels; maybe that will be apparent when I look at your latest db.

Concerning the error. Yes, sometimes upgrading a db to a newer version causes problems. Instead just copy the code, module, whatever into the later version.

I'll look at your db when I have more time.

[Rod]

Had a chance to look a little more deeply at your db. I'm surprised that I can see as much of it as I can since it is v2010 and I'm using v2007. Anyway the error message about the clone has nothing to do with versions. Rather it is because there is no recordset from which to formulate a clone. Here's the property sheet for frmVisit1.



There is no record source, therefore there is no recordset, with no recordset it is impossible to create a recordsetclone.

There are other things waiting to bite you. I called my PK PatientID not knowing your names. You have no such name; you must use your own names.

Why place the combo box on frmVisit1 rather than frmAll?

---

As a general overall observation, your design, at first glance, seems to break many of the design rules and best practices. But then I see what you've done and can guess why you've done it. I just wish to point out that your db is for this survey and this survey only, little or nothing is reuseable. If you accept that then otherwise your GUI looks clean and smart. You seem to understand what you're doing which is more than half the battle won.

Get back to me if you think I may help further.

[trolleri]

I'm surprised that I can see as much of it as I can since it is v2010 and I'm using v2007.

I thought the format .accdb was a shared format for '07 and later. But it might be that I am using some new forms that aren't in Access 2007.

The reason why I added the combobox on frmVisit1, was that I found the single level of subforms inside the tab contol considerable more clean than having yet another level of tabs with subforms within the tabs holding the parent subform - illustrated by the confusing layers in frmAll. I simply found it too confusing with the many sliders. Maybe you have another idea of how to circumvent this confusion? The problem with confusion when using more than one level of panes was the reason why I started to try out the new Navigation Subform feature. For this, I already asked and got some help in another thread. You will see my attemp if you look at my latest draft, and open frmSearch, then use it to open frmNavigation with the correct header.


You however gave me lots of great tips, and I will also this to fall back on, does the Navigation Subform prove to be of no use (still struggling).

As a general overall observation, your design, at first glance, seems to break many of the design rules and best practices. But then I see what you've done and can guess why you've done it.

Do you recommend any readings about the design rules and best practices? As mentioned before, I am interested in the best practices when creating a database.

[Rod]

Here are a couple of links - not necessarily the best. There's plenty out there on the web; try searching for database design and/or normalization.

http://databases.about.com/od/specif...normalform.htm

http://www.rogersaccesslibrary.com/T...lationship.zip

[trolleri]

Thanks a ton for you help and guidance!

For your curiousity, you, and others watching this thread, can follow my continued quest with the Navigation Form, by clicking this link.