Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-05-2019, 03:18 PM   #1
Weekleyba
Registered User
Silver Supporter
 
Join Date: Oct 2013
Location: North Dakota
Posts: 156
Thanks: 88
Thanked 0 Times in 0 Posts
Weekleyba is on a distinguished road
Closing a Form after Validation

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#

Weekleyba is offline   Reply With Quote
Old 11-05-2019, 05:23 PM   #2
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,558
Thanks: 58
Thanked 1,433 Times in 1,414 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Closing a Form after Validation

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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 11-05-2019, 05:49 PM   #3
Weekleyba
Registered User
Silver Supporter
 
Join Date: Oct 2013
Location: North Dakota
Posts: 156
Thanks: 88
Thanked 0 Times in 0 Posts
Weekleyba is on a distinguished road
Re: Closing a Form after Validation

Pardon my inexperience here but, do mean a Public function vs the Private ones?
I’ll give that a try. Thanks.

Weekleyba is offline   Reply With Quote
Old 11-05-2019, 06:06 PM   #4
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,558
Thanks: 58
Thanked 1,433 Times in 1,414 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Closing a Form after Validation

Quote:
Originally Posted by Weekleyba View Post
Pardon my inexperience here but, do mean a Public function vs the Private ones?
I値l 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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 11-15-2019, 12:15 PM   #5
Weekleyba
Registered User
Silver Supporter
 
Join Date: Oct 2013
Location: North Dakota
Posts: 156
Thanks: 88
Thanked 0 Times in 0 Posts
Weekleyba is on a distinguished road
Re: Closing a Form after Validation

I've been at this for awhile and I need some help on this...

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
Attached Files
File Type: accdb DFM Database_9.accdb (820.0 KB, 6 views)
Weekleyba is offline   Reply With Quote
Old 11-15-2019, 12:23 PM   #6
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,558
Thanks: 58
Thanked 1,433 Times in 1,414 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Closing a Form after Validation

Hi. I think I can take a look at your file this weekend (if no one else beats me to it).
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 11-15-2019, 12:25 PM   #7
MickJav
Newly Registered User
 
Join Date: Nov 2005
Location: Margate
Posts: 1,007
Thanks: 73
Thanked 52 Times in 45 Posts
MickJav will become famous soon enough
Re: Closing a Form after Validation

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...d.php?t=306487


Look at the code behind the employees OK button.

__________________
All open code examples and free projects are only available from:

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Creating a new project then add styles to it download open code example from:

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
MickJav is offline   Reply With Quote
The Following User Says Thank You to MickJav For This Useful Post:
Weekleyba (11-22-2019)
Old 11-22-2019, 12:15 PM   #8
Weekleyba
Registered User
Silver Supporter
 
Join Date: Oct 2013
Location: North Dakota
Posts: 156
Thanks: 88
Thanked 0 Times in 0 Posts
Weekleyba is on a distinguished road
Re: Closing a Form after Validation

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.
Weekleyba is offline   Reply With Quote
Old 11-22-2019, 12:33 PM   #9
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,558
Thanks: 58
Thanked 1,433 Times in 1,414 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Closing a Form after Validation

Quote:
Originally Posted by Weekleyba View Post
I知 still at a loss on this.
I知 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致e 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!
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 11-22-2019, 12:57 PM   #10
Weekleyba
Registered User
Silver Supporter
 
Join Date: Oct 2013
Location: North Dakota
Posts: 156
Thanks: 88
Thanked 0 Times in 0 Posts
Weekleyba is on a distinguished road
Re: Closing a Form after Validation

Thanks DBguy!
Appreciate it.
Weekleyba is offline   Reply With Quote
Old 11-22-2019, 01:40 PM   #11
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 2,112
Thanks: 20
Thanked 400 Times in 393 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: Closing a Form after Validation

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.
Mark_ is offline   Reply With Quote
The Following User Says Thank You to Mark_ For This Useful Post:
Weekleyba (11-22-2019)
Old 11-22-2019, 01:44 PM   #12
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,558
Thanks: 58
Thanked 1,433 Times in 1,414 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Closing a Form after Validation

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!
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
Weekleyba (11-22-2019)
Old 11-22-2019, 06:56 PM   #13
Weekleyba
Registered User
Silver Supporter
 
Join Date: Oct 2013
Location: North Dakota
Posts: 156
Thanks: 88
Thanked 0 Times in 0 Posts
Weekleyba is on a distinguished road
Re: Closing a Form after Validation

Thanks guys!
I will give this a go when I return to my computer.
Appreciate all the help!!
Weekleyba is offline   Reply With Quote
Old 11-23-2019, 11:58 AM   #14
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,558
Thanks: 58
Thanked 1,433 Times in 1,414 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Closing a Form after Validation

Quote:
Originally Posted by Weekleyba View Post
Thanks guys!
I will give this a go when I return to my computer.
Appreciate all the help!!
Hi. See if this works for you...
Attached Files
File Type: zip DFM Database_9.zip (134.5 KB, 4 views)
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
Weekleyba (11-24-2019)
Old 11-24-2019, 07:04 PM   #15
Weekleyba
Registered User
Silver Supporter
 
Join Date: Oct 2013
Location: North Dakota
Posts: 156
Thanks: 88
Thanked 0 Times in 0 Posts
Weekleyba is on a distinguished road
Re: Closing a Form after Validation

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.

Weekleyba is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Validation before closing form Danick Forms 13 05-20-2012 02:32 PM
Closing a form when closing a report Bryan Reports 3 05-07-2012 11:12 AM
Form Level Validation - Closing Dairy Farmer Forms 4 10-04-2010 05:49 AM
Cancel form from closing if Validation fails speakers_86 Forms 8 07-11-2010 03:45 PM
Cancel validation rule message boxes on closing form Fiona H Forms 2 10-26-2006 02:42 PM




All times are GMT -8. The time now is 01:21 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World