Closing a Form after Validation (1 Viewer)

Weekleyba

Silver Supporter
Joined
Oct 10, 2013
Messages
219
Ok. I have form, F_Project, that when opened I want to make sure certain fields are populated. The following code is in the Before Update Event and seems to work well when I tab through and it tries to update but the event prevents it.
The problem is, I also want to have command button on the this form to close the form. The only code there is, On Click, DoCmd.Close.
What happens is if I click the close button I created, it will find the first text or combo box that is required to be populated, give me the message, I click OK, then is closes the form instead of setting the focus on the empty control.
How to do correct this?
Thanks for any help!

code#
Private Sub Form_BeforeUpdate(Cancel As Integer)
'An asterisk (*) in the Tag Property of the text for combo boxes to be validated.

Dim msg As String, Style As Integer, Title As String
Dim nl As String, ctl As Control
nl = vbNewLine & vbNewLine
For Each ctl In Me.Controls
If ctl.ControlType = acComboBox Or ctl.ControlType = acTextBox Then
If ctl.Tag = "*" And Len(ctl.Value & "") = 0 Then ' "" indicates a null string
msg = "Data Required for '" & ctl.Name & "' 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.SetFocus
Cancel = True
Exit For
End If
End If
Next
End Sub
code#
 

theDBguy

I’m here to help
Joined
Oct 29, 2018
Messages
7,577
Hi. You may have to move your validation routine into a separate function, so you can call it from multiple places like the BeforeUpdate event and your Close button.
 

Weekleyba

Silver Supporter
Joined
Oct 10, 2013
Messages
219
Pardon my inexperience here but, do mean a Public function vs the Private ones?
I’ll give that a try. Thanks.
 

theDBguy

I’m here to help
Joined
Oct 29, 2018
Messages
7,577
Pardon my inexperience here but, do mean a Public function vs the Private ones?
I’ll give that a try. Thanks.
Hi. Either one. You might start out with a Private one first, if it will make it easier for you.
 

Weekleyba

Silver Supporter
Joined
Oct 10, 2013
Messages
219
I've been at this for awhile and I need some help on this... :banghead:

I'm trying to force the user to enter data in three text boxes
ie. ProjectName, EngineerName, & ProjectPhase.
(I need to add the Location too but, that's in a subform which complicates things for me. One step at a time, I guess.)

I first put the code in a Private Sub in the Before Event for the form and this worked fine but, I wanted to add a command button to close the form. This is where things got complicated for me...
I then tried to create a Public Function and call it from the Before Event of the form and from the On Click event of the command button. But, this too is not getting the results I want. FYI - I'm still very new to VBA but trying to learn, so I know what I currently have is not correct.

Below is the code in the Public Function.
Also, attached is a sample db showing the problem.

I'm trying to achieve:
If the user clicks Add New Project, but does not input any data, the user should be able to click the close command button and close the form.
If the user clicks Add New Project and inputs any data at all, I want to force them to add the data in the four required fields prior to closing or creating a new record.
If the user clicks the close command button after adding some data but not to all the required fields, it would give a warning and set the focus to that specific field.
If the user tries to create the record, the Before Update should force them to add data to the four required fields.


Any and all help would be very much appreciated!


Code:
Public Function ValidationOfControl()
    Dim msg As String, Style As Integer, Title As String
    Dim nl As String, ctl As Control
    
    nl = vbNewLine & vbNewLine
    For Each ctl In Forms!F_Project.Controls
        If ctl.ControlType = acComboBox Or ctl.ControlType = acTextBox Then
            If ctl.Tag = "*" And Len(ctl.Value & "") = 0 Then
              msg = "Data Required for '" & ctl.Name & "' field! Before Update" & 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.SetFocus
              Cancel = True
              Exit For
            End If
          End If
       Next ctl
End Function
 

Attachments

theDBguy

I’m here to help
Joined
Oct 29, 2018
Messages
7,577
Hi. I think I can take a look at your file this weekend (if no one else beats me to it).
 

MickJav

Registered User
Joined
Nov 28, 2005
Messages
1,149
What I normally do is if all tests are passed the return true if not false then in the button test for that.


You will need to update your function like
Public Function ValidationOfControl() As Boolean


Edit I did add a number of validation functions to my employees example Here: https://www.access-programmers.co.uk/forums/showthread.php?t=306487


Look at the code behind the employees OK button.
 

Weekleyba

Silver Supporter
Joined
Oct 10, 2013
Messages
219
I’m still at a loss on this.
I’m thinking that the two cannot work together to validate.
Perhaps the answer would be to just use the close button with the code OR the BeforeUpdate with the code bit not both.
I’ve tried using an IF statement too but failed.
Any other ideas before I pull the plug on this?
As always, thanks for all advise and ideas.
 

theDBguy

I’m here to help
Joined
Oct 29, 2018
Messages
7,577
I’m still at a loss on this.
I’m thinking that the two cannot work together to validate.
Perhaps the answer would be to just use the close button with the code OR the BeforeUpdate with the code bit not both.
I’ve tried using an IF statement too but failed.
Any other ideas before I pull the plug on this?
As always, thanks for all advise and ideas.
Oops, sorry I got sidetracked and forgot this. I'll try to take a look tomorrow (this weekend), and if I forget again, please send me an email to remind me. Cheers!
 

Mark_

Longboard on the internet
Joined
Sep 12, 2017
Messages
2,112
What I would do, personally, is have your "ValidationOfControl" return a value that is used by both BeforeUpdate AND your CloseButton.

For BeforeUpdate you wind up with something along the lines of
Code:
IF ValidationOfControl() = FALSE THEN Cancel = True
For your button you get
Code:
IF ValidationOfControl() = TRUE THEN DoCmd.Close
In the first, if your validation does not return TRUE then you know to cancel. In the second, if your validation does return TRUE then you know to close the form.
 

theDBguy

I’m here to help
Joined
Oct 29, 2018
Messages
7,577
Bingo! That's what I was actually planning to do this weekend. If the OP can implement that now, then we'll be good to go. Thanks!
 

Weekleyba

Silver Supporter
Joined
Oct 10, 2013
Messages
219
Thanks guys!
I will give this a go when I return to my computer.
Appreciate all the help!!
 

Weekleyba

Silver Supporter
Joined
Oct 10, 2013
Messages
219
Thank you DBguy! That works great.
Of course that leads to the next step that I'm trying to figure out and that is checking to see if the all the controls are empty and if so, clicking the Close button allows the form to close.
I could present a separate thread but I think it might be fine here.

Here's what I've tried in the form F_Project, CloseButton On Click event.

#Private Sub Command5CloseButton_Click()
'This is for the close button.
If CheckForEmpty(Me) = False Then
DoCmd.Close
ElseIf ValidationOfControl(Me) = False Then
DoCmd.Close
End If
End Sub#

And the public function CheckForEmpty,

#Public Function CheckForEmpty(frm As Access.Form) As Boolean
Dim ctl As Control
Dim boolEmptyBox As Boolean

For Each ctl In frm.Controls
If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Then
If (IsNull(ctl.Value) Or ctl.Value = "") Then
boolEmptyBox = True
End If
End If
Next ctl
CheckForEmpty = boolEmptyBox
End Function#

I can't figure out why it doesn't work.
Any suggestions?
Thanks.
 

theDBguy

I’m here to help
Joined
Oct 29, 2018
Messages
7,577
Hi. You shouldn't need two separate functions doing the same thing. You should be able to modify the first one to do both of what you want to do.
 

theDBguy

I’m here to help
Joined
Oct 29, 2018
Messages
7,577
Hi. If you get stuck, post your updated db, so we can take a look. Good night.
 

Weekleyba

Silver Supporter
Joined
Oct 10, 2013
Messages
219
Ok for some reason I'm not able to get this to work.
More then likely my addition to the global module is not done correctly.
I've read numerous other posting trying to figure this out but...alas I'm at a loss. :banghead:
Can you help?
See attached db with my attempt.
Thanks in advance!
 

Attachments

theDBguy

I’m here to help
Joined
Oct 29, 2018
Messages
7,577
Ok for some reason I'm not able to get this to work.
More then likely my addition to the global module is not done correctly.
I've read numerous other posting trying to figure this out but...alas I'm at a loss. :banghead:
Can you help?
See attached db with my attempt.
Thanks in advance!
Hi. Can you please zip your file before attaching? Otherwise, I'll have to wait until I get home tonight to download it. Thanks.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom