Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2013
    Posts
    3

    Unanswered: Add records to linked tables while seeing them all

    Hi
    I'm new to Access2007. I created 5 tables all related to each other in series with one-to-many joins (type 2 and all enforcements on).
    So an Id in table 1 can show up several times in table 2, and the same is true from tables 2 to 3, 3 to 4 and 4 to 5.
    I want to enter new records in such way that I can see all tables at once. I tried to do that with a query, but I can only modify records, not add new ones.
    I have no problem adding records to individual tables but have not figured out how to do it to this all-tables query. Can somebody help?
    Thanks
    Joe

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    You really need to use Forms for this, with Table1 being the RecordSource for the Main Form, then Table2 for the Subform, then Table3 for the Subform of the Subform, and so on, cascading down the line.

    With this many Subforms, most of us would use a Tabbed Control and place each Subform on a separate Page of the Control.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Jan 2013
    Posts
    3
    Thanks, I tried that and it works, ut to be fair, in my case it is not better than using the tables directly for entry since I can click of the little + plus signs to open the appropriate records in the daughter related table. But that's in my case, I can see that there could be cases where the forms and subforms solution may work better. Thanks again. Joe

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    A MainForm/Subform scenario will always work better! For experienced Access developers, one of the cardinal rules is that you never allow users access to the Tables! All data entry should be done through Forms.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #5
    Join Date
    Jan 2013
    Posts
    3
    Yes I agree and this is actually one of the reasons why I wanted learn access that is, security, but, as a beginner I have not figured out yet how forms provide that security. So far the forms that I made are just like the table in that I can do just as much damage with the form if I'm not careful as with working directly with the table. But from your reply I'm thinking that there must be ways to make entry forms more restrictive such as "you can modify some records but not others". I'm not there yet, but thanks so much. Joe

  6. #6
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Entering data directly into a Table means that if corruption rears its ugly head, it's going to be in the Table and you run a very good chance of losing data! While a Table can become corrupted, when entering data through a Form, the chances are greatly reduced; it's much more likely that the Form will suffer corruption, while the data will be safely stored in the Table.

    As for the "you can modify some records but not others" and its corollary, "you can modify some Fields but not others," I believe that Allen Browne covers this in great detail here:

    Microsoft Access tips: Field-level Permissions in Microsoft Access

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

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