Full Version: Can't exit from requiring operator entry into control
UtterAccess Forums > Microsoft® Access > Access Forms
Retired
Simple name & address form. When user selects to enter a new record, I want to force them to enter a last name before continuing. Arrived at this code which works well as far as demanding an input, but prevents the user from exiting. Is there a way to permit the user to exit from entering a new record mode if they choose Cancel?
CODE
Private Sub txtLname_LostFocus()
  Dim Int1 As Integer
  If IsNull(Me.txtLname) Or Me.txtLname = "" Then
    Int1 = MsgBox("Please Enter A Last Name", vbOKCancel)
    If Int1 = 2 Then ' Operator choose to cancel
      ' need code to exit from the "new record" mode.
    End If
    Me.txtJrSr.SetFocus
    Me.txtLname.SetFocus
  End If
End Sub

Thanks,
Jay Witt
theDBguy
My suggestion would be to put the code in the BeforeUpdate event of the form and check if the current record is a new record. Then check for the missing value. To cancel the addition, you can set the Cancel value to True.
For example:
If Me.NewRecord Then
If Len(Me.txtlname & vbNullString) = 0 Then
If MsgBox("Please enter a last name", vbOKCancel, "Enter Lastname") = vbCancel Then
Me.Undo
Else
Me.txtLname.SetFocus
End If
Cancel = True
End If
End If
(untested)
Hope that helps...
Retired
Thanks DBG,
The form uses three related tables and I'd rather stop the user before they tab thru the last and first name controls without entering any data. For after this point related records will be created in the other two tables.
It seemed that an effective method would be to hold them from continuing until the names are entered. Tried the undo and cancel lines earlier to no effect. At least not the effect of exiting out of the "create a new record" mode.
Thanks for your suggestion.
Jay
theDBguy
Sorry about that. It was more of a general response because I don't see your form or understand its function. What is the first control that gets the focus? How do you handle a user who clicks around instead of tabbing through your form? If after the user enters their last name and decides to cancel the form, how do they do that? Knowing the answers to these questions and others like it will help me give you a better suggestion.
Good luck.
Retired
DBguy,
My apologies. I misled you by saying it was a simple name/address form. Actually, it's not overly complicated but it does use two other related tables. My main concern was that a user would skip over entering a last name or first name or both and enter only address info (located on a related table). Thus creating an almost worthless member record.
What is the first control that gets the focus?
The form's "On Current" event makes sure that the txtLname control is the first to receive focus.
How do you handle a user who clicks around instead of tabbing through your form?
Oselected the control's "On Lost Focus" event thinking that however the user bypassed the txtLname control, they would be asked to enter a last name.
If after the user enters their last name and decides to cancel the form, how do they do that?
This is where my logic failed and where I asked for help. Regardless of how the user would try to exit the control or try to exit the process, the On Lost Focus event kept returning them back to the txtLname control. Is there, perhaps, a better event to have used?
Any thoughts you may have about this would be greatly appreciated.
Jay
theDBguy
I will have to see your table/form setup to be able to give you a possible solution. Are we talking about a form/subform scenario here?
Retired
DBguy,
I'm appreciative you are offering to take time to look over the table and form design and understand that sometimes that is the best way to get into a problem. But neither the tables or the form is the problem and is not the issue. What I wanted to determine was whether there is a way to escape from the "trapped" situation as presented in my first message.
I've come to the conclusion that there is no way, except to enter a last name, which is less than ideal.
I've since come up with a solution which isn't as demanding and doesn't trap the user. It is this ...
CODE

Private Sub txtLname_LostFocus()
  If IsNull(Me.txtLname) Or Me.txtLname = "" Then
    Dim Int1 As Integer
    Int1 = MsgBox("In Order to Create a New Record" & vbCr & _
                  "You Must Enter a Last Name." & vbCr & vbCr & _
                  "Do You Want To Continue?", vbYesNo)
  End If
  If Int1 = 6 Then
    Me.txtJrSr.SetFocus
    Me.txtLname.SetFocus
  End If
  If Int1 = 7 Then
    Requery  ' exits new rec mode and returns to view existing recs mode
  End If
End Sub

Thanks for your help.
Jay
theDBguy
Glad to hear you got a solution you're comfortable with.
nother solution might be to use an InputBox to ask for the last name before you move the form to a new record. If the user cancels the InputBox or leaves it empty, then you can exit out of the code (leaving the form at the current record, instead of moving it back to the first record with the requery).
Good luck with your project.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.