Detecting a value was deleted from ms access text box before entering value in Table (1 Viewer)

[HelpMoses76]

Hello everyone

I was looking to validate that all the text boxes were populated on the ms access form. The code should be able to detect if the value was entered and then deleted too .
Any ideas ? I have tried using "" , " " (space between quotes),Null etc.

No luck so far.

Thanks for your help

Moses

 

[Isaac]

The code should be able to detect if the value was entered and then deleted too .

So there are two ways to successfully fulfill the rules:
1) enter a value
2) enter a value and then delete it back out of the control.

Really?

 

[HelpMoses76]

So there are two ways to successfully fulfill the rules:
1) enter a value
2) enter a value and then delete it back out of the control.

Really?

This is just to ensure that nothing gets missed . I work with an interesting set of people.

 

[Gasman]

This is just to ensure that nothing gets missed . I work with an interesting set of people.

If they delete it, wouldn't the control be empty?

 

[HelpMoses76]

If they delete it, wouldn't the control be empty?

Yes and how would I detect it- use what code to detect it - when I hit save button ?

 

[Isaac]

I guess you'd have to write code in the after update event of every control in order to count them?
But this is a positively odd situation

 

[Gasman]

I guess you'd have to write code in the after update event of every control in order to count them?
But this is a positively odd situation

Wouldn't you check in the BeforeUpdate of the form?
I thought that was the best place for form validation?

 

[HelpMoses76]

Wouldn't you check in the BeforeUpdate of the form?
I thought that was the best place for form validation?

I really do not know that . Thats why i am asking.

 

[Isaac]

Wouldn't you check in the BeforeUpdate of the form?
I thought that was the best place for form validation?

But they are wanting to know if text was entered and then deleted out of a control.
If all you do is code in beforeupdate, and look at the current value of a control, how will you know that text was entered and then removed?

 

[Gasman]

But they are wanting to know if text was entered and then deleted out of a control.
If all you do is code in beforeupdate, and look at the current value of a control, how will you know that text was entered and then removed?

Ah. I am reading it as control just being empty. Unable to see the significance of whether a user deleted an entry as long as they replace it.
Perhaps the o/p can clarify?

Could the .Text/OldValue property come into play?

 

[Cronk]

Seems to me that the OP doesn't care if data was deleted and then replaced - just that all text boxes need to be populated.

 

[The_Doc_Man]

It is possible that you would be unable to catch all cases of this.

When a textbox gains focus for the first time after either navigation to a different record or creation of a new record, there will be a value in the textbox called .OldValue, and it will retain the value of the record at the moment it became "current" on the form.

When a user edits that textbox, the .Value changes but .OldValue does not. In the Form_BeforeUpdate event, you could verify that a change had occurred for that textbox. However, ....

It is possible for someone to change something, TAB out and do something, then do a SHIFT-TAB to back-step to that first textbox. If that happens, and if the person re-instates the previous value, the Form_BeforeUpdate event routine would say that nothing had occurred.

If you have someone who tabs into the box, changes the box contents, but before leaving the box decides to erase what was done and retype the original value, you also would have a hard time being sure that any change had occurred. If and ONLY if you had a control_Change event routine, you could track what was done in that box, but would be very busy handling the events.

Therefore, you need to think about what you REALLY want to know, and then ask that question. The ONLY way to know that someone had diddled in the textbox NO MATTER WHAT they did, you would have to look to control_Change events, which would lead to serious overhead.

 

[Gasman]

It is possible that you would be unable to catch all cases of this.

@The_Doc_Man

How about saving the control values to variables in the Current event, then compare?

 

[Isaac]

Ah. I am reading it as control just being empty. Unable to see the significance of whether a user deleted an entry as long as they replace it.
Perhaps the o/p can clarify?

Could the .Text/OldValue property come into play?

That's why I told OP their requirement was very odd.

He said, "The code should be able to detect if the value was entered and then deleted too .". I have never heard of a requirement like that in my life.

 

[HelpMoses76]

fixed..


If IsNull(txtDescription.Value) Or _
Len(Nz(txtDescription.Value, vbNullString)) = 0 Or _
Len(txtDescription.Value & vbNullString) = 0 Then
'IsNullOrEmpty = True
MsgBox "Description is empty"
Exit Sub
Else

IsNullOrEmpty = False
MsgBox "Description is not empty"

End If

 

[HelpMoses76]

It is possible that you would be unable to catch all cases of this.

When a textbox gains focus for the first time after either navigation to a different record or creation of a new record, there will be a value in the textbox called .OldValue, and it will retain the value of the record at the moment it became "current" on the form.

When a user edits that textbox, the .Value changes but .OldValue does not. In the Form_BeforeUpdate event, you could verify that a change had occurred for that textbox. However, ....

It is possible for someone to change something, TAB out and do something, then do a SHIFT-TAB to back-step to that first textbox. If that happens, and if the person re-instates the previous value, the Form_BeforeUpdate event routine would say that nothing had occurred.

If you have someone who tabs into the box, changes the box contents, but before leaving the box decides to erase what was done and retype the original value, you also would have a hard time being sure that any change had occurred. If and ONLY if you had a control_Change event routine, you could track what was done in that box, but would be very busy handling the events.

Therefore, you need to think about what you REALLY want to know, and then ask that question. The ONLY way to know that someone had diddled in the textbox NO MATTER WHAT they did, you would have to look to control_Change events, which would lead to serious overhead.

fixed..


If IsNull(txtDescription.Value) Or _
Len(Nz(txtDescription.Value, vbNullString)) = 0 Or _
Len(txtDescription.Value & vbNullString) = 0 Then
'IsNullOrEmpty = True
MsgBox "Description is empty"
Exit Sub
Else

IsNullOrEmpty = False
MsgBox "Description is not empty"

End If

 

[gemma-the-husky]

If someone is entering a new record, and enters then delete text, I can't see you can use existing events to trap this directly.
You can compare a control's oldvalue with the newvalue but for a new control the old value is null/blank so it doesn't help.

You can reject an entry if a field that should not be blank is blank, by a validation process, or using a table field rule.
You can stop a user leaving a control if it can't be blank

You could use the control's onchange event to manage the process in detail, but to what end?

 

[Gasman]

fixed..


If IsNull(txtDescription.Value) Or _
Len(Nz(txtDescription.Value, vbNullString)) = 0 Or _
Len(txtDescription.Value & vbNullString) = 0 Then
'IsNullOrEmpty = True
MsgBox "Description is empty"
Exit Sub
Else

IsNullOrEmpty = False
MsgBox "Description is not empty"

End If

If txtDescription is already being tested for Null, why are you using the NZ() function?

 

[The_Doc_Man]

How about saving the control values to variables in the Current event, then compare?

There is a perfectly good property called .OldValue that is available for bound controls. You are right that you would need variables for unbound controls. However, my question was actually trying to determine the scope of change to be tracked.

 

[Pat Hartman]

Do your validation in the correct event. That way, you only have to do it once. Cancel the update and exit the event when you encounter an error. In the BeforeUpdate event of the FORM:

If Me.SomeField & "" = "" Then
    Msgbox "This field is required.", vbOKOnly
    Cancel = True
    Me.SomeField.SetFocus
    Exit Sub
End If

In addition, if a text field is required, ALWAYS set its AllowZeroLengthString property to False to let the database engine ensure that no data is omitted.