Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    markjkubicki's Avatar
    markjkubicki is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jul 2010
    Location
    Fire Island Pines, NY
    Posts
    496

    entries on subform tab control would "create duplicate values in the index..." at new records

    On one of the pages of a tab control on a single record data entry form, I needed (for user clarity) to place additional tabs (essentially: "tabs on tab" ...which you can't do...) UNLESS, you place on that page a sub-form, with a tab control on it. Which is what I did. The record source is the same for everything; same table, same record.

    Here's the problem I'm having when I create a new record:
    Any data I enter anywhere on that second tab results in error "would create duplicate values in the index, primary key..." if I try to save the record / move from it ... UNLESS I've started the record, not entered anything in the second tab , navigate away from that record, and then return to the record; now I can enter on that tab, move around the form, whatever. (On the main form or tab control is OK, and does not create ant conflict.)

    Also to know: when the record is first created, focus is set to a text control (on the main form), and unless something is entered and validated, the user can't move away from it, and once that happens, there's code behind it: me.dirty = false.

    fully perplexed, and welcoming in advance... any suggestions at all,


    mark

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    i dont see a need for tabs in tabs.
    the error sounds like the parent and child forms are not linked together.
    The subform properties should have them set to the KEY field in both:
    LINK MASTER FIELDS: = ID
    LINK CHILD FIELDS: = ID

    this prevents duplicate records. IF you have this, then the table may be key wrong.
    The TAB control on the parent form usu allows you to change the subform to another subform.(some inbed lots of subform onto the tab control. this can eat up memory, but they can still work)

    What is your current form structure?

  3. #3
    markjkubicki's Avatar
    markjkubicki is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jul 2010
    Location
    Fire Island Pines, NY
    Posts
    496
    The tabs in tabs are an effort to create clarity and organization on an already crowded form (see attached -although on this screen shot it does not look "that" busy). I could have simply added more tabs to the parent, but intuitively... that's not how the users "think"... (say no more)
    When I had tried to simply put a tab control onto the page of a tab control, I couldn't get it to work, and was subsequently told it is not possible, and that the work-around was to put on that page a sub-form (with transparent borders and background), and a tab control on it... which is what I have.

    The parent and child are linked together; and navigating from record to record (record next / record previous / find record...) does keep both in sync. They are linked on the master child fields of the subform.

    "IF you have this, then the **table may be key wrong**" <- not certain what this means

    The tab control of the parent form does have a few subforms, but only 1 per page (total of (3) sub on the tab control; (6) subforms in total on the parent.

    What I did just notice is that the the parent form has a recordset type: dynaset (inconsistent updates), whereas the subform type is: dynaset (not certain if this matters at all, nor can I recall any reason why the parent would be "inconsistent", and i have not played with it yet to find out...)

    Screenshot 2017-11-28 09.03.07.pdf

  4. #4
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    from the diagram, is that is subform IN a subform?
    when I said 'keyed wrong' , does the table key have either an actual key, or INDEXED - no duplicates
    this can give the error preventing you from entering data
    (because its already there and NO DUPICATE setting stops you)

  5. #5
    markjkubicki's Avatar
    markjkubicki is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jul 2010
    Location
    Fire Island Pines, NY
    Posts
    496
    the subform is on a page of a tab control on the main form; the subform is the container that the child tab lives on
    the field in the data source tables is a primary key, which is
    - validation - not null
    - required - yes
    - allow zero length - yes (this should be 'no'; however code behind the related control on the form is catching it, so ... no worries)
    - INDEXED - YES (no duplicates) (I could change this if it will help, and have any duplicates caught in code; however, the table / primary field is used in multiple one-to-many relationships, and of consequence is required (?))

  6. #6
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    It would help if you could attach a copy of your database with some sample data.

  7. #7
    markjkubicki's Avatar
    markjkubicki is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jul 2010
    Location
    Fire Island Pines, NY
    Posts
    496
    will do, but make take me a day or 2 to strip a program sufficiently...

  8. #8
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    I may have found a solution for you. You were getting the errors because the subforms were attempting to add new records with a duplicate ID.

    So, prevent them from doing that by setting the Allow Additions property of the subforms to No.

    However, you want to have each of your subforms to have a record to display (the one created in the main form), so what you have to do is give the subforms the record to edit using a requery.

    In the same block of code where you save the main form data with me.dirty = false, requery all the subforms with me![subform_container_name].form.requery,

    where [subform_container_name] is the name of the control containing the subform, not the name of the subform itself.

    I did a quick test and it seems to be working for me.

  9. #9
    markjkubicki's Avatar
    markjkubicki is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jul 2010
    Location
    Fire Island Pines, NY
    Posts
    496
    FANTASTIC !
    it worked exactly (Thnx)

    small point, (and in the bigger scheme of things: it's fine exactly the way it is...)
    if that subform is in view when a record is first created, until that required field is filled (the one with the code me.dirty = false), the subform is blank (no text boxes, no fields... nothing)
    again... I'm OK with that, BUT is there a work-around to keep them visible (somebody... is gong to get confused and complain)

  10. #10
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    the subform is blank (no text boxes, no fields... nothing)
    again... I'm OK with that, BUT is there a work-around to keep them visible (somebody... is gong to get confused and complain)
    I don't think there is a workaround to make them visible. A form/subform that doesn't allow additions will look like that if there are no records to display, which in your case doesn't happen until the record is saved in the main form.

    What you could do is keep all the subforms hidden (.visible = False) until the record is saved, then you could unhide them (.visible = True) in the same code block where you do the requery. I haven't tried this, but I think you can apply the .visible = True/false to the tabs instead of the subform containers, and by doing that your users won't be able to click a tab and see "nothing". In other words, keep then out of the subform tabs until the record is saved.

  11. #11
    markjkubicki's Avatar
    markjkubicki is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jul 2010
    Location
    Fire Island Pines, NY
    Posts
    496
    Quote Originally Posted by John_G View Post
    I don't think there is a workaround to make them visible. A form/subform that doesn't allow additions will look like that if there are no records to display, which in your case doesn't happen until the record is saved in the main form.

    What you could do is keep all the subforms hidden (.visible = False) until the record is saved, then you could unhide them (.visible = True) in the same code block where you do the requery. I haven't tried this, but I think you can apply the .visible = True/false to the tabs instead of the subform containers, and by doing that your users won't be able to click a tab and see "nothing". In other words, keep then out of the subform tabs until the record is saved.
    I like!
    Thanks !!

  12. #12
    markjkubicki's Avatar
    markjkubicki is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jul 2010
    Location
    Fire Island Pines, NY
    Posts
    496
    AURGH!!!
    but we both missed a different problem of consequence:

    If I edit any of the fields on the parent form (not necessarily the first time, but definitely within the first few edits and all thereafter), when I navigate to the next record, a write conflict occurs (although I can edit on the sub form and navigate).
    If I close the popup dialogue [X], all is OK, but if I Drop Changes, the record gets deleted - sometimes, at other time it leaves it as is, but Save Changes clears the record of all data. (I'm still searching for the pattern).

    Well... this can certainly cause a mess.
    I'm thinking of trying "ignore errors" as a patch, but that's one that I never like to use! any thoughts?

  13. #13
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    It's because the sub-form and the main form are both trying to update the same record. Ideally what you need to be able to do is prevent users from doing anything in the main form while a subform is open, but I'm not sure you can do that easily with subforms. Have you considered replacing the tabbed subforms with command buttons to open popup forms for the additional data? Popup forms can be opened as "modal", meaning users are confined to working in that form until they close it.

    You can some additional thoughts on this here:

    https://answers.microsoft.com/en-us/...552f4ff?auth=1

    and

    https://bytes.com/topic/access/answe...when-has-focus

  14. #14
    markjkubicki's Avatar
    markjkubicki is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jul 2010
    Location
    Fire Island Pines, NY
    Posts
    496
    what do you think about this ?

    I create new tab control on the main form (not on a subform of the main form)
    In layout, it is on top of the original tab control and sized to "fit onto one of its sheets. the new tab control remains hidden.
    When sheet of the tab control that would have had the subform (with the new tab control) becomes visible, so does the new tab -giving the appearance that it is on that sheet of the original control, when it is actually 'on top of' the control itself.
    Then when the user changes to a different sheet, the new tab control is again hidden

  15. #15
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Maybe you can cread a "blend" of the two concepts, tabs and popup forms.

    On your main form, replace the tabs with command buttons to open popup (modal) forms. Then, on the popup forms you could use tabs to look at the various subgroups of controls.

    So, going back to your screen cap in post # 3, the "Lamps, Power and Control" tab would be replaced with a command button that had the same caption, but instead of opening a tab page it would open a form that looks almost the same as the original tab page. This popup form could then have a tab control on it with the same three tabs/pages (Source, Power, Control) as it does now. If you make the popup forms modal, users would have to close one popup form before opening another, or before doing anything on the main form.

    You will probably need a little VBA code to synchronize the popup forms with the main form, but it is doable.

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

Similar Threads

  1. Replies: 3
    Last Post: 09-21-2017, 05:40 AM
  2. Replies: 4
    Last Post: 12-20-2015, 12:16 PM
  3. Replies: 5
    Last Post: 05-28-2015, 03:27 PM
  4. Replies: 11
    Last Post: 05-09-2014, 12:00 PM
  5. Replies: 1
    Last Post: 12-20-2013, 05:14 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