Results 1 to 11 of 11
  1. #1
    scampbell is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Posts
    12

    Form control source standards

    I had originally planned a long explanation of my problem and all the issues connected, but I think I may be able to handle this by requesting a blanket statement on correct form design.

    I have a database with three tables, and a form for each of them, call them "Sections", "Issues", and "Questions". In that order, they form a one-many-many relationship with one another. I need to be able to show data from the first table on its form, as well as the "Issues" and "Questions" forms as read-only fields.

    Originally this was easy, in the first related form I just added controls for the required field using the Field List, where the Sections table was in the related tables. As you know this adds that table to the control source for the form.

    On the second table I couldn't take the same approach because the "Questions" table is not directly related to the "Sections", but linked through "Issues". The solution I came up with was to make two queries (one on the other) that would give me the info I needed, and linked the controls to that query.

    All this seemed to work at first, but I have been noticing other problems. The "Issues" form seems not to track the "Issues" table very well, it shows more records than the table does. If I set it up through the query, it tracks just fine. I also have combo boxes set up to filter off one of the controls, but when they are sourced from the query, they don't seem to be able to see the control.

    So the ultimate question is, what is the correct architecture for relationships like this? Are their problems sourcing a form from multiple tables? If not, is there a way to do the same thing on my third table? I tried to do it in the query box for the row source, but it wouldn't allow me to join three tables.

    Convoluted, I know. At this point my head is spinning because I thought I was one control away from getting the DB done, and now I'm questioning my whole design. Thanks to anyone still with me.

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    This may be useful. It will be a useful reference in future as well.
    You're looking at resolving Many to Many relationship.

    http://forums.aspfree.com/attachment...achmentid=4712

    Good luck.

  3. #3
    scampbell is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Posts
    12

    Thanks

    I'll definitely give it a read. With what little I know, this situation didn't seem like a typical many-many, where there can be multiple occurrences in either direction, but more like a one-many-many, or put another way, two one-many relationships chained together. I don't know if there is any functional difference or if the same principles apply.

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Perhaps you could give an actual example using some data representing the issue. Maybe others will have some comments or experience as well.

  5. #5
    scampbell is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Posts
    12

    Update

    Orange, I will try to put up a reduced version after work in case someone can help.

    In the meantime, I think I may have figured out why one of the options is not working. When using a query to populate my control, I have to use a listbox or combobox because the query returns multiple matches.

    In the listbox, I then apply a group and filter so there is only one line, but I am then trying to reference that value to filter another combo box. I assume that this is not working because although you only see one value, there could potential be many. Thoughts?

  6. #6
    scampbell is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Posts
    12

    Example

    Well I made a much smaller version to share as an example, but after trimming as much as I could and compacting, I still can only get it down to 632 kB, so it can't be linked on the site. Any suggestions?

  7. #7
    Rawb is offline Expert
    Windows XP Access 2000
    Join Date
    Dec 2009
    Location
    Somewhere
    Posts
    875
    Have you tried to compress it into a ZIP (or other) Archive?

    If not, then try that. In my experience Access Databases tent to compress VERY well, unless most of the data is images or attachments.

    If that size is just as a straight mdb/accdb then I'm guessing it'll compress down to about about 120k when zipped.

  8. #8
    scampbell is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Posts
    12
    Wow, I don't know why I didn't do that first, but it did zip extremely well. Thanks.

    This is a stripped down version of my database, basically showing just enough to highlight my architecture and problem. The controls for "Person Working On It" in both the issues and questions forms are the problem. It is straightforward to pull in the list of people, but the user of the DB wants to be able to filter the drop down by the assigned team for that section. I believe everything is set up as I have it currently.

  9. #9
    scampbell is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Posts
    12
    Also, another shortcoming of this design is that anytime a record is added to one of the tables the queries must be run again so that the forms use up-to-date info. I'm not sure how to automate this correctly, and I'm thinking there is a simpler way.

  10. #10
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Not everyone has Acc2007 or 2010, so can not read your accdb. You may get more responses if you include an mdb version.

  11. #11
    scampbell is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Posts
    12

    Problem Solved

    I was trying to filter by a value in a listbox/combobox. The problem was I was really using the listbox as a display, and so was not actively selecting anything. Added an On Current to the form to select the first (only) item in the list, now I can filter by that value.

    Thanks for the help.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 10-13-2011, 03:36 PM
  2. Replies: 2
    Last Post: 05-13-2011, 02:34 PM
  3. Replies: 2
    Last Post: 04-15-2011, 01:13 PM
  4. Control Source
    By sarah54 in forum Access
    Replies: 1
    Last Post: 03-07-2011, 09:00 PM
  5. one form two sources - invalid control source msg
    By techexpressinc in forum Forms
    Replies: 1
    Last Post: 05-28-2010, 02:11 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