Force data entry of Main form AND sub form

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • RockKandee
    New Member
    • Dec 2013
    • 89

    Force data entry of Main form AND sub form

    I am working with Access 2013 in Windows 8

    Due to changes I made to make another form work, I have created myself a new problem with a different form.

    I have 2 tables
    Table 1 = parent
    Table 2 = child

    The parent table must have at least 1 child.

    On my form, I am trying to figure out how to prevent the main form from closing or going to a new record unless the sub has also created a record.

    I have tried numerous methods that have failed.

    I have been focused on an event for my close button. Once this is working I will include it with my new record button. I have the form set so these buttons are the only option to close or get a new record.

    None of my research has given me enough information to make this work. Every time I try to get an event to look at the sub form I receive the following error....

    Error Message: The object doesn't contain the Automation object 'Forms.' You tried to run a Visual Basic procedure to set a proper or method for an object. However, the component doesn't make the property or method available for Automation operations.

    'Forms' is replaced with what ever control of the sub form I have tried. This error was from attempting to use an If statement in a macro:
    Code:
    DCount((([Forms]![MainFormName]![SubFormName].[Forms]![NameofField])<1),1,0)
    I have tried to use - IsNull also

    Not sure if I should be using an If statement and I know DCount is for tables and queries, but I didn't know what else to try.

    I am using
    IF "whatever I can think of to try"
    then message box
    then go to control (the one I want filled in on the sub form, though I don't care where it goes)
    Else close form

    I am using macros.

    I would love some insight...

    Thanks for all of the help so far.
  • xzorxx
    New Member
    • Dec 2013
    • 12

    #2
    Hi ,I didn't get what you wan't really but what I have understand
    is that you trying to
    when create new record in the main form sub form get a new record
    and actually I can't see where is the problem is
    ms access will make new record when you make one on the main
    you can try to make query then create form to it ,and that's it.

    Comment

    • RockKandee
      New Member
      • Dec 2013
      • 89

      #3
      My access form is not creating a new record in the sub form unless I enter data in the sub form.

      If data is entered in the main form and then the form is closed (or goes to a new record) before entering anything into the subform, then I have no child record in table 2 related to the record created in table 1.

      I would like to know how this works for you.

      Thanks

      Comment

      • xzorxx
        New Member
        • Dec 2013
        • 12

        #4
        can you please tell me what are you using, table to form or query to form ??
        if it's query to form I believe that there is problem in your query can you write what did you do..@!

        Comment

        • zmbd
          Recognized Expert Moderator Expert
          • Mar 2012
          • 5501

          #5
          xzorxx :
          ms access will make new record when you make one on the main
          you can try to make query then create form to it ,and that's it.
          This is not exactly true, just because the parent has a record does not mean that the the child will have a related record.

          xzorxx :
          can you please tell me what are you using, table to form or query to form ??
          if it's query to form I believe that there is problem in your query can you write what did you do
          More than likely this has absolutly nothing to do with the query what so ever.

          xzorxx, just exactly how much experience you have with Access?


          @RockKandee
          IN the before update, check for "new record" and set a flag at the form declarations (the very top of the form's code).
          In the afterupdate event of the parent form, if the flag was set have the code either open a recordset or use the "INSERT" db.execute method.

          It's quite late here in the states or I'd bash you some code and an example; however, it's time for my nap, I just don't function well w/o 3 or 4 hours of sleep a day (I've made at least a dozen typos already).
          Let me get that and clear my desk in the morning. ADezii may be along here in a moment, I sware "A" can just exhale and the code will write itself for him! d(^_^)b
          Last edited by zmbd; Jan 14 '14, 06:23 PM.

          Comment

          • NeoPa
            Recognized Expert Moderator MVP
            • Oct 2006
            • 32633

            #6
            I'm struggling to understand exactly what it is you're trying to achieve here. You mention that a sub record isn't created when you create the main record, but not why that would surprise you. That seems to be standard behaviour with sub-forms. Any time you attempt to move focus away from the main part of the form (which includes pages - or tabs - but not any sub-forms, whether these are found on pages or on the main form itself) the main record will be saved - unless you abort that in your code. If you abort it, of course, you also abort the focus change.

            I hope some of this helps you to understand why things aren't behaving as you'd like, but I'm still a bit confused so we'll just have to see.

            Comment

            • zmbd
              Recognized Expert Moderator Expert
              • Mar 2012
              • 5501

              #7
              Neopa,
              I am Guessing here.
              Take a situation I have in the lab.
              When I receive a new item, the parent table has a record generated that has the details (serial, lot etc) and related information. The child table has to have the record created that has today's date and the fact that the item was received.

              Comment

              • NeoPa
                Recognized Expert Moderator MVP
                • Oct 2006
                • 32633

                #8
                I get the idea of related tables, Z. What I'm struggling to understand is the precise problem as reported. There seems to be some sort of expectation that both related records might be created at the same time somehow, but exactly what is required or expected is unclear to me.

                Comment

                • zmbd
                  Recognized Expert Moderator Expert
                  • Mar 2012
                  • 5501

                  #9
                  RockKandee
                  in your court.

                  Comment

                  • RockKandee
                    New Member
                    • Dec 2013
                    • 89

                    #10
                    I understand that the way the form is behaving is normal.

                    I understand that as soon as a record on the main form is created, it will be saved in the table. I am not trying to prevent the save of the main form record.

                    I am trying to prevent the close of the form or moving to a new main form record unless the subform has a record created.

                    I would like to force the user to create a record in the subform before the user can go to a new record on the main form or close the form.

                    This could be as simple as auto filling in the main forms ID number into the subform on an after update event on the main form.

                    I am not sure how else to explain.

                    Thanks for your help :D

                    Comment

                    • RockKandee
                      New Member
                      • Dec 2013
                      • 89

                      #11
                      When a field is required in a table, then the form will not save the record if the required field is blank.

                      I want to require that a subform record has been created (by the user) or the form will not close (or go to new record).

                      Thank you Z for trying to explain for me. *:
                      Last edited by RockKandee; Dec 19 '13, 11:17 PM. Reason: addition

                      Comment

                      • zmbd
                        Recognized Expert Moderator Expert
                        • Mar 2012
                        • 5501

                        #12
                        On Parent:
                        Before update event:
                        Check for new record
                        (optional)Valid ate the entries if needed
                        (optional)Confi rm parent record entry is intended
                        Set variable at module/form level that new record was created, I use PK value for the new record.

                        Afterupdate event
                        Check if the module/form level variable has a value>0 and if so then simple insert SQL to insert a new record into the child table with the value from the variable, set the variable back to a 0 or negative value, and requery the subform, set the focus to the first field/control in the subform.

                        Comment

                        • NeoPa
                          Recognized Expert Moderator MVP
                          • Oct 2006
                          • 32633

                          #13
                          Thanks Kandee. That's clearer now :-)

                          You can stop the form closing by using the Form_Unload() event procedure and setting Cancel=True.

                          I'm not sure there is a way to stop the focus moving from one record to another. You could use Form_Current() to see if it has done and then go back. That would be a way to handle it. Not too clean, but workable.

                          Comment

                          • RockKandee
                            New Member
                            • Dec 2013
                            • 89

                            #14
                            Thanks zmbd and NeoPa.

                            I am trying to work out what you both are saying so I can apply it.

                            I have been trying to figure out how to search for new record for 2 days now. I am feeling very blond, well I am , so I should, but still feeling like a bit too much bleach soaked into my head. LOL

                            I will keep at it. Thanks for your help. One of these days I will actually ask a clear question.
                            Last edited by zmbd; Dec 20 '13, 04:50 PM. Reason: [z{no edit{practice makes perfect (^_^)}]

                            Comment

                            • RockKandee
                              New Member
                              • Dec 2013
                              • 89

                              #15
                              My temporary solution until I can figure out the above information....

                              I have a form for the 2 tables that doesn't allow new records.

                              I added a pop up form that closes this form and opens a form described below using no subforms.
                              When this form is closed successfully, it re opens the previous form where the user can then add information to the new records created in the pop up form.

                              POP UP Form
                              I selected the one required data entry field from each table
                              Plus Id fields - Table 1 - primary key / table 2 - primary key and foreign key from table 1
                              Keys are hidden on the form leaving only Table 1 Name field and Table 2 Type field visible.

                              I set the form so the only option for closing or going to a new record is to use buttons added to the form.
                              Form - Format - Navigation Buttons - No
                              Form - Format - Close Button - No
                              Add Button - ctrlNewRecord
                              Add Button - ctrlClose

                              The Close Button:
                              Code:
                              ctrlClose button - Event - On Click - Macro
                              If - IsNull([Type]) Then
                              GoToControl - Control Name - Type
                              MessageBox - Type cannot be left blank
                              Else
                              CloseWindow
                              OpenForm
                              EndIf
                              If - IsNull([Name]) Then
                              CloseWindow
                              OpenForm
                              The New Record Button:
                              Code:
                              ctrlNewRecord button - Event - On Click - Macro
                              If - IsNull([Type]) Then
                              GoToControl - Control Name - Type
                              MessageBox - Type cannot be left blank
                              Else
                              GoTo Record - New
                              If - IsNull([Name]) Then
                              GoTo Record - New
                              This prevents the form from closing or going to a new record
                              IF the Type field is empty
                              UNLESS no record was created in Table one (No Name entered).

                              I am going to keep attempting using zmbd's and NeoPa's suggestions and will update when I have it working. Until then, if anyone else figures this out and can dumb it down for me, that would be awesome!

                              Comment

                              Working...