Closing a Form after Validation

[theDBguy]

No


No

Okay, here's the situation we have, if I understand it correctly.


Let's say the main form is bound to tblParent and the subform is bound to tblChild, and let's say both tables are currently empty.


So, you're basically saying you don't want the user to enter a child record without entering a parent first. This makes sense because if we allow a child record without a parent, then we'll have an orphan record. These orphan records won't show up in the subforms, even if they exist in the table, because the link fields in the mainform/subform setup will eliminate them from being display due to a missing foreign key to tblParent.


So, to enter the first child record in tblChild (subform), we'll need the first record in tblParent (mainform). Let's say the ID for the parent record is 1. Now, when we select a location in the subform, the foreign key for that first record will also be a 1, which matches the ID from tblParent (mainform). Hope you're following so far.


Now, the problem is you said you didn't want tblParent to have a record with ID=1 if there is no matching FK=1 in tblChild. But as you can see, you can't have a record in tblChild with FK=1 if there's no record in tblParent with ID=1.


So, the only way we can do this is if you let the user enter a record on the main form (ID=1) but delete it if the user doesn't then enter a record in the subform. However, you can only trap that event either in the Close event of the form or its Current event. The Close event will catch the situation I asked you in Question #1 above, and the Current event can catch the situation I asked you in Question #2 above.


Either that, or like I said, use unbound forms or forms bound to temporary tables.


Hope it makes sense...

 

[Weekleyba]

Thank you so much for your patients with this VBA rookie!
I totally follow what you have laid out.
All of that makes sense, logically.
The problem I have is translating that logic into code for the Close and Current Events.
I’m not experienced enough to come up with this code on my own, so I end up searching to try and find relevant examples but everything I’ve tried comes up short.

History:
I’ve been learning Access for a few years on and off using mainly the wizard to perform many of the tasks but I’ve come to realize that I really need to learn VBA to release the full power of the Access. I’m not afraid of learning and have purchased two books on learning VBA and I’m about 1/3 through each of them.

Goal:
I’m an facility engineer with the Indian Health Service in the US and I’ve developed a small database that I use for managing all my projects across two states. The database has caught the eye of my director and he wants me to develop a database for all the engineers to use. This thread has been helping me with one of the issues in my pursuit of the goal.
My previous db was not able to have one project at multiple locations. Thus that’s the reason for the continuous subform with the junction table. I also need the capability of having multiple contracts with each project and this that will also be a subform on the Project main form but that one does not need to populated at first.
Anyway I probably went too far in the weeds with this but thought a little more background might help you understand where I’m coming from and trying to get to.
I hope I’m not wearing out my welcome on this forum as it has very helpful in learning about Access.
Have a Happy Thanksgiving and we can pick this up after the holiday if you are willing.
Thanks again.

 

[theDBguy]

Hi. I probably won't have a chance to play with your file today because of the Holiday, but I'll let you know as soon as I get a chance. Cheers!

 

[Weekleyba]

Thought I would upload the latest.
Thanks for your help!

 

[theDBguy]

Thought I would upload the latest.
Thanks for your help!

Hi. I had a little time to play with it today but not enough time to test it thoroughly. Just take a look and let us know if you have any questions. Hope it helps...

 

[Weekleyba]

I like it! Thank you very much.

I was wanting to make the message box for the missing Location, to look the same as the message box for the controls on the Project form.
I changed the code to this (see below) but get this error:
Error 91 object variable or with block variable not set

What am I missing here?

Private Sub Command5CloseButton_Click()
'This is for the close button.
'A False response from ValidationOfControl means, all tagged fields have data.
Dim msg As String, Style As Integer, Title As String
Dim nl As String, ctl As Control
nl = vbNewLine & vbNewLine
On Error GoTo ErrProc
    If Me.Dirty Then
        DoCmd.RunCommand acCmdSaveRecord
    End If
    'modified by [EMAIL="thedbguy@gmail.com"]thedbguy@gmail.com[/EMAIL]
    '11/28/2019
    If Not IsNull(Me.ProjectID) And Me.F_Location.Form.Recordset.RecordCount > 0 _
        Or IsNull(Me.ProjectID) And Me.F_Location.Form.Recordset.RecordCount = 0 Then
        DoCmd.Close
    Else
        'MsgBox "Please select a Location for this project.", vbQuestion, "Required Data..."
            msg = "Data Required for '" & ctl.Me.cboLocation1 & "' field!" & nl & _
                  "You can't save this record until this data is provided!" & nl & _
                  "Enter the data and try again . . . "
            Style = vbQuestion + vbOKOnly
            Title = "Required Data..."
            MsgBox msg, Style, Title
            ctl.Me.cboLocation1.SetFocus
    End If
ExitProc:
    Exit Sub
ErrProc:
    Select Case Err.Number
        Case 2501
        Case Else
            MsgBox Err.Number & "--" & Err.Description
     End Select
     Resume ExitProc
     
 End Sub

 

[Weekleyba]

After some more effort, I was able to find a solution.
See below.
Not sure this is proper code but, it seems to work.

I do have a question though.
When the subform, F_Location, is missing data, the code identifies it and sends out the message box. But, I want the focus to be set on the subform control that is missing. Namely, cboLocation1. This is not working.
Is it because the subform record is not yet dirty or created?

Private Sub Command5CloseButton_Click()
'This is for the close button.
'A False response from ValidationOfControl means, all tagged fields have data.
    Dim nl As String
    nl = vbNewLine & vbNewLine
On Error GoTo ErrProc
    If Me.Dirty Then
        DoCmd.RunCommand acCmdSaveRecord
    End If
    'modified by [EMAIL="thedbguy@gmail.com"]thedbguy@gmail.com[/EMAIL]
    '11/28/2019
    If Not IsNull(Me.ProjectID) And Me.F_Location.Form.Recordset.RecordCount > 0 _
        Or IsNull(Me.ProjectID) And Me.F_Location.Form.Recordset.RecordCount = 0 Then
        DoCmd.Close
    Else
        MsgBox "Data Required for Location field!" & nl & _
        "You can't save this record until this data is provided!" & nl & _
        "Enter the data and try again . . . ", vbQuestion, "Required Data..."
        Me!F_Location.Form!cboLocation1.SetFocus               'This doesn't work.....???"
    End If
ExitProc:
    Exit Sub
ErrProc:
    Select Case Err.Number
        Case 2501
        Case Else
            MsgBox Err.Number & "--" & Err.Description
     End Select
     Resume ExitProc
     
 End Sub

Attached is the sample database with the issue.
Thanks for you help.

 

[Weekleyba]

Well, I figured it out.
You have to set the focus to the subform first, then set the focus to the control in the subform.

    Else
        MsgBox "Data Required for Location field!" & nl & _
        "You can't save this record until this data is provided!" & nl & _
        "Enter the data and try again . . . ", vbQuestion, "Required Data..."
        Me!F_Location.SetFocus
        Me!F_Location.Form.cboLocation1.SetFocus
    End If

With that....this thread is closed.

 

[theDBguy]

Well, I figured it out.
You have to set the focus to the subform first, then set the focus to the control in the subform.

    Else
        MsgBox "Data Required for Location field!" & nl & _
        "You can't save this record until this data is provided!" & nl & _
        "Enter the data and try again . . . ", vbQuestion, "Required Data..."
        Me!F_Location.SetFocus
        Me!F_Location.Form.cboLocation1.SetFocus
    End If

With that....this thread is closed.

Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.

 

[Weekleyba]

Ok...so I thought I had this all figured out but when I went to apply it to my larger project...I can't get it to work right.
Attached is my database all stripped down and it still won't work right.
I'm trying to make sure that when the user adds a project, they are forced to add the ProjectName, Engineer, ProjectPhase, and Location.

Can please help me out before I pull all my hair out!
Thank you.

 

[Weekleyba]

I think the problem is that the public function is not firing on the Before Update event.
I just can’t see why it is not.
Can anyone take a look and tell me why?
Thanks

 

[Pat Hartman]

There are too many comments for me to review all of them fully to determine where you are but here's what's wrong with the basics. If you fix these things, you should be able to move forward.

1. There is no Referential Integrity enforced. If there were, you would be getting errors that would have pointed out some of the other issues. If you cannot add RI, then fix the data FIRST and go back and add RI.
2. Foreign keys should be set to required if a value is required. None of the FK's are defined as required.
3. Foreign keys should rarely have a default. The default should normally be null. The default should NEVER be 0 since that isn't a value that would ever be generated as an AutoNumber. For example, it might make sense to default the ProjectPhaseID to "1" or whatever the first phase is but by what logic would you default the engineer to "1"? Personally, I don't think either of these fields should have a default at all. DateCreated can default to Date() if that makes sense but if you are always entering data after the fact, then even the date shouldn't have a default but all should still be set as required.
4. You have default values set on the form controls so the length will NEVER be zero and that means that your validation will never work.

I'm pretty sure I made some suggestions earlier in this thread but you chose another path. I'm going to explain again the issue.

You cannot save a child record that has no parent record if RI is enforced (you do not have RI enforced but you should). The forms also raise errors if you try to do this as long as the master/child links are defined since the PK of the parent record would be null if no record existed so this essentially prevents you from creating a child record without a parent. Not that you would EVER want to do this in any event. However, Once the parent record is saved, there is no built in way to force you to save a child record at this time. The reason for this is because relational database theory does not require it. So if the basic definition of a relational database does not require something, neither Access nor any other database engine will enforce this rule. This is a business rule and you are solely responsible for enforcing business rules. You were told multiple times that the only way to actually do this is by using unbound forms or forms bound to temp tables. Only when the entire transaction is complete would your code attempt to add the data to the permanent tables and this code would run inside a transaction to ensure that BOTH the parent and child rows got copied. The transaction ensures that ALL actions within the transaction have completed successfully before any are actually committed.

One other thing that needs mentioning is with regard to how bound forms actually work. Somewhere in this epic thread you or someone mentioned that you want to make the subform dirty while the mainform is dirty. That is NOT possible. When you move focus to the subform in order to dirty it, that forces Access to save the main form first. So the main form is saved if it is dirty and THEN the subform gets the focus but the main form has ALREADY been saved so it is no longer dirty.

You are creating a logical impasse. You can't save the child record when the parent has not been saved (RDBMS rule) and you can't save the parent record when there is no child record (your business rule). Something has to give.

You have spent a long time fighting with this but I'm not sure you understand what the battle is all about yet. This isn't a failing of Access is is a conflict caused by your business rules. So, step back and answer the question - what bad thing will happen if there is no location record for a project? If the answer is nothing, then fix the problems I identified and move on. Stop trying to enforce this business rule.

If the world will come to an end if a project is created without a location, then you will have to create shadow tables and all your create activity will have to happen on the shadow tables (this will be a far easier solution to implement than unbound forms would be. But there are still lots of moving parts). When the user wants to commit his work, he presses a button and your code will verify that there has been at least one location created. Start a transaction and run two append queries. One that appends the project and the second that appends the location records. Don't forget that you MUST obtain the Autonumber PK of the project record BEFORE you can run the append query for the locations so you can pass the FK to the append query. Then you delete the project from the shadow tables and complete the transaction. ALL I/O that took place within the transaction will either complete successfully or be rolled back. You'll have to figure out how to deal with errors. In addition, since "adds" are essentially done off-line, you will need application logic to bring up pending "adds" that have not been completed and deal with them.

You will also need logic in the view/change/delete version of the forms to prevent the deletion of the "last" location since that would create the anomaly of a project without a location.

Another solution I have used multiple times is to add a flag to the parent record. When the set of data has been verified, the flag gets set to "verified". So, your form would have a "verify" button and that button would determine if there is at least one location record and if one was found (use dCount()), then update the verified flag to true. This is significantly simpler than the shadow tables solution or the unbound forms solution. However, noting is without problems. This solution is difficult to add to an existing application because you will need to find all queries that reference the project table and add criteria to ignore any unverified records. EXCEPT of course for the maintenance form. That form needs to include the unverified records in order to verify them. Keep in mind that once the project record is saved NOTHING IN THE WORLD can force the location to be saved. The user always has access to the power off button and will use it if frustrated. So, that means that this technique as well as the shadow tables technique REQUIRES that you run queries at startup to identify work in process and bring it to the attention of the manager as well as the person who started the task but didn't finish it.

 

[Weekleyba]

Pat,
Thanks for the response.
As it turns out, the problem I had was with the default value for the FKs for the Engineer and ProjectPhase in the T_Project, which were set to 0. This is the default when adding a number field. (still learning here...so I didn't realize I had to change that)
I also deleted the default values on the form controls.
The reason for forcing the user to enter a Location, has to do with the larger project I'm working on, which has listbox on the main form that requeries once a project is added. If the location is not included, the listbox does not show the project. (due to underlying query)
That's why the world was coming to end. (-:
If the user deletes the location, it does not allow them to close the form. So, I think I'm good to go there.
I won't pretend to understand everything you wrote here, but I do appreciate it all and I've read through not less than a few times. Good stuff!

Bottom line, the attached sample db, works like I want (or think I want).

Big thanks to DBguy who taught me a lot through this 'epic' thread.
Sometimes it takes an epic thread to get through this thick skull of mine!

Thanks for all the patience with the newb. Love this forum!

 

[Pat Hartman]

The reason for forcing the user to enter a Location, has to do with the larger project I'm working on, which has listbox on the main form that requeries once a project is added. If the location is not included, the listbox does not show the project. (due to underlying query)
T

This can be handled by using a left join between the project and location tables.

If the user deletes the location, it does not allow them to close the form. So, I think I'm good to go there.

Refer to my previous comments regarding the user having the ultimate power because he has the power off button. If you frustrate him, he will use it. YOU cannot prevent this. Therefore, YOU must prevent the delete if it would remove the only location record. YOU can prevent the delete by using the BeforeDelete and/or BeforeDeleteConfirm events. Attempting to prevent the closure of the form is akin to closing the barn door after the horse has escaped. Once the record is deleted, it is too late to prevent a problem.

 

[Weekleyba]

Thanks Pat!
I give both of those a try and see where I get.

Is it wrong that I’m starting to like VBA?

 

[Pat Hartman]

VBA is actually much easier than macros. Macro logic is bizarre to anyone who knows even the rudiments of VBA.