Driving me crazy: is null problem

[rnickels]

I have a client contact information form which requires the user to enter details about the tennis player.

I want to ensure the user enters the name of the new player on the form and for the user to not be able to exit the form unless a playername is entered.

I have the following code on the before update and on exit of the control to ensure that a user cannot tab to the next textbox unless data is entered:

Private Sub playername_BeforeUpdate(Cancel As Integer)
If IsNull(Me.[PlayerName]) = True Or Len(Me.[PlayerName]) < 1 Then
MsgBox "You must enter the Player's name!", vbOK, "Tennis Center Business Manager 2007"
Cancel = True
End If
End Sub

I have kind of sorted this out but there is one problem.
I have command button on the same form that using the on click event checks to see if the playername is null (ie empty) and then if it is not empty closes the form. However if the playername has had data entered and then deleted for whatever reason, my on click code does not see the text box as empty and closes the form against my wish without showing the msgbox I want to appear. Here is my on click vba code:

Private Sub OLEUnbound199_Click()
If Len(Me.[PlayerName]) = O Or IsNull(Me.PlayerName) = True Or Nz(Me.PlayerName) Then
MsgBox "You must enter the Player's name!", vbOK, "Tennis Center Business Manager 2007"
Cancel = True
Exit Sub
End If
DoCmd.OpenForm "Switchboard", acNormal
DoCmd.Close acForm, "Client Information", acSaveYes

 

[petehilljnr]

Get rid of the Or Nz(Me.PlayerName) part.

Try using:

If IsNull(Me.PlayerName) or Me.PlayerName = "" Then

Also, I'd suggest you put your code on the On_Close event and cancel closing the form should your required fields not be complete, or alternatively, make the Player Name field in the (I'm guessing) Players table to be a Required = Yes.

Regards,
Pete.

 

[rnickels]

Thanks Pete

Thanks Pete,

I have a follow up question.

If I have a Cancel Command button on the same players contact form.
Is there a way to overide the code that requires a data entry in the playername. Basically I need a "way-out" for the user if they open this form by mistake and need to go back to the switchboard. Right now I can exit the form Ok but in doing so I get 2 msg box one after the other due to my vba code on the on exit and before update and on exit event for the playername text box.

How can I prevent these msg boxes from appearing and close the form without a problem.

Thanks in advance,
Rob

 

[petehilljnr]

If the PlayerName is a required field at the table level, it is hard to trap the error from within the form, but it is pretty much fail safe (I think).
So - let's forget about that approach and try this one instead.

Add the following code to the form:

Function PlayerName_OK() As Boolean

If IsNull(Me.PlayerName) or Me.PlayerName = "" Then
   PlayerName_OK = True
Else
   PlayerName_OK = False
End If

On the Lost Focus event on the PlayerName control, put the following code:

If Not PlayerName_OK Then
   Msgbox "You gotta enter a player name"
   Me.PlayerName.SetFocus
End If

On the On Close event of the Form place the following code:

If Not PlayerName_OK Then
   If Msgbox("You haven't entered a player name.  Are you sure you want to exit? (data will be lost)",vbYesNo) = vbYes Then
      'open the forms you want opened etc... the form will close automatically
   Else
      Me.PlayerName.SetFocus
      Cancel = True
   End If
End If

I'm doing this off the top of my head - but looks ok at first glance!

Regards,
Pete