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.selected
[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_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

[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_LostFocus 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!

[sajitk]

Hi Linq,

this is actually an old problem.

i have a table called beneficiary which has a field called cost (interger, default value =0). in the form this is the last field for entry. the user enters the value and presses the save button.The record gets saved.

i have written the following code in the forms_before update ()

[PHP]On Error GoTo Err_frm_GotFocus

If Me.cost = 0 Then
MsgBox "Please enter the cost of Solar Lantern", vbInformation, "Beneficiary Entry"
Cancel = True
Me.cost.SetFocus
GoTo Exit_save_Gotfocus
End If


Exit_frm_Gotfocus:
Exit Sub

Err_frm_GotFocus:
MsgBox Err.Description
Resume Exit_frm_Gotfocus[/PHP]

what happens is that, when the saved is clicked, the record is saved and the ADD button gets enabled and the save button gets disabled. when i click the Add button the focus shifts to the cost control. i think is due to the like
[PHP]Me.cost = 0[/PHP] because each time a new form is loaded, the default value for this field is 0.

The writing the abovecode in OnExit () would be tedious at the moment because there are quite a few controls in the form.

Would be great if you could help me on this.
sajit

Glad we could help!

Linq ;0)>

[missinglinq]

If you're saying that when you hit the ADD button the new record is pulled up and the focus is in the Cost field, this is normal behavior for Access. Anytime you move from one record to another, the first control to receive focus in Record #2 will be the same control that last had focus on Record #1.

To solve this you simply need to set focus on the first control you want to have focus, every time you move to a different record:

Private Sub Form_Current()
   Me.FirstTextbox.SetFocus
End Sub

Linq ;0)>