A particular field cannot be empty

[sajitk]

I am new to access and i am trying to build a database for enntering Subscription details of a particular journal. Wat I want is that FirstName field on the form should not be left blank. If the user does not enters the First Name and moves to the field, there should be msgbox prompt, prompting the user that the name cannot be left blank and the cursor should be back on the Firstname text box.

I have tried using the foll code:

Private Sub FirstName_LostFocus()
If IsNull(Me.FirstName) Then
MsgBox "First Name cannot be left blank"
Me.FirstName.SetFocus
'Exit Sub
End If
End Sub
 

But the problem is that the cursor does not comes back to FirstName text box. Can anyone help me on this....

Sajit

[Delerna]

add
[code]
me.FirstName.se lected
[code]

immediately after the line

Me.FirstName.SetFocus
 

I think its selected, intellisense will point you to the correct property

I am new to access and i am trying to build a database for enntering Subscription details of a particular journal. Wat I want is that FirstName field on the form should not be left blank. If the user does not enters the First Name and moves to the field, there should be msgbox prompt, prompting the user that the name cannot be left blank and the cursor should be back on the Firstname text box.

I have tried using the foll code:

Private Sub FirstName_LostF ocus()
If IsNull(Me.First Name) Then
MsgBox "First Name cannot be left blank"
Me.FirstName.Se tFocus
'Exit Sub
End If
End Sub

But the problem is that the cursor does not comes back to FirstName text box. Can anyone help me on this....

Sajit

[missinglinq]

Your code is correct, but there's a major flaw in your logic here, Sajit! You want to insure that the first name field has data in it, but you're tying your validation code to the LostFocus event of your first name textbox. What happens if your user never tabs/clicks into the first name textbox and it never has focus ? Your user could then save the record without a first name being entered! When checking to see if a field is empty, you need to place your code at the form level, in the form's BeforeUpdate event. This way, if the user simply skips over the textbox completely, they'll still be forced to fill in the box before the record can be saved!

Remove the code from the LostFocus event and place it here, adding the line Cancel = True, which essentially tells Access that the record is not ready to be saved yet.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.FirstName) Then
  MsgBox "First Name cannot be left blank"
  Cancel = True
  Me.FirstName.SetFocus
End If
End Sub
 

Welcome to TheScripts!

Linq ;0)>

[missinglinq]

BTW, the Selected property in Access VBA, at least up thru version 2003, is only associated with listboxes, not textboxes. I realized after addressing where the validation code needs to go that I didn't explain why the SetFocus didn't work on the FirstName_LostF ocus event.

One of the peculiarities of Access is that there are some events, such as LostFocus and OnExit, where one would assume that the control has, indeed, lost focus, and thus focus could be reset to the control. In fact, in these events the focus is sort of betwixt and between! The control doesn't have focus (i.e. the cursor doesn't show within the textbox) but you can't set focus to it either! What you have to do, when you run into this situation, is to set focus to another control then set focus back on the desired control. Just another of the mysteries of Access!

Linq ;0)>

[sajitk]

Thank you,

it works....

Sajit

[missinglinq]

Glad we could help!

Linq ;0)>

[truthlover]

Hi Linq

I'm having the same issue. I understand what you've explained, but I was hoping for a solution that didnt require the person entering the data to go enter the whole form before they found that they are missing required data (after all, setting the properties as required will throw up a message if you try to proceede).

I also tried using the validation property but I cant get that to work for me either.

My database is supposed to go live today, so your help would be greatly appreciated.

Thanks!

Your code is correct, but there's a major flaw in your logic here, Sajit! You want to insure that the first name field has data in it, but you're tying your validation code to the LostFocus event of your first name textbox. What happens if your user never tabs/clicks into the first name textbox and it never has focus ? Your user could then save the record without a first name being entered! When checking to see if a field is empty, you need to place your code at the form level, in the form's BeforeUpdate event. This way, if the user simply skips over the textbox completely, they'll still be forced to fill in the box before the record can be saved!

Remove the code from the LostFocus event and place it here, adding the line Cancel = True, which essentially tells Access that the record is not ready to be saved yet.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.FirstName) Then
MsgBox "First Name cannot be left blank"
Cancel = True
Me.FirstName.SetFocus
End If
End Sub
 

Welcome to TheScripts!

Linq ;0)>

[missinglinq]

You can do it on a control by control basis, but in order to do so, you have to insure that the user actually goes to the control(s) and the only way to do this is to

1. Set focus on a specific control anytime you move to a record
2. Use the OnExit event for that control to insure data is entered and then force the cursor to move to the next control in line
3. Repeat this process for each and every textbox until all of the required textboxes have been cycled thru. If you have option groups or comboboxes/listboxes where a choice has to be made, they'll have to be included also.

Remember, as shown in the code below, when coming to the final required control, do not set focus anywhere! This is needed so that the user can then use the mouse to click on New Record, Exit, etc.

Doing this for a few controls might not be bad, but it could get quite tedious for a large number of them.

You're probably going to aggravate your end users no end. You'll be effectively keeping them from using the mouse to navigate around the form, forcing them to enter data in the order that you think it should be entered, not neccessarily in the order that they feel it should be entered.

Also, if you need a way for a user to dump a record without saving it, you're going to have to provide a way for them to do so without using the mouse to click on a button.

Private Sub Form_Current()
  TextBox1.SetFocus
End Sub
 
Private Sub TextBox1_Exit(Cancel As Integer)
 If IsNull(Me.TextBox1) Then
  MsgBox "TextBox1 cannot be left blank"
  Cancel = True
 Else
  TextBox2.SetFocus
 End If
End Sub
 
Private Sub TextBox2_Exit(Cancel As Integer)
 If IsNull(Me.TextBox2) Then
  MsgBox "TextBox2 cannot be left blank"
  Cancel = True
 Else
  ComboBox1.SetFocus
 End If
End Sub
 
Private Sub ComboBox1_Exit(Cancel As Integer)
 TextBox3.SetFocus
End Sub
 
Private Sub TextBox3_Exit(Cancel As Integer)
 If IsNull(Me.TextBox3) Then
  MsgBox "TextBox3 cannot be left blank"
  Cancel = True
 Else
  'If this is the last control, you do not force focus anywhere
 End If
End Sub

Linq ;0)>

[truthlover]

Actually, the field in question is only the third field on the form so if I force two controls, it shouldnt be a big deal. I would think it would be better than an annoying popup happening *every* time they come to the field or going through the whole form only to have to dump it in the end.

I'll give it a try and see how it goes.

Thanks!

You can do it on a control by control basis, but in order to do so, you have to insure that the user actually goes to the control(s) and the only way to do this is to

1. Set focus on a specific control anytime you move to a record
2. Use the OnExit event for that control to insure data is entered and then force the cursor to move to the next control in line
3. Repeat this process for each and every textbox until all of the required textboxes have been cycled thru. If you have option groups or comboboxes/listboxes where a choice has to be made, they'll have to be included also.

Remember, as shown in the code below, when coming to the final required control, do not set focus anywhere! This is needed so that the user can then use the mouse to click on New Record, Exit, etc.

Doing this for a few controls might not be bad, but it could get quite tedious for a large number of them.

You're probably going to aggravate your end users no end. You'll be effectively keeping them from using the mouse to navigate around the form, forcing them to enter data in the order that you think it should be entered, not neccessarily in the order that they feel it should be entered.

Also, if you need a way for a user to dump a record without saving it, you're going to have to provide a way for them to do so without using the mouse to click on a button.

Private Sub Form_Current()
TextBox1.SetFocus
End Sub
 
Private Sub TextBox1_Exit(Cancel As Integer)
If IsNull(Me.TextBox1) Then
MsgBox "TextBox1 cannot be left blank"
Cancel = True
Else
TextBox2.SetFocus
End If
End Sub
 
Private Sub TextBox2_Exit(Cancel As Integer)
If IsNull(Me.TextBox2) Then
MsgBox "TextBox2 cannot be left blank"
Cancel = True
Else
ComboBox1.SetFocus
End If
End Sub
 
Private Sub ComboBox1_Exit(Cancel As Integer)
TextBox3.SetFocus
End Sub
 
Private Sub TextBox3_Exit(Cancel As Integer)
If IsNull(Me.TextBox3) Then
MsgBox "TextBox3 cannot be left blank"
Cancel = True
Else
'If this is the last control, you do not force focus anywhere
End If
End Sub

Linq ;0)>

[truthlover]

Well, I implemented the code and it seems to be working pretty well. You're right that it would be annoying to have more than a few controls forced, but it's only effecting 3 fields and it's still much better than my previous solution.

Thanks again for your help!

Actually, the field in question is only the third field on the form so if I force two controls, it shouldnt be a big deal. I would think it would be better than an annoying popup happening *every* time they come to the field or going through the whole form only to have to dump it in the end.

I'll give it a try and see how it goes.

Thanks!