Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    May 2006
    Posts
    38

    Unanswered: Correct syntax for Sub Forms

    I have an unlinked form with a drop down at the top which allows user to look up a store. Once it is selected, the sub form one shows store details. Then I have a second form within this sub form which allows people to record various projects against each store.

    I would like to force the user to ensure that one field 'Priority' in the sub form 2 is always completed when they quit the screen. So I added the following code to the Before Update even of the sub form 2 and it works fine. When user moves to add next record using navigation button, this test takes place.


    If IsNull(ME.Priority) Then
    MsgBox "You must enter the priority number." _
    & "Failing which you will not be able to save this record." _
    & ""

    Me.Priority.SetFocus
    Me..Priority.Dropdown
    End If

    But the problem is when user chooses not to add a record and simply hit the Close Form button on the very first form, this test does not take place and the form is closed. So to get around this issue, I amended the above code as under :

    If IsNull(Forms!frmAssignment.Form!frmAssignmentMain. Form!frmDPProjects.Priority) Then
    MsgBox "You must enter the priority number." _
    & "Failing which you will not be able to save this record." _
    & ""

    Forms!frmAssignment.Form!frmAssignmentMain.Form!fr mDPProjects.Priority.SetFocus
    Forms!frmAssignment.Form!frmAssignmentMain.Form!fr mDPProjects.Priority.Dropdown
    End If

    There are no error messages, it still does not do the job.

    If my approach is wrong, then please correct me othewise will appreciate if someone can help with the expression I have used.

  2. #2
    Join Date
    Apr 2003
    Location
    Alabama, USA
    Posts
    154
    This is probably too basic but since I am mot sure if you did this in the post intentionally I thought I should at least point it out. You have some spaces in your expression to reference the controls, but I should think that you would get an error if that were the case.
    Code:
    If IsNull(Forms!frmAssignment.Form!frmAssignmentMain. Form!frmDPProjects.Priority) Then
    MsgBox "You must enter the priority number." _
    & "Failing which you will not be able to save this record." _
    & ""
    
    Forms!frmAssignment.Form!frmAssignmentMain.Form!fr mDPProjects.Priority.SetFocus
    Forms!frmAssignment.Form!frmAssignmentMain.Form!fr mDPProjects.Priority.Dropdown
    End If
    Also, I am not sure why you need this bit
    Code:
     MsgBox "You must enter the priority number." _
    & "Failing which you will not be able to save this record." _
    & ""
    Perhaps this might be helpful - How to refer to a form, its' sub-form and sub-sub-form.
    http://AccessDB.Info

    You live and learn. At any rate, you live. - Douglas Adams

  3. #3
    Join Date
    May 2006
    Posts
    38
    Thanks. These spaces don't appear in my code. Perhaps when copying the code, distortion occured.

    Re the nsgbox, that is to warn user to fill in the required info before quitting.

    I checked the ref you supplied previously, I still have a problem in correcly referring to sub form within a sub form. Hopefully, someone can help.

  4. #4
    Join Date
    Apr 2003
    Location
    Alabama, USA
    Posts
    154
    Can you post a sample file?
    http://AccessDB.Info

    You live and learn. At any rate, you live. - Douglas Adams

  5. #5
    Join Date
    Nov 2003
    Posts
    1,487
    Forms("ParentForm")("FirstSubFormControlName")("SecondSubFormControlName").Form.FieldOnSecondSubForm.SetFocus

    Forms("ParentForm")("FirstSubFormControlName")("SecondSubFormControlName").Form.FieldOnSecondSubForm.Dropdown

    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  6. #6
    Join Date
    May 2006
    Posts
    38
    Thanks. I was trying to preape the DB to attach as a sample and then I got this response from Cyberlynx. I might just try his/her solution. Please can you tell me if I need square brackets around any where in the statement. Obviously, quotes are not to be used?

  7. #7
    Join Date
    Nov 2003
    Posts
    1,487
    Quotes are indeed to be used. That's what makes it so simple to use.

    Forms("ParentForm")("FirstSubFormControlName")("SecondSubFormControlName").Form.FieldOnSecondSubForm.SetFocus

    Simplay place the appropriate names into the statement. For example:

    ParentForm would be the name of the main Form which is displaying your SubForms. This would be the name as you see it in the database window.

    FirstSubFormControlName is the actual name of the SubForm Control that your first SubForm is displayed in. Not the Form name but the Control name as shown in the properties window for this control.

    SecondSubFormControlName is the actual name of the SubForm Control that your second SubForm is displayed in. Not the Form name but the Control name as shown in the properties window for this control. This is the control that was place within your first SubForm Control.

    FieldOnSecondSubForm is the name (as seen in the Name property) of the Control you want to set focus to which is located within the Second SubForm.

    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  8. #8
    Join Date
    May 2006
    Posts
    38
    Thanks so much. Very clear explanation. I have customised my statement in the light of the above. No errors. As I wish to alert user before closing the form which is triggered by a Close button, I put the code in the Before Update even of the parent form. So now when the Priority field is blank, and user tries to close the form, user gets an alert, however the form closes before giving an opportunity to the user to go back and add the required priority. Can I hold the form closure process so that user can enter the priority. In its revised form, I am using following code in case you need to review.

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    On Error GoTo MyError

    If IsNull(Forms("frmDP")("frmDPSF")("frmDPProjects"). Form.Priority) Then
    MsgBox "You must enter the priority number." _
    & "Failing which you will not be able to save this record." _
    & ""

    Forms("frmDP")("frmDPSF")("frmDPProjects").Form.Pr iority.SetFocus
    Forms("frmDP")("frmDPSF")("frmDPProjects").Form.Pr iority.Dropdown
    End If
    ExitSub:
    Exit Sub

    MyError:
    MsgBox Err.Description & " _ " & Err.Number
    Resume ExitSub
    End Sub


    Regards

    Ash

  9. #9
    Join Date
    Nov 2003
    Posts
    1,487
    I think the best way to approach this for what you are trying to do is to actually take the code you have made (plus a little more) and place it into a Function which is Private to the Parent Form (frmDP). Then, call this function from both the BeforeUpdate event and the Unload even of your frmDP Form. Since you have provided your code, your function should look like this:

    Place this Function into the code module for your frmDP Form. It should work for your application:

    Code:
    Private Function CheckPriority() As Boolean
       ' Trap Errors if one should occur.
       On Error GoTo CheckPriority_Error
     
       ' See if the Priority field located within the "frmDPProjects" nested
      ' SubForm is NULL.
      If IsNull(Forms("frmDP")("frmDPSF")("frmDPProjects"). Form.Priority) Then
          ' If it is then inform the user that this is not acceptable.
          MsgBox "You must provide a Priority number." & vbCrLf & _
                     "This Record will not be Saved if you fail to do so.", _
                     vbExclamation, "Data Required..."
     
          ' In order to properly set focus to a control within a SubForm 
         ' or a nested SubForm, you must first set focus to to the
         ' SubForm Control which holds each SubForm within the order
         ' then finaly the field control itself which in this case is a 
         ' ComboBox.
     
          ' Set Focus to the First SubForm (frmDPSF).
          Forms("frmDP")("frmDPSF").SetFocus
          ' Set Focus to the Second nested SubForm (frmDPProjects).
          Forms("frmDP")("frmDPSF")("frmDPProjects").SetFocus
          ' Set Focus to the "Priority" field Control in the Nested SubForm (frmDPProjects).
          Forms("frmDP")("frmDPSF")("frmDPProjects").Form.Priority.SetFocus
          ' Make the Priority field ComboBox Drop Down so as to view the list.
          Forms("frmDP")("frmDPSF")("frmDPProjects").Form.Priority.Dropdown
          ' Make this function return True 
          CheckPriority = True
       End If
     
    CheckPriority_Exit:
       ' Outta Here...
       Exit Function
     
    CheckPriority_Error:
       ' Display the Error message and number if an Error 
      ' happened to be generated.
       MsgBox Err.Description & " _ " & Err.Number
       ' Clear the Error
       Err.Clear
       ' Resume code at the CheckPriority Lable.
       Resume CheckPriority_Exit
    End Function
    Now simply make sure you have the following line of code in both the BeforeUpdate event and the Unload event of your frmDP Form:

    Cancel = CheckPriority

    This should pretty much cover the bases I hope.

    Code Corrected May 30, 2006
    .
    Last edited by CyberLynx; 05-30-06 at 04:49.
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  10. #10
    Join Date
    May 2006
    Posts
    38
    Thanks so much for your help. I have tried this. There is one error.

    CMS Database can't find the field 'frmDPProjects' refered to in your expression. _ 2465.

    Cheers

  11. #11
    Join Date
    May 2006
    Posts
    38
    I am sorry, I guess I should have given you more details. I added the code to the unload and Before update event of the parent form as required. I copied the full function within the same class module. I then checked that the last nested form has the right name and it turned out that the name was wrong. So I changed it to the correct name. I than tried to enter a record and left the priority field blank. Then I closed the form using close button and this time round I still got the error but not the one I wrote earlier. It says 'Type mismatch_13'.

    I hope we can remove this small error.

    Regards

  12. #12
    Join Date
    Nov 2003
    Posts
    1,487
    On which line is the error pointing to? It should be highlighted if you select the Debug button in the Error message box.

    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  13. #13
    Join Date
    May 2006
    Posts
    38
    It is a very small message with 'OK' as the only option provided which when pressed closes the form. HTH.

  14. #14
    Join Date
    May 2006
    Posts
    38
    I have attched the sample db if this could be of help. Pl open the frmDP and select a store and adddata to few fields except Priority and then close the form. You will see the error.

    Regards
    Attached Files Attached Files

  15. #15
    Join Date
    Nov 2003
    Posts
    1,487
    The 'Type mismatch_13' Error is generated simply because.....I forgot to place a comma into the code for the MsgBox (sorry). It is suppose to go between the

    ...do so." and the vbExclamation

    Instead of this:
    Code:
    MsgBox "You must provide a Priority number." & vbCrLf & _
                     "This Record will not be Saved if you fail to do so." & _
                     vbExclamation, "Data Required..."
    It should be this:
    Code:
    MsgBox "You must provide a Priority number." & vbCrLf & vbCrLf & _
           "This Record will not be Saved if you fail to do so.", _
           vbExclamation, "Data Required..."
    You are also going to get a Run Time Error in the OnClick event for your Close button that states:

    The Close Action was Canceled.

    To get around this simply change the code in this event from this:

    Code:
    DoCmd.Close
    Exit_Command8_Click:
        Exit Sub
     
    Err_Command8_Click:
        MsgBox err.Description
        Resume Exit_Command8_Click
    To This:

    Code:
       On Error Resume Next
       DoCmd.Close
       If err <> 0 Then err.Clear
    .
    Last edited by CyberLynx; 05-30-06 at 04:50.
    Environment:
    Self Taught In ALL Environments.....And It Shows!


Posting Permissions

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