Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    trolleri is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2013
    Posts
    43

    How do I open subforms with a certain criteria in the new Navigation Form

    DB-Structure:


    My database contains multiple questionnaires. Each questionnaire have a form dragged as subforms into seperate tabs of a Navigation Form called frmNavigation. Mind it is the new Navigation Form feature which first appeared in Access 2010, and not the old Tab Control. The reason why I don't use the normal Tab Control, is that it cannot automatically resize the tabs according to the different sizes of questionnaire-forms / subform. That is why I see the Navigation Form feature as a more cosmetic viable feature than the older tabbed panes, and so far it has been very simple to use. It is also possible to have two horizontal levels of tabs with the Navigation Form, which I have used to arrange my many subforms into categories (Visit 1, Visit 2, Visit 3, Visit 4).

    The default window shown is frmSearch, which contains a multiple search form, to search between subjects in a list box. When clicking on a record in the list box (SSN being the unique identifier between tables), this code runs:

    Code:
    Private Sub SearchResults_DblClick(Cancel As Integer)
     
        Dim stDocName As String
        Dim stLinkCriteria As String
     
        stDocName = "frmNavigation"
        stLinkCriteria = "[SSN]='" & Me.SearchResults & "'"
        DoCmd.OpenForm stDocName, , , stLinkCriteria
        DoCmd.Close acForm, "frmSearch"
     
    End Sub
    Problem:
    The above code works so far as to open frmNavigation, and show the correct record in some fields I have included in the header. But the different subforms in the NavigationSubform Control just defaults to the first record, and not the one handpicked in frmSearch. Maybe I can link the subforms to the locked, and read-only field "SSN" in the header of frmNavigation? Both the control NavigationSubform (shared control for all subforms) and the buttons/tabs for all the subforms, eg. navVisit1Crf1, have events called "On Enter", and all the panes/tabs have "On Click". They even have a data control called "Navigation Where Clause", but I am struggling to get any code working. And yes I am very much a beginner in the world of Access.

    I tried this code in the data control "Navigation Where Clause":

    Code:
    DoCmd.BrowseTo acBrowseToForm, "frmVisit1Crf1", "frmNavigation.NavigationSubform", "[SSN]='" & Me.SSN & "'"
    But it did not work. Do I also need a SSN-field (locked and invisible, like the one in the header of frmNavigation) on the subform frmVisit1Crf1?

    My database is too large (2 mb) for upload, but you can get it here
    Last edited by trolleri; 03-01-2013 at 10:59 AM. Reason: formatting

  2. #2
    burrina's Avatar
    burrina is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2012
    Location
    Freeport,Texas
    Posts
    1,383

    Relationships and Screens Shots

    I downloaded your db and man is it ever difficult to navigate and understand! Could you please explain exactly what form and subform is the problem! I cant help but think this could be so much simplier by using conventional mainform/subform (s). Guys, here is a screenshot of the relationships! 18 tables, 1 query and 1 main form and 11 subforms. Whew!
    Attached Thumbnails Attached Thumbnails ssnrelationships.jpg   mainform.jpg  

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    I personally don't like the Navigation form. Think it is intended for use in web databases. Subforms on a tab control is so much simpler.

    Up to 2mb zip file allowed. Be sure to run Compact & Repair before uploading any db.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    burrina's Avatar
    burrina is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2012
    Location
    Freeport,Texas
    Posts
    1,383

    This database loses me completely

    Here is the database! It is a nightmare.
    Attached Files Attached Files

  5. #5
    trolleri is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2013
    Posts
    43
    Splendid, this is going to be fun! Thx for all the replies so far :-D

    Quote Originally Posted by burrina View Post
    I downloaded your db and man is it ever difficult to navigate and understand! Could you please explain exactly what form and subform is the problem! I cant help but think this could be so much simplier by using conventional mainform/subform (s). Guys, here is a screenshot of the relationships! 18 tables, 1 query and 1 main form and 11 subforms. Whew!
    This is my first database, but I never said it was going to be small ;-) Most descriptions and labels are in Danish, so in extend to my above explanations of the structure:

    Tables and their names (not the problem):
    This database is for a medical clinical trial, where every patient have records of several questionnaires, and measurements measured by the doctor. That adds up to more than 900 unique fields for every of the 200 patients. I simply could not make one table including all the answers since there is a limit of 255 fields per table, so I split all questionnaires into separate tables. I tried to use conventional naming. All patients will have one record in tblSubjects each, with a primary and unique key called "SSN" (abbrevation for social security number). All tables are connected with a one-to-one relation of that SSN key. For the sake of the overview, I have then categorized the different questionnaires into at what specific visit at the doctor the patient did answer the questionnaire. Prefix tblVisit1 for the first visit, prefix tblVisit2 for the second etc. The suffix is an abbreviation of the type of questionnaire. Some questionnaire types is found more than once, but under different visits, e.g. tblVisit1Crf1, tblVisit2Crf2, tblVisit3Crf3, tblVisit4Crf4. These questionnaires have slightly different questions, and even when a question is excactly the same, one patient have unique answers to both questionnaires since it have been asked at different times (e.g. before and after treatment). Thus, I NEED all the tables ;-) Not all tables is complete since I have yet to discover what data from the different blood samples is relevant for the study. The forms for those tables have not yet been made, just like “tblEvents”, “tblInclusionExclusion”, and “tblEndOfStudy” have not. But the tables is not the problem here.

    Forms (this is where the problem starts):
    When done, all the tables will have a corresponding form. In this draft I simply began with the tables for the questionnaires. When possible, I have limited some controls to have a variety of predefined answers in combo boxes. As you can see, there are two extra forms: frmNavigation and frmSearch:

    frmSearch
    This form is a simple form with a searchstring, and a list box showing the results from tblSubjects. In extend, when you double-click the resulting record in the list box, frmNavigation opens on that record, as shown in the header of frmNavigation.

    frmNavigation (problem!)
    This form was originally an empty form, and then I added some non-editable fields from tblSubjects in the header to make the records identifiable. In the details section I added the Navigation Subform, and dragged all the questionnaire-forms into the respective tabs as subforms. I cannot get these subforms to show the correct record, as shown in the header. Look at my code described above. Should I use the command BrowseTo instead of OpenForm?

    Quote Originally Posted by June7 View Post
    I personally don't like the Navigation form. Think it is intended for use in web databases. Subforms on a tab control is so much simpler. Up to 2mb zip file allowed. Be sure to run Compact & Repair before uploading any db.
    My database is exactly 2.41 MB in size after Compact & Repair, and it is set to do an automatic compact on close. As explained above I find the Navigation Form to be more cosmetic as supposed to Tab Controls, especially when having sub-sub-subforms (Visit1->Visit1Crf1->Demografi etc.). All the scrollbars make it hard to navigate in the form and the Tab Control simply do not automatically resize the panes as the Navigation Form does. It is a specific request to have all subforms collected on one page, and this must be possible. As mentioned above, there are different data controls like the "Navigation Where Clause", and in the header of frmNavigation, I already got the correct SSN-value to show for the record, which I double-click in frmSearch.

    Try to run a search in frmSearch for the subject Mini Mouse, double-click the record in the list box, and press the buttons in the Navigation Form. You will notice the error, when all you get is the default record #1 with the value SD1 in the field with the label “Indtastet af” (translates into Entered By). If you look into the tables you will notice that the subject Mini Mouse have SSN=2222222222, and that all questionnaires have an associated record for SSN=2222222222, and that the value in field “Entered By” is SD2. When the Navigation Form show SD1 in “Indtastet af” it indicates that the subform opened the record Mickey Mouse (who have SSN=1111111111).

    Quote Originally Posted by burrina View Post
    Here is the database! It is a nightmare. (link)
    Oh it did not occur to me to zip it! thx for the tip :-)


    I hope it did clarify the structure of my draft for you

  6. #6
    burrina's Avatar
    burrina is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2012
    Location
    Freeport,Texas
    Posts
    1,383

    Anything Wrong Here?

    So as I see it, here is the problem code that is NOT working for you, is this correct?
    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmNavigation"
    stLinkCriteria = "[SSN]='" & Me.SearchResults & "'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    DoCmd.Close acForm, "frmSearch"
    Attached Thumbnails Attached Thumbnails MiniMouse.jpg  
    Last edited by burrina; 03-01-2013 at 02:44 PM. Reason: Screenshot

  7. #7
    trolleri is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2013
    Posts
    43
    I think that specific code is working, since the header in frmNavigation grabs the correct SSN. But the control "NavigationSubform" does not grab the SSN and pass it to the subforms - something is missing in frmNavigation...
    Last edited by trolleri; 03-02-2013 at 08:12 AM.

  8. #8
    burrina's Avatar
    burrina is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2012
    Location
    Freeport,Texas
    Posts
    1,383
    Where exactly does this SSN need to go in what subform? What field? Your forms are in German or something!
    Please be more specific, remember, this may make sense to you but not me, it is not my database.

  9. #9
    trolleri is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2013
    Posts
    43
    ... and also, if I change frmNavigation to frmVisit1Crf1 in the code you just did paste, that form opens directly, and at the correct record (Mini Mouse with the value SD2 in front of "Indtastet af"). This too is an argument that it is somewhere in the Navigation Form there needs to be a correction. The Navigation Form simply do not parse the SSN the its subforms.

    SSN is only shown for the user, to identify the patients. At this point it should not be possible to edit that field in any forms - that comes later when I create a "create new record for a new patient"-form.

    The value of SSN (searched for and found in frmSearch) needs to be cross referenced with the records in the subforms in frmNavigation, so only the one record with that specific SSN is shown - this goes for all subforms.

    Maybe the value of the SSN control in the header of frmNavigation is going to be handy, since it already show the correct SSN, and that part works. I just need the Navigation Form to parse the SSN to the subforms, and then only show that matching record.
    Last edited by trolleri; 03-02-2013 at 08:16 AM.

  10. #10
    burrina's Avatar
    burrina is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2012
    Location
    Freeport,Texas
    Posts
    1,383
    The problem as I see it is this. You need to refer specifically to the control in your search form,i.e. SSN and not just SearchResults.Try making this adjustment and see if it works for you.
    stLinkCriteria = "[SSN]='" & Me.SearchResults.Column(0) & "'"
    Last edited by burrina; 03-01-2013 at 04:18 PM. Reason: Code

  11. #11
    trolleri is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2013
    Posts
    43
    That correction of code did not get it done. Maybe we should try something like this? He does not give any code examples though, and I am not sure about my own coding skills.

  12. #12
    burrina's Avatar
    burrina is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2012
    Location
    Freeport,Texas
    Posts
    1,383
    I don't know why you don't search from your main form anyway, seems like this would be easier and as you said, the code works this way.Just use a combobox and let it filter your search! I am not understanding you clearly enough maybe! You tried that code and it did not work? Maybe we should try something like what? Who is he? Me? I just don't understand you. Maybe someone else can give you better direction here. Or have you noticed that no one else has offered? Good Luck with your project!

  13. #13
    trolleri is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2013
    Posts
    43
    Now I am confused, and also sad because you must have misunderstood me. I am searching in my main form frmSearch. And I am double-clicking the list box wishing it would open frmNavigation on that specific record. But the subforms do not display the correct record, so that last bit does not work. What works are the header of frmNavigation. E.g. the record with SSN=2222222222 have EnteredBy=SD2, and not SD1. The header works, but the subforms do not, as shown in this picture:
    Click image for larger version. 

Name:	draft27.png 
Views:	17 
Size:	30.0 KB 
ID:	11379


    When I wrote "Maybe we should try something like this?", I was refering to the link in "this". In that thread, someone is explaining his solution, but it is not specific enough for me to understand it. Remember I am new to this, and I certainly hope I have not offended you by asking for help.
    Last edited by trolleri; 03-02-2013 at 09:23 AM. Reason: formatting of link

  14. #14
    burrina's Avatar
    burrina is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2012
    Location
    Freeport,Texas
    Posts
    1,383
    Is this form , subform linked by SSN to the main form? I can't seem to be able to see the master/child relationships of your forms? The design of the subforms is strange to me.Very confusing! Not in conventional design! Please answer the above question if you can!

  15. #15
    burrina's Avatar
    burrina is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2012
    Location
    Freeport,Texas
    Posts
    1,383
    I am able to create a new main form and subform and a new search form and use them and Link the subform to the main form with master/child relationships as SSN and it works! Your problem is that YOUR main form frmNavigation is NOT linked to the subform frmVisit1Crf1 by SSN Fix this and your problem will be solved. I have tried doing this with your main form but can't get to edit the relationships somehow???

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 12-25-2012, 12:43 AM
  2. On Double Click Open Form with two criteria
    By AndreasPanayiotou in forum Programming
    Replies: 3
    Last Post: 09-10-2012, 08:47 AM
  3. Open 2nd Form with 2 criteria?
    By Robeen in forum Forms
    Replies: 1
    Last Post: 09-19-2011, 10:20 AM
  4. Open (sub)form linking 2 criteria on current form
    By websterh in forum Programming
    Replies: 2
    Last Post: 02-07-2011, 11:56 PM
  5. Open form based on Subform criteria
    By Suzan in forum Programming
    Replies: 0
    Last Post: 04-25-2006, 02:28 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums