Field Validation that controls Cursor Advancement 2

[Xeseus]

I would like to create an event which is initiated by entering a value into a specific field and hitting the tab or enter button. It would create a pop up that asks the user if they are certain that they want to keep the value entered. If the answer is “yes” then the newly entered value is retained and the cursor proceeds to the next field, if the answer is “no” then no value is entered and the cursor remains where it is. What would be the best way to do this?

 

[PHV]

You may use the BeforeUpdate event procedure of the control and play with its Cancel parameter.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TheAceMan1]

How are ya Xeseus . . .

In parallel with [blue]PHV[/blue], copy/paste the following function to the code module od the form:

[blue]Public Function ValCtl(ctlName As String) As Boolean
   Dim Msg As String, Style As Integer, Title As String
   
   If Trim(Me(ctlName) & "") <> "" Then
      Msg = "Are you sure you want to keep your current Data Entry?"
      Style = vbQuestion + vbYesNo
      Title = "Resonse Required! . . ."
      
      If MsgBox(Msg, Style, Title) = vbNo Then
         Me(ctlName).Undo
      Else
         ValCtl = True
      End If
   End If

End Function[/blue]

Then in the [blue]BeforeUpdate[/blue] event of the controls of interest, copy/paste the following:

[blue]   If Not ValCtl("[purple][b]YourControlName[/b][/purple]") Then Cancel = True[/blue]

Thats it! . . .

See Ya! . . . . . .

Be sure to see thread181-473997

 

[Xeseus]

Thanks, this gives me a real good jump start. I do get an error, however:


Run-time error '2465':

StartUp can't find the field '928' referred to in your expression.

I pasted the function you gave me (without changing anything) into my code module. I then pasted the code you gave me into BeforeUpdate event of my control using my control name, sngInsuredID, in place of "YourControlName". The debugger pointed to this line:

If Trim(Me(ctlName) & "") <> "" Then

Any ideas about what to do get beyond this?

 

[TheAceMan1]

Xeseus . . .

Post the code in the [blue]BeforeUpdate[/blue] event as you have it! . . .

See Ya! . . . . . .

Be sure to see thread181-473997

 

[Xeseus]

This is the code I'm using:

Public Function ValCtl(ctlName As String) As Boolean
Dim Msg As String, Style As Integer, Title As String

If Trim(Me(ctlName) & "") <> "" Then
Msg = "Are you sure you want to keep your current Data Entry?"
Style = vbQuestion + vbYesNo
Title = "Resonse Required! . . ."

If MsgBox(Msg, Style, Title) = vbNo Then
Me(ctlName).Undo
Else
ValCtl = True
End If
End If

End Function
____________________________________________________________
Private Sub sngInsuredID_BeforeUpdate(Cancel As Integer)
If Not ValCtl(sngInsuredID) Then Cancel = True
End Sub

 

[TheAceMan1]

Xeseus . . .

As suspected . . . don't forget the [blue]double quotes[/blue] in red [red]"[/red]

[blue]Private Sub sngInsuredID_BeforeUpdate(Cancel As Integer)
   If Not ValCtl([red][b]"[/b][/red]sngInsuredID[red][b]"[/b][/red]) Then Cancel = True
End Sub[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997

 

[Xeseus]

Thanks a million, it works great!!!

 

[Xeseus]

When I ran this I had problems when I deleted an entry and hit "Yes" to change the value then I received an error message. I remedied this by replacing <> " " with > 0. But I would like to a way to only invoke the pop-up for values being change, that is, when a brand new entry is being made then I don't want to see a pop-up asking me if I want to change an entry. How can I do this?

 

[PHV]

Check the NewRecord property of the form.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Xeseus]

That works great!!! Thanks PHV!!!

 

[Xeseus]

On the form I created I use list box fields that populate other tables. If an item isn’t in them I employ a command button that first opens a form to enter in the new item into the other table then the user is redirected back to the original form and uses a command button to refresh the list box. This process seems to circumvent the fix offered by the NewRecord property, specifically, the pop-up validations are initiated once the refresh button is used even if I dealing with a new record. Can anyone suggest a work-around?

This is the BeforeUpdate code that I am currently using:

Private Sub strLossAddr_BeforeUpdate(Cancel As Integer)
Dim strMsg As String
Dim iResponse As Integer

If Not (Me.NewRecord) Then

' Specify the message to display.
strMsg = "Do you wish to save the changes?" & Chr(10)
strMsg = strMsg & "Click Yes to Save or No to Discard changes."

' Display the message box.
iResponse = MsgBox(strMsg, vbQuestion + vbYesNo, "Save Record?")

' Check the user's response.
If iResponse = vbNo Then
' Undo the change.
Me!strLossAddr.Undo

' Cancel the update.
Cancel = True
End If
End If
End Sub

 

[Xeseus]

I'm sorry, my posting was in haste, I think I'm getting the hang of the VBA to some extent. Nonetheless, hopefully my solution can be of help to others. I created a radio button called "cmdRecordComplete" with a label that asks the question, "Record complete?". Then created a condition that it needed to be clicked for the pop-up validations to begin:

Private Sub strLossAddr_BeforeUpdate(Cancel As Integer)
Dim strMsg As String
Dim iResponse As Integer

If Me.cmdRecordComplete = True Then

If Not (Me.NewRecord) Then

' Specify the message to display.
strMsg = "Do you wish to save the changes?" & Chr(10)
strMsg = strMsg & "Click Yes to Save or No to Discard changes."

' Display the message box.
iResponse = MsgBox(strMsg, vbQuestion + vbYesNo, "Save Record?")

' Check the user's response.
If iResponse = vbNo Then
' Undo the change.
Me!strLossAddr.Undo

' Cancel the update.
Cancel = True
End If
End If
End If

End Sub

I can and should probably remove the other "If" statements. This is in no way an eloquent solution though, and can probably be done much better. If anyone has a better solution I'd really appreciate the contribution. Thanks in advance!