Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    njdaisy86 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jun 2018
    Posts
    7

    Don't save parent record if child record (on sub form) is null

    Hello Experts.

    (Parent Record) Form: frmComboBoxNewDelete
    (Child Record) Sub Form: frmComboBoxNewDeleteTransferSubform

    When the User enters the parent record information but decides to "exit" the record WITHOUT entering the child record information, the parent record saves. What is the code to prevent the parent record from saving. This is my code:

    Private Sub Form_Unload(Cancel As Integer)
    If IsNull(Me.LastName) Then
    If MsgBox("There is no Employee Record. Do you really want to exit?", vbYesNo, "Exit Confirm") = vbNo Then
    Me.LastName.SetFocus
    Cancel = True
    End If
    End If

    If IsNull(Me!frmComboBoxNewDeleteTransferSubform.Form ![TransferDate]) Then
    If MsgBox("Make sure you enter a Transfer Record." & vbCrLf & "This is MANDATORY." & vbCrLf & "Do you really want to exit?", vbYesNo, "Exit Confirm") = vbNo Then
    Me!frmComboBoxNewDeleteTransferSubform.Form![TransferDate].SetFocus
    Cancel = True
    End If
    End If
    End Sub



    After the second msgbox fires, and if the User selects "Yes", I do not want the parent record to save. Thank you.

  2. #2
    Beetle is offline Unrelatable
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    Camp Swampy (Denver, CO)
    Posts
    207
    By the time the user enters the sub form the record on the main form is already saved, so at that point you would need to retrieve the PK value of the record on the parent form and delete it. Untested example code;

    Code:
    If IsNull(Me!frmComboBoxNewDeleteTransferSubform.Form ![TransferDate]) Then
        If MsgBox("Make sure you enter a Transfer Record." & vbCrLf & "This is MANDATORY." & vbCrLf & "Do you really want to exit?", vbYesNo, "Exit Confirm") = vbNo Then
            Me!frmComboBoxNewDeleteTransferSubform.Form![TransferDate].SetFocus
            Cancel = True
        Else
            CurrentDb.Execute "DELETE * FROM tblYourParentTable WHERE PrimaryKeyValue = " & Me.PrimaryKeyField , dbFailOnError
        End If
    End If
    Items in red need to be modified to reflect your actual table/field names. Also, you may need to add delimiters depending on the data type of your PK field.

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    As Sean said, you can't avoid the parent record saving. Another option would be to bind the form and subform to local tables, and only append to the "real" tables if your conditions are met.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    njdaisy86 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jun 2018
    Posts
    7
    Sean, Thank you so much! Your code worked PERFECTLY! I'll work on this one tonight:

    Dim Thirsty As String
    Thirsty = "Insert Into Glass (Select Beer From Fridge)"
    DoCmd.Execute Thirsty, dbPourAnotherOnError

    too funny....I thought that was part of my code.

    Paul, thank you also for the suggestion. I'm new at coding so any help and suggestions are appreciated.

    Terri.

  5. #5
    njdaisy86 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jun 2018
    Posts
    7
    Sean, Thank you so much! Your code worked PERFECTLY! Exactly what I needed!

    I'll work on this one tonight:
    Dim Thirsty As String
    Thirsty = "Insert Into Glass (Select Beer From Fridge)"
    DoCmd.Execute Thirsty, dbPourAnotherOnError

    too funny....I thought that was part of my code.

    Paul, thank you also for the suggestion. I'm new at coding so any help and suggestions are much appreciated.

    Terri.

  6. #6
    Beetle is offline Unrelatable
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    Camp Swampy (Denver, CO)
    Posts
    207
    Happy to help, although I suggest you get your coding done before you execute Thirsty too many times

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I got a kick out of that one Sean, though I fear including the dbPourAnotherOnError option will create an endless loop.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    njdaisy86 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jun 2018
    Posts
    7
    One additional question that just came up....
    I get a Run-time error '3017: Syntax error (missing operator) in query expression 'EmployeeID ='. when the form is opened but NOTHING is entered in any field but the close form button is selected. Is there code to by-pass or prevent this?

  9. #9
    Beetle is offline Unrelatable
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    Camp Swampy (Denver, CO)
    Posts
    207
    @pbaldy

    I got a kick out of that one Sean, though I fear including the dbPourAnotherOnError option will create an endless loop.
    Yes, that happened to me once. Luckily a friend was around to Ctrl-Alt-Delete me.



    @njdaisy86

    I might suggest a slightly different approach. When the form is closed, grab the PK value of the current record in the main form (parent table), then check to see if there are any associated records in the child table. More example air code;

    Code:
    If Not IsNull(Me!PKField) Then
        If IsNull(DLookup("ChildTableID","ChildTable","ChildTableID=" & Me!PKField)) Then
            If MsgBox("Make sure you enter a Transfer Record." & vbCrLf & "This is MANDATORY." & vbCrLf & "Do you really want to exit?", vbYesNo, "Exit Confirm") = vbNo Then
                Me!frmComboBoxNewDeleteTransferSubform.Form![TransferDate].SetFocus
                Cancel = True
            Else
                CurrentDb.Execute "DELETE * FROM tblYourParentTable WHERE PKValue = " & Me!PKField , dbFailOnError
            End If
        End If            
    End If



  10. #10
    njdaisy86 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jun 2018
    Posts
    7
    Beetle,

    Thank you for your suggestion unfortunately I could not get it to work. Here's the dilemma....if the User just opens the form and attempts to close the form without entering any information, no PK value is generated. I get a Run-time error '3017: Syntax error (missing operator) in query expression 'EmployeeID ='. The user then has to select "end" or "debug". Both of which I do not want. Because I have codes in the On Unload event to prevent null fields, I don't know how to resolve this issue. I there another code you can suggest?

  11. #11
    Beetle is offline Unrelatable
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    Camp Swampy (Denver, CO)
    Posts
    207
    In the code example I posted, this line;

    - If Not IsNull(Me!PKField) Then

    should prevent the code from running if there is no PK value on the main form (i.e. the user has navigated to a new record but not entered any data).

    Can you post your full code as it currently stands?

    Alternatively, if you want to upload a sanitized copy of your db I can take a look at it.

  12. #12
    njdaisy86 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jun 2018
    Posts
    7
    Beetle,

    Thank you....you are correct and your code does work to close the form if nothing is entered in either parent and child forms. However, if the user enters only parent information and hits the "save" button, with your code, that record is saved as opposed to having that record deleted. I need the parent and corresponding child records to be entered. I've attached screen shots of the form and VBA code. Can you help me? I want the form to close if the user "accidently" opens the form and enters nothing and closes [clicks on save] the form. I'd also like the parent record to not save if the user selects the "save" button but has not entered the child record. Thank you so much for your help.

    Click image for larger version. 

Name:	OnUnload.png 
Views:	42 
Size:	43.4 KB 
ID:	34509Click image for larger version. 

Name:	Untitled.png 
Views:	41 
Size:	47.7 KB 
ID:	34510

  13. #13
    Beetle is offline Unrelatable
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    Camp Swampy (Denver, CO)
    Posts
    207
    I'm curious about your DLookup after seeing your actual code;

    Code:
    If Isnull(DLookup("TransferID", "tbl_Transfer", "TransferID=" & Me!EmployeeID)) Then
    Why are you comparing TransferID in the child table to EmployeeID in the parent? It seems like you would evaluate based on EmployeeID in both tables. It's hard to say for sure but it appears from your image that there is an EmployeeID field in the subform (i.e. in the child table).

    ...if the user enters only parent information and hits the "save" button, with your code, that record is saved as opposed to having that record deleted.
    Just to clarify, it sounds like the message box is never actually being displayed in this scenario and the new record in the main form is saving? If that is the case, I have two guesses (right now) as to the cause;

    1) Per my previous comment, you are comparing the incorrect fields. However, these are both Autonumber ID fields and, as such, there is a good probability that there are matching values that are just coincidental (the DLookup doesn't care if the two fields are related, it just evaluates if 1 = 1). So the IsNull function never (or rarely) evaluates to True and the rest of the code is skipped.

    2) If you are actually storing the EmployeeID value from the parent table in the TransferID field in the child table for some reason, then my secondary guess would be that there are pre-existing records in the Transfer table with that EmployeeID (TransferID), and Dlookup is just returning the first one it finds. In this case you would need to change (or add to) the DLookup criteria in order to return the correct row in the child table (or, more specifically, look for the absence of that row).

  14. #14
    njdaisy86 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jun 2018
    Posts
    7
    Beettle,

    Sorry I have not responded sooner..... Okay, so went over all your suggestions and made some modifications to my code. Now, if the User only enters Parent record information (EmployeeID, Lastname, Firstname, EmployeeType) and NO corresponding Child record (TransferID, TransferDate, etc......), the form closes and the Parent record is NOT saved. YEAH!


    However, if the User opens this form and promptly closes the form without entering a darn thing, my message boxes pop up (as it should) and I enter "Yes" to both message boxes to exit the form; I get a "Run-time error '3075': Syntax error (missing operator) in query expression 'EmployeeID ='. This is my code in my On Click event:

    DoCmd.Close acForm, Me.Name
    DoCmd.Close acForm, "frmComboBoxFind"

    Can you help me resolve this error? Thank you.

  15. #15
    deepakg27 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2017
    Posts
    160
    Thanks, topic helps me out also.

    Thanks and Regards
    Deepak Gupta

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

Similar Threads

  1. Replies: 15
    Last Post: 05-16-2018, 12:12 PM
  2. Replies: 7
    Last Post: 07-28-2015, 11:50 AM
  3. Replies: 5
    Last Post: 05-10-2014, 12:25 PM
  4. Replies: 3
    Last Post: 08-09-2012, 01:49 PM
  5. Delete child records with parent record
    By jpkeller55 in forum Access
    Replies: 8
    Last Post: 09-07-2010, 08:52 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