Prevent moving to the next control

[Peter Bellamy]

I am testing the date entered in AfterUpdate of a control and want the user to re-enter it if conflicts with existing data.

I have coded the testing and bring up a yes/no message box if it does not comply and want the focus to stay on the control if they answer No (7).

I have tried using SetFocus but it is ignored, any suggestions?

cheers
pnb

 

[pbaldy]

You want the before update event, and Cancel = True:

http://www.baldyweb.com/BeforeUpdate.htm

 

[Peter Bellamy]

Paul, thanks.
The control has the value for the before update event but it will not accept the SetFocus command (back on its self as it were)

pnb

 

[gemma-the-husky]

if you cancel the before update event, and then exit

cancel=true
exit sub


the control will remain active, and you don't need a setfocus

 

[pbaldy]

You said the code was in the after update event, not the before. Which is it? What's the exact code?

 

[Peter Bellamy]

Thanks Dave,
That however puts me in a loop I cannot break, not even to close the form, unless I provide a suitable date?

pnb

 

[bob fitz]

That however puts me in a loop I cannot break, not even to close the form, unless I provide a suitable date?

Surely that that's not the case if you press the "Esc" key, to remove the enterd data.
Perhaps this would work for:

cancel=true
Me.ActiveControl.Undo
exit sub

 

[gemma-the-husky]

Thanks Dave,
That however puts me in a loop I cannot break, not even to close the form, unless I provide a suitable date?

pnb

that should be the idea

force acceptable input, or press escape to cancel the entry.

 

[Peter Bellamy]

This is the code I am using:

Private Sub ImpDate_BeforeUpdate(Cancel As Integer)
Dim db As Database
Dim rec As Recordset
Dim answer As String

Set db = CurrentDb()
Set rec = db.OpenRecordset("SetupData")

rec.FindFirst "param_current = -1"
Me.DateLast = rec("param_inc_date")

rec.Close

If Me.ImpDate <= Me.DateLast Then
    answer = MsgBox("Future date expected, this date is equal to or older than last update, continue?", vbYesNo, "Date conflict")
        If answer = 7 Then '7 = No, Yes =6
'            Me.ImpDate = ""
            Cancel = True
            Exit Sub
        End If
End If

Exit_BeforeUpdate:
    Exit Sub

Err_BeforeUpdate:
    MsgBox Err.Description
    Resume Exit_BeforeUpdate
End Sub

The date test is to warn not to exclude the entry of the same date, that is why I have a Yes/No option in the message. If the user decides that the same date is NOT acceptable I want to them to have the chance to re-enter the date. If they decide they want the same date then they are allowed to proceed to the next field.

pnb

 

[bob fitz]

Hi
You may like to try this:

Private Sub ImpDate_BeforeUpdate(Cancel As Integer)
Dim db As [URL="http://www.access-programmers.co.uk/forums/showthread.php?p=1075875#"][COLOR=darkgreen]Database[/COLOR][/URL]
Dim rec As Recordset
 
Set db = CurrentDb()
Set rec = db.OpenRecordset("SetupData")
 
rec.FindFirst "param_current = -1"
Me.DateLast = rec("param_inc_date")
 
If Me.ImpDate <= Me.DateLast Then
    If MsgBox("Future date expected, this date is equal to or older than last update, continue?", vbYesNo, "Date conflict") = vbYes Then
            Cancel = True
            Me.ActiveControl.Undo
            Exit Sub
        End If
End If
 
Exit_BeforeUpdate:
    Set db = Nothing
    rec.Close
 
    Exit Sub
 
Err_BeforeUpdate:
    MsgBox Err.Description
    Resume Exit_BeforeUpdate
End Sub

 

[Peter Bellamy]

Thanks everyone, that now works a treat!