Page 1 of 3 123 LastLast
Results 1 to 15 of 32
  1. #1
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    405
    Thanks
    35
    Thanked 5 Times in 5 Posts
    Please help* me get a full solution to this, not bits and pieces I really don't need.

    WHAT IS THE FOOLPROOF (AND QUICK) WAY OF CONSTRUCTING A BASIC FORM THAT CAN SHOW, ENTER, AND UPDATE INFORMATION FROM A JUNCTION TABLE?

    Here is a simplified problem that I confront frequently.

    I have tblNouns that contains two fields: nounID (the PK), and Nouns.

    I have tblVerbs that contains two fields: verbID (the PK), and Verbs.

    I have a junction (many-to-many) tblSentences that contains two fields: nounID and verbID (with both set as the PK).

    I can build frmNouns (based on a query or table, writing my own SQL or having Access do it for me) that will show the records in tblNouns, and allow me to edit them, or add and delete them. It does this in natural language, showing the Nouns field, not nounID.

    I can do the same for frmVerbs.

    I can include frmVerbs on frmNouns as a subform, and get the whole thing working.

    WHAT I CANNOT DO IS THE FOLLOWING:

    Build something - anything - that I can add to frmNouns that will 1) show only the records of tblSentences that correspond to the record from tblNouns that I am currently editing, 2) allow me to add new records, delete old records, and edit existing records of tblSentences from frmNouns, 3) do all this in natural language using Nouns and Verbs instead of nounID and verbID, and 4) be able to add new records to tblVerbs at about the same time that I am adding the record that will use them in tblSentences.

    * As near as I can figure, overcoming this obstacle is a huge problem for novice Access developers. There are posts in forums all over about this problem, and many of them seem to fade out because the poster can't make headway. I have a best-selling 1000+ page book in front of me that mentions this problem, but does not contain a solution on how to solve it. It isn't the only one I own. I know of an employed developer whose solution to this was to copy the Northwind database, delete everything from it, then replace it with his information: he just maintains that database now and hopes that no one asks him to do it again from scratch. And I have talked to multiple college Access instructors who ... haven't been able to get me where I need to be. There is a glass ceiling here.

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    I attach a demo.

    • My frmNouns displays the NounID, but disabled and off to the side. If you don't want to see it, you can set its visible property to No, but it still needs to be there.
    • The subform is just based on tblSentences, which has just two fields VerbID and NounID as joint key.
    • The subform has NounID hidden ...Visible=No
    • VerbID is displayed a combo box. The combo box draws its values from tblVerbs, and has two columns. The first column holds VerbID, but its column width is set to zero, so the VerbID is hidden and you only see the Verb as a word.
    [attachment=89277:sentences.zip]

    I appreciate you have chosen nouns, verbs and sentences as just an example, but it it does not seem a very good one to me.
    It would make more sense to me for example, to have Students, Subjects and Enrolments for example. Each student can enrol in a number of subjects, and each subject has a number of students enrolled. The enrolments form the junction table.
    Attached Files Attached Files
    Regards
    John



  3. #3
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    405
    Thanks
    35
    Thanked 5 Times in 5 Posts
    Thanks. I will take a look at this over the weekend.

    I am hopeful that this will work, but I have demos already. Rather, what I seem to be lacking is a recipe for doing this, or god forbid - a wizard. I will see if I can construct one from your demo.

    Then nouns and verbs was just a basic database I through together. The big problem I'm having is not that I can't do what I'm asking, it's that I can't do it consistently - and in fact, I was not able to get it working in this simple example. I suspect that there are conflicting options and code that I'm not seeing - but I am not seeing a pattern in the error messages I get either. Many times I just throw things out and start over because of junction table problems. I have a strong suspicion that there are a lot of people out there, like me, who get the idea of higher order normalization, but can't get it to work in practice.

    I am glad that you'll at least work with the nouns and verbs example. I've asked similar questions on other forums, and I tend to get responses back that criticize my database design, rather than help me figure out where I need to go. That is not much help when I didn't think up the design; so I made a point of dreaming up a simple one for this thread.

    BTW: I have searched through WSL, and Woody's before that, and I've seen your replies to other posts, and noticed that you like the student/class/enrollment example. That's similar to what I was working on this past week - I had person/year/degree/field and person/project/round junctions that I was working on. I will get back to those once I settle this problem once and for all.

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    I am happy to work with your example if you find it useful.
    I would not choose it, as I don't think it would be helpful as an example that tries to explain what junction tables are all about. A sentence, for example often contains multiple nouns, and can contain multiple verbs. A junction table sets up a many-to- many relationship, but itself has one-to-many relationships with the other two tables.

    In general the records in the subform are just drawn from the junction table, but a combo box is used to restrict choices (In this case of verbs) to values present in the verb table.

    A complication that sometimes arises is that you want to display, in the subform, other fields from the verbs table (or its equivalent) Let's worry about that complication later.
    Regards
    John



  5. #5
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    405
    Thanks
    35
    Thanked 5 Times in 5 Posts
    Yes. Let's save the complication.

    You are right about sentences. I was thinking along the lines of "Dick runs.", "Jane walks.", and "Spot runs."

  6. #6
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    405
    Thanks
    35
    Thanked 5 Times in 5 Posts
    Starting with simple questions, and trying to be very thorough.

    For frmNouns,

    1) Why does nounID "have to be there". I'm curious about this because the Wizard will give you the option of not including it. Do you include it if you're going to perform some action, but exclude it if the form is just being used as a convenient way to view data?

    2) Just checking: you switched the "Enabled" property of the nounID control to "no", right? Why would it make a difference if it isn't visible?.

  7. #7
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Quote Originally Posted by boobounder View Post
    1) Why does nounID "have to be there". I'm curious about this because the Wizard will give you the option of not including it. Do you include it if you're going to perform some action, but exclude it if the form is just being used as a convenient way to view data?
    I just removed it and it still worked. NounID provides the link between the main form (frmNouns) and the subform, via the Link Master and Child fields.
    The linking field does two things:
    1. It ensure that the correct sentences are displayed for each noun, and
    2. Fills in the NounID automatically when you create a new sentence.
    So the NounID needs to be available in the Record Source of the form..but it seems that it does not need to be on the form itself.
    I nearly always put it there, disabled, and off to the side and grayed out, so that if you if ever want to do something where knowing the number makes it easier, you know what it is.

    Also I just removed the hidden NounID from the subform, and that didn't make any difference either. Again it needs to present in the Record Source of the form.

    2) Just checking: you switched the "Enabled" property of the nounID control to "no", right? Why would it make a difference if it isn't visible?.
    You are right, and if it were invisible there is no need to make it disabled. Because it is an autonumber field, users cannot change what is there anyway, so there is no point letting them try.
    Regards
    John



  8. #8
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    405
    Thanks
    35
    Thanked 5 Times in 5 Posts
    Thanks.

    Now, in your initial second step "The subform is just based on tblSentences, which has just two fields VerbID and NounID as joint key":

    1) Do you build this first as a form, and then include it as a subform, or did you build it within frmNouns starting with the button on the ribbon?

    2) Did you use the Form Wizard?

    I'm trying to figure out if one of my issues is default settings in this step.

  9. #9
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Quote Originally Posted by boobounder View Post
    1) Do you build this first as a form, and then include it as a subform, or did you build it within frmNouns starting with the button on the ribbon?

    2) Did you use the Form Wizard?
    I think I used the 'subform wizard' - but I regularly use both methods. Neither is perfect so the choice is pretty arbitrary.

    The subform wizard creates the form as a datasheet, so I then do some work on it to make it the way I want:
    • Change to continuous
    • Hide the NounID and remove its Label.
    • Move the Verb to the left
    • Display the Verb as a Combo box
    • Remove the navigation buttons (they are nearly always confusing on subforms)
    If I were wanted to build it first as a separate form, I would use the Wizard, then make a similar set of modifications.
    Regards
    John



  10. #10
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    405
    Thanks
    35
    Thanked 5 Times in 5 Posts
    Great.

    I'm still working through this - and comparing to other problematic databases as I go.

    One problem I'm seeing in past failures is that when I inserted my subform, sometimes it seems to have set the master and child fields properties, and sometimes it doesn't. But, I can't go back and look at where this step got goofed up in putting those together.

    Is there a setting that might interfere with that? Or is this why having nounID in the parent frmNoun is important?

    Normally, I just choose the option for that about half-way through the setup windows of the subform/subreport control, and assume that it works.

  11. #11
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    405
    Thanks
    35
    Thanked 5 Times in 5 Posts
    OK. I'm running, and the only outstanding issues I have are in my last post.

    My subform will add a new record to the junction table if it is for a new record in tblNouns.

    But ... it will not add a new record to the junction table if that record from tblNouns already has an entry in the junction table.

  12. #12
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    405
    Thanks
    35
    Thanked 5 Times in 5 Posts
    I see now that your subform does that with the second and additional rows.

    Which setting controlled showing 2 rows initially, and bumping that up to a third row when the first row was entered?

  13. #13
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    405
    Thanks
    35
    Thanked 5 Times in 5 Posts
    That last point was badly phrased.

    Yes ... there's definitely something that you're doing when you make your subform continuous that I'm missing.

  14. #14
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    405
    Thanks
    35
    Thanked 5 Times in 5 Posts
    I went back and recreated my subform using the wizard, and it looked like it would be continuous. When I tried to enter a second record in tblSentences for the noun current in frmNoun, I got the error message in the attached JPEG.
    Attached Images Attached Images

  15. #15
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Your last post , with the error message, occurs if you try to add the same verb twice in the subform.
    The design of tblsentences with the joint key does not allow you to have two sentences with the same noun and verb.

    Added later: Two other possible explanations:
    • You have set the key of tblSentences to be just NounId (rather than the joint key). If you did that you cannot enter more than one sentence per noun.
    • The NounID is not being correcly entered into tblSentences. What do you see in the table? This depends on the Master/child fields being correct.
    end of additions.

    A continuous subform always shows a blank record at the bottom, below any existing records. So if there are no records present, you just see 1 row (blank), if there is 1 record you see 2 rows etc.

    To set the master/child properties after creating the form, look at the properties (i.e. the properties sheet) of the subform control. For this you click on the subform just once. Click again and you see the properties of the form within the subform control, and this is not what you want.

    [attachment=89301:mastchild.gif]

    I don't think I have addressed all your questions yet, but I have to go now.
    Attached Images Attached Images
    Regards
    John



Page 1 of 3 123 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •