Cancel = True not working??

[slenish]

Hello everyone,

Well I am having a small problem with canceling an action and keeping the focus on the text box with the problem.

To give some details as to what im trying to do. What i have is a form where I have an error box pop up if you are going to duplicate a record on the table. I have the event in the BeforeUpdate of the text box. So if there is a duplication (cancel = true). Now from everything I have been reading if you have the cancel = true function it should keep the focus on the current text box that has a problem but on my form it is not. It just alerts you with the pop up box and then continues on to the next text box. I want to make it so that if the error box pops up (which you can only press ok on) then it will keep the focus of the text box that the error occured instead of continuing on to the next text box.

Any help would be great thanks so much :)

code im working with

Private Sub Text9_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_Text9_BeforeUpdate

If DCount("*", _
          "[MainTable]", _
          "(([Date]=#" & Format(Me.Text3, "mm/dd/YYYY") & "#) AND (" & _
          "[Block1]=" & Chr(34) & Me.Text9 & Chr(34) & "))") > 0 Then
  
    LResponse = MsgBox("A Record Of This Block On This Date Already Exists.", vbOKOnly + vbQuestion, "Record Duplication Error")
     LResponse = vbOK
        Cancel = True
        End If

Exit_Text9_BeforeUpdate:
    Exit Sub

Err_Text9_BeforeUpdate:
    MsgBox Err.Description
    Resume Exit_Text9_BeforeUpdate

[Jerry Maiapu]

Try:

 Private Sub Text9_BeforeUpdate(Cancel As Integer)
 On Error GoTo Err_Text9_BeforeUpdate
  
 If DCount("*", _
          "[MainTable]", _
           "(([Date]=#" & Format(Me.Text3, "mm/dd/YYYY") & "#) AND (" & _
           "[Block1]=" & Chr(34) & Me.Text9 & Chr(34) & "))") > 0 Then
  
      MsgBox"A Record Of This Block On This Date Already Exists.", vbOKOnly + vbQuestion, "Record Duplication Error"
         Cancel = 1
          Me.Text9.setfocus
         Exit sub
         End If
  
 Exit_Text9_BeforeUpdate:
     Exit Sub
  
 Err_Text9_BeforeUpdate:
     MsgBox Err.Description
     Resume Exit_Text9_BeforeUpdate

[slenish]

Hi Jerry,

Thanks for the reply. I tried your idea and still am getting the same problem but I took what you added and then moved the entire code to the AfterUpdate function and now it works....not sure why it wont work in the before update but it works now :D

Thanks again for the help

[Jerry Maiapu]

...not sure why it wont work in the before update but it works now :D

I did not notice that too.
Well if you see (logic) Before update will fire before putting the value in Text9. After Update will fire after Text9 has a value.

For example if you what to stop people entering value into Text9 if say Text8 is blank then
you can use Before update of Text9 to check if there exist a value in Text8.

In your case, you want to perform something after Text9 has a value.

Anyway, I'm glad my solution was helpful

Jerry