Results 1 to 11 of 11
  1. #1
    bnorton is offline Novice
    Windows 8 Access 2013
    Join Date
    May 2014
    Posts
    8

    An unusual(?) parent-child form

    Help me think this through. It's been a while since I have done any Access development



    I have a parent-child form that shows publishers (the parent) and all the associated publications (the child). The form and sub-form are joined with the PublisherID field. Right now we have pretty much the whole set of Publishers and the whole set of Publications, we just don't have many of them associated with each other, i.e. the [Publications].[PublisherID] field is null. I want the user to be able to add and delete the associated publications for each publisher from the sub-form. I DON'T want them to add and delete publication RECORDS from the sub-form. Essentially I just want them to be able to edit the PublisherID field of the Publications records listed in the sub form.

    I'm not finding a simple solution to this, which usually means that I'm approaching it all wrong.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Set the form AllowAdditions and AllowDeletions properties to No. This can be managed programmatically.
    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.

  3. #3
    bnorton is offline Novice
    Windows 8 Access 2013
    Join Date
    May 2014
    Posts
    8
    Thanks, June7, but that's not quite what I need. The fact that I'm having a hard time explaining this is further evidence that there may be something fundamentally flawed with the way I'm thinking about it.

    Let me try it this way.

    I have a Publishers table and a Publications table and a one-to-many relationship (Publishers can publish many Publications). Both the Publishers and the Publications tables are already mostly populated, but the only thing missing is the field that associates the two, i.e., [Publications].[PublisherID] is null for most records. What I was hoping for was a simple way to associate the two tables. I've seen things like this before where you have the parent record on the left and the list of available children (orphans???) on the right, and you can select multiple children with Ctrl+Click or Shift+Click.

    Does that help?

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Each publication can have only one publisher - correct?

    Is the publisher name in the Publications table?

    If there is no common field in both tables there is no way to relate them.

    Do data entry in the Publications table to populate a PublisherID field. Use a combobox that lists the publishers. For each Publication record select a publisher from combobox. You probably want a multi-column combobox http://www.datapigtechnologies.com/f...combobox3.html

    No VBA or macro coding required.
    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.

  5. #5
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You could have a combo box to select the Publisher and
    a multi-select list box to select the Publications where the foreign key field to Publishers is NULL.

    Then have VBA code to loop through the list box selections and update the PublisherID (FK) field in the table Publications with the PK from the Publishers table.

    Does this make sense??

  6. #6
    bnorton is offline Novice
    Windows 8 Access 2013
    Join Date
    May 2014
    Posts
    8
    Each publication can have only one publisher - correct? - Yes

    Is the publisher name in the Publications table? - No, only the PublisherID

    If there is no common field in both tables there is no way to relate them. - They both have PublisherID as the common field (see attachment).
    Click image for larger version. 

Name:	Publ-Pubr.jpg 
Views:	13 
Size:	109.2 KB 
ID:	17859
    Do data entry in the Publications table to populate a PublisherID field... Well, that's what it may come down to.

  7. #7
    bnorton is offline Novice
    Windows 8 Access 2013
    Join Date
    May 2014
    Posts
    8
    Thanks, ssanfu, that's the more elegant solution, but perhaps the more expeditious solution is to just make the user update the PublisherID directly from the Publications table, even if it's just a datasheet view.

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    They both have the field but if there is no data in both then same as not being there at all.

    Need data in both. This means data entry. Two options have been suggested, which do you want?
    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.

  9. #9
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    OK, I thought you wanted to do a one time update of the PublishersID field in the Publications table for the orphans.


    ...make the user update the PublisherID directly from the Publications table,....
    I never allow anyone to see tables, let alone edit directly in the tables - always use forms.
    But having a combo box for the selection of the PublisherID is a good idea for routine data entry.

  10. #10
    bnorton is offline Novice
    Windows 8 Access 2013
    Join Date
    May 2014
    Posts
    8
    Thanks for helping me think this through. I think we are all on the same page now, so perhaps I can off this a the final solution, at least as far as it goes.

    First the problem in generic terms:

    You have a two tables with a parent child relationship. The PK in the parent table is ParentID, and there is an FK in the child table called ParentID as well. Pretty basic stuff so far. You have populated the two tables via a spreadsheet or other flat file, and everything is fine except that all the children are orphaned, i.e., the ParentID field in the child table is null. So, how do you populate that last field.

    The elegant solution would be to have a form with the parent information on the left, and on the right a long list box that showed all the remaining orphaned children, although ideally it would also show all the children already linked to each parent. So the user could scroll through the parents, and for each one select the children that belonged to that parent. That would involve some code to loop through the list box and update the ParentID field in the child table.

    The easier solution, at least from the developer's point of view, would be to have the user just use some simple form based on the child table. This would be a simple matter of having a combo box of all parents on the form.

    I guess the main lesson for me, anyway, is that a basic form/sub-form approach is not appropriate for this. That approach works fine if you want to add and delete entire child records, but not if you are trying to associate existing records to the parent.

  11. #11
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You have populated the two tables via a spreadsheet
    Yes, if you import data to two tables and the relating field is NULL, you will have to enter the PK field manually or use code.

    The elegant solution would be to have a form with the parent information on the left,....
    That is why I suggested a combo box and a multi-select list box. Select the publisher, then select the related child records from the list box and press a button. The code would update the FK field. Or you could use two list boxes - a simple select list box and a multi-select list box.


    All of my primary key fields have the suffix "_PK" and the foreign keys have "_FK", just to avoid confusion (me and Access).


    would be to have the user just use some simple form based on the child table. This would be a simple matter of having a combo box of all parents on the form.
    Yes, you could have a form with a combo box bound to the "ParentID_FK" field and the row source a query that includes the "ParentID_PK" from the Publishers table. The user would/could select the publisher as they are entering the rest of the child record. I use this arrangement a lot. I use continuous forms..... but never datasheet view.

    Good luck with your project.

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

Similar Threads

  1. Replies: 7
    Last Post: 01-21-2014, 11:32 AM
  2. Replies: 3
    Last Post: 07-03-2013, 01:20 PM
  3. Passing value from parent to child form
    By Pragmatic in forum Forms
    Replies: 1
    Last Post: 12-02-2011, 07:23 PM
  4. Replies: 2
    Last Post: 09-16-2011, 06:11 PM
  5. Problem using Parent/Child Form/Subform
    By EvanRosenlieb in forum Forms
    Replies: 4
    Last Post: 06-27-2011, 05:25 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