zero length string

[Gazza2]

Please can someone help with what i thought would be a simple problem but has turned out to be very annoying.

I have a form bound to a table with about ten textboxes with the first one (accountcode) as the primary key in the table being required. I have tried the following code in the before and afterupdate events of the textbox with no luck

If Me.Accountcode = "" Or IsNull(Me.Accountcode) Then
MsgBox "A valid accountcode must be entered before continuing", vbOKOnly
Cancel = True
Me.Undo
End If

I want the message box to pop up if the user presses the enter or tab key before entering data into the textbox.

At the moment it just moves to the next field on the form and then gives me the primary key cannot contain a null value at the end of the form which i would like to stop.

Thanks in advance
Gareth

 

[pbaldy]

The problem is that the update events don't occur if the user simply tabs through (nothing has been updated). I would use the before update event of the form rather than an individual control.

 

[CraigDolphin]

As an alternative mechanism use the keyup event of the controls to call a sub to validate that the accountcode exists

something like:

Public Sub validate(mycode As Integer)
Dim validflag As Boolean

Select Case mycode
    Case 9 'tab
        validflag = True
    Case 13 'enter
        validflag = True
    Case Else 'some other key
        validflag = False
End Select

If validflag = True Then
    If Me.AccountCode& "" = "" Then
        MsgBox "A valid accountcode must be entered before continuing", vbInformation
        Me.AccountCode.SetFocus
    End If
End If

End Sub

Which you would call from the keyup event of each of your controls like

Private Sub YourControlName_KeyUp(KeyCode As Integer, Shift As Integer)
Call validate(KeyCode)
End Sub

This should fire regardless of whether the record was being updated or not.

 

[KenHigg]

Looks like the best option would be to put the code in an independant sub proceedure and call it from the text box exit event and the before update event - ?

 

[pbaldy]

I suppose "best" is a matter of opinion but Pat and I agree on using the before update event of the form (only). You can't necessarily count on individual control events even firing, since users can use the mouse to skip controls completely.

 

[boblarson]

I suppose "best" is a matter of opinion but Pat and I agree on using the before update event of the form (only). You can't necessarily count on individual control events even firing, since users can use the mouse to skip controls completely.

Agreed here.

 

[CraigDolphin]

I would never argue with Pat et al

I only suggested it as the OP specifically wanted the event to fire when the keypress occured to move between controls. It was my thought that the form's before update did not necessarily fire until you either change record, or explictly save the record. Perhaps I'm mistaken about that?

But I agree it would be much more efficient, and a better method for ensuring data integrity, to only have the code fired from one event that will always fire before the record is saved. And the best candidate for that is clearly the before update event of the form.

 

[KenHigg]

I suppose "best" is a matter of opinion but Pat and I agree on using the before update event of the form (only). You can't necessarily count on individual control events even firing, since users can use the mouse to skip controls completely.

But aren't you missing the fact that he wanted a message to appear after the user attempted to tab out of the control without a valid entry? So if the user attempts to leave, or tab out of the control you need something to fire and if they never even entered the control and left it blank while trying to save the record you'd need an event to fire.

 

[gemma-the-husky]

if your users are just not entering anything in the text box then your before update event doesnt fire, because they haven't changed anything

as an aside you can test the entered string by

If nz(Accountcode,vbnulsltring)=vbnullstring Then
MsgBox "A valid accountcode must be entered before continuing", vbOKOnly
Cancel = True
End If

which is tidier than testing two conditions,.

---------
now a choice of ways of testing for a blank field

you could use similar code to test this in the form's before update event

or since the field is required the insert will fail, and you wil get a standard error message, so you could trap that with the forms on error event

 

[CraigDolphin]

which is tidier than testing two conditions

Agreed. Which is why I had used

If Me.Accountcode & "" = "" then

Which handles both nulls and zls Just another way to skin the same cat

 

[KenHigg]

So when are you two saying you should fire the validation code? I think it should fire from the text box exit event and from before update event of the form.

 

[CraigDolphin]

I think it depends on the exact behavior desired by the OP.

If it is fired from the before update event then the record can never be saved to the table without the necessary information in the first place, so logically there's really no need to check existing records that have not been subsequently edited. And if the record is edited then the before update event will be fired when the user changes record or closes the form/db.

But if the OP really wants the validation code to fire while tabbing/moving between controls (before the beforeupdate events gets triggered) then you'd likely need to use the on exit events also, as you suggest.

 

[KenHigg]

...But if the OP really wants the validation code to fire while tabbing/moving between controls (before the beforeupdate events gets triggered) then you'd likely need to use the on exit events also, as you suggest.

Thank you. I thought I was missing something here...

 

[ShaneMan]

Agreed. Which is why I had used

If Me.Accountcode & "" = "" then

Which handles both nulls and zls Just another way to skin the same cat

The one I tend to use the most is: Trim(Nz(Me.ControlName,"")) = ""

Ms. Pat or Paul,
Does the BeforeUpate event work the same if you use a command button to close instead of the close button provided with the forms properties? The reason I ask is I always use my own close buttons and when I use the BeforeUpdate to validate it traps everything ok but the cancel does seem to work cause the form will go ahead and close and will not set the focus to the control that is in violation.

 

[boblarson]

The one I tend to use the most is: Trim(Nz(Me.ControlName,"")) = ""

Ms. Pat or Paul,
Does the BeforeUpate event work the same if you use a command button to close instead of the close button provided with the forms properties? The reason I ask is I always use my own close buttons and when I use the BeforeUpdate to validate it traps everything ok but the cancel does seem to work cause the form will go ahead and close and will not set the focus to the control that is in violation.

Shane:

For that you have to trap in the form's Unload event. What I will typically do is put a boolean in the form's declaration section and then I set
Cancel = blnCancel
in the unload event and if it has been set to true it will cancel. Then, in the Before Update event, I will do all of my validation and then if I need to cancel the update and return to the form, I issue

Cancel = True
blnCancel = True

and then the unload event will cancel as well.