Results 1 to 12 of 12
  1. #1
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I have a form (Single Form view) with several subforms (Datasheet view). In the Switchboard I open this form in three different modes (Add, Read only, and Edit) with three different buttons set up using the options in the Database Utilities, Switchboard Manager. For the Read Only mode I run a macro that I created as a Macro object with the Action "OpenForm" and the Data Mode as "Read Only" (I hope I described that clearly.)
    Initially I thought this was working just fine. The add button opens the form with everything blank and the user can't navigate to other records. The Read Only button opens the form with all the fields displayed, navigation works, but the user can't change anything or add any rows. The Edit button allows everything, or so it seemed.
    Here's the problem.
    If I open the form using the Read Only button, close the form and then open the form using the Edit button, the last subform does not allow me to add new rows.
    In Read Only mode the subforms do not have the last line that appears in Edit mode; i.e. they do not have the row with the asterisk used to add a new record. When I check the attributes of the subforms (actually the forms properties of the subforms) after this happens these three attributes have been changed to "no": Allow Edits, Allow Deletions, and Allow Additions but only for the last subform. I suspect they are being changed to "no" for all the subforms and only the last one isn't getting changed back but I'm not sure.
    If I manually set them all to "yes" then open the form with the Edit button the last row of the last subform is fine (i.e. it has the asterisk).
    Any idea what's going on here and how to fix this?
    Thanks for any info you can give me.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    What happens if you place a command button cmdClose on the main form with the following On Click event procedure:

    Code:
    Private Sub cmdClose_Click()
      DoCmd.Close acForm, Me.Name, acSaveNo
    End Sub
    (or the equivalent macro)

    Does the problem persist if you close the form using this button instead of by clicking the x in the upper right corner?

  3. #3
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Sorry for my slow response, I was unavoidably detained.
    I tried your suggestion and the problem persists. I used your code exactly; was that correct? Me.Name returns the name of the form, right? I think that proves my theory was wrong!
    I tried looking at the subforms both directly, and from within the big form to make sure I had them set up correctly. Then I tested again and got the same results. After I manually reset the attributes I can use the switchboard to go to the form in Edit mode numerous times in a row with no problem. As soon as I go to the form in Read Only mode once, from that time on, when I go to the form in Edit mode the attributes for the last subform are set to "no". Very strange.
    I'm going to try to interrogate the value of the attributes in immediate mode using the debugger. Maybe that will tell us something.
    Thanks

  4. #4
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Wait! The no save caused the change I made to the code not to be saved! I'm re-testing.

  5. #5
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Retested - the problem persists with the close nosave code.
    My debugging shows that when I open the form in Read Only mode the attributes are set to no when control returns from the open macro object (I can't figure out how to get the debugger to walk through the macro object code) and they remain that way even in the Form_Close code. As soon as I open it in Edit mode the attributes are reset to "yes" for all the other subforms but remain "no" for the last subform.
    How can I change the sequence of the subforms? I want to test if it has to do with the number of subforms or the fact that this is the last one. I didn't see a "me.subforms" collection so I don't know how to check how Access sees the order.
    Maybe I'll just tack another subform onto the end of the form and see what happens there.
    Thanks

  6. #6
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I added another table related one to many to my contract table and added another subform to my main form for this new table. I added some test data.
    The problem persists with the same subform as before, not the new subform. So there must be something different about that one subform. It does not appear to be due to the fact that it was the last subform on the main form.
    Thanks

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I'd try recreating the problem subform.

  8. #8
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Great minds think alike! (Not that I'm in your league!) I'm working on recreating it as we speak. The difference with this subform is that unlike the others that have a table as the record source, this subform has a query as the record source. There is no other difference that I can see in the properties of the form. I'll keep you posted if I find out anything new. Right now, I suspect the query may be causing the problem but I have no idea why.
    The obvious workaround is to reset the attributes in code, maybe in the Form_Close routine but I'd like to figure out what is causing the problem in case it is causing something else I'm just not aware of yet.
    Thanks for the suggestion, keep 'em coming!

  9. #9
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts
    It has something to do with the query. I have been able to recreate it. I'm creating a scaled down version of the db which I will post over the weekend. I guess this is another lesson to keep it simple. I get into trouble when I do something complicated.
    Thanks for your help and patience.

  10. #10
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I finally created a working version that I am posting here. I had to learn about compact and repair so I could get the size down. I first tried to create a new db with the same problem but I wasn't able to do it. I then made a copy of the problem db and stripped out everything that wasn't needed. It is now quite small and still exhibits the same problem but it was still almost 29 megs until I ran the compact and repair database utility.
    When you open the db, the Switchboard will automatically load. Click the top option to go to the "Schedule" Switchboard. (In our world, any number of schedules can be written against a master agreement. Each schedule is a contract.)
    Click on the third option (Edit all schedules...) and observe that both the "Milestones" and "Referenced schedules" subforms display a final row with an asterisk. You can navigate through the other records to see what's in there but you don't need to do that in order to see the problem.
    Click on the "Close" button to return to the Switchboard. You can repeat the above step to see that it still displays the last row with the asterisk. Once you are ready to see the problem, close the form to return to the Switchboard.
    Click the second option (Find and view...) and observe that there are no rows with asterisks.
    Click the close button to close the form and return to the Switchboard.
    Click the third option again (Edit all schedules...) and observe that the "Milestones" subform has the last row with an asterisk, but the "Referenced schedules" subform does not.
    A schedule can refer back to another schedule for seveal reasons in our world. That is the reason I have this construct. If there is a better way to implement this, I'm very open to your suggestions.
    I am stumped! Any help you can give would be GREATLY appreciated!!
    Thanks
    Attached Files Attached Files

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The problem is caused by "Track name AutoCorrect Info" in the General tab of Tools | Options...
    With this turned on, both subforms are saved automatically when you close the main form.
    If I turn it off (i.e. clear the check box), the problem goes away: the subforms aren't saved each time.

    "Track name AutoCorrect Info" is known to cause a lot of problems. You should turn it off in all databases (unfortunately, Access turns it on by default in every new database and there is no way to change this default).

    See the attached version: [attachment=83589:CML_Test.zip]
    Attached Files Attached Files

  12. #12
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Wow! Hans, you are the greatest! What would I do without you? I can't imagine ever being able to have figured this one out on my own! I made the change and the problem simply vanished. Thank you so much! Who knows what other problems you probably also saved me from with this! I owe you a beer... or three.
    Thank you, thank you, thank you!

Posting Permissions

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