Looking for the correct form control

baadams

Board Regular
Joined
Mar 2, 2006
Messages
134
I want to verify that a user has filled out 3 controls on a form before they can leave the form or record. I can't seem to find the correct control. Is there a control that I can use to check that certain fields have been populated before they leave the record? Thanks for the help............
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
A simple If...Then statement should be what you are looking for. Something like this could work:
Code:
Private Sub cmd1_Click()
Dim allowExit As Boolean

allowExit = True
If IsNull(Me.txt1.Value) Then
    allowExit = False
End If
If IsNull(Me.txt2.Value) Then
    allowExit = False
End If
If IsNull(Me.txt3.Value) Then
    allowExit = False
End If

If allowExit = False Then
    MsgBox ("You have not entered all of the correct information.")
End If

End Sub

This only gives an example of three textboxes and a command button to show you how the code would work. It depends on how you want to exit the form or move to a different record how you would implement this.
 
Upvote 0
Montez, Thanks for the example. My code works but I don't know when it needs to run. If I use the BeforeUpdate event it seems to work, but when I set the focus to the control that wasn't filled out you can just click somewhere else and you don't get a message box becaue you haven't changed anything so the BeforeUpdate event isn't triggered.

What I did was put the same logic on the LostFocus event for the control so if the user decides not to fill it out again they will be warned. But it will still let you navigate to the next record?

I'd like to keep the user from leaving the control before it's been filled out, but I don't know the event to perform this action.

These forms are being used in datasheet view, if that makes any difference.

Thanks Again...
 
Upvote 0
You can put this in the BeforeUpdate and add this little piece of code right after the MessageBox within that last If...Then statement:
Code:
Cancel = True
So it should look like:
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim allowExit As Boolean

allowExit = True
If IsNull(Me.txt1.Value) Then
    allowExit = False
End If
If IsNull(Me.txt2.Value) Then
    allowExit = False
End If
If IsNull(Me.txt3.Value) Then
    allowExit = False
End If

If allowExit = False Then
    MsgBox ("You have not entered all of the correct information.")
    Cancel = True
End If

End Sub

This will stop the update from happening, but beware - any information already entered will not be saved if they do decide to exit out anyways. This might be perfectly fine, but you need to be aware of it.
 
Upvote 0

Forum statistics

Threads
1,224,502
Messages
6,179,126
Members
452,890
Latest member
Nikhil Ramesh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top