Problem with SetFocus

[robkiolbasa]

I am trying to set the focus of a form when some enters a duplicate
value. The logic is correct, but the focus goes to the next field.
All I want to do is blank the field and toggle back to that same
field. The form is a datasheet view of another table. I would like
duplicates to be allowed, but the end user is prevented from entering
them. I am lost as to why this isn't working. Code below:

Function checkTag() As Integer

Dim db12 As Database
Set db12 = CurrentDb
Dim rs12 As Recordset
Set rs12 = db12.OpenRecordset(Me.Name, dbOpenDynaset)
rs12.MoveFirst
rs12.FindFirst "[ServiceTag] = " & Chr(34) & ServiceTag & Chr(34)
If rs12.NoMatch() Then
checkTag = 1
Else
checkTag = 0
End If

Set rs12 = Nothing

End Function



Private Sub ServiceTag_AfterUpdate()

Call checkTag

If checkTag = 0 Then
MsgBox "The Service Tag is already in use."
ServiceTag.Value = ""
ServiceTag.SetFocus
End If

End Sub

 

[Allen Browne]

Use the BeforeUpdate event of the control.
It provides a Cancel argument.
If you set this to True, the user cannot get out of the control until the
fix or undo the value.

This kind of thing:

Private Sub ServiceTag_BeforeUpdate(Cancel As Integer)
Dim strWhere as String
Dim varResult as Variant

With Me.ServiceTag
If IsNull(.Value) Or (.Value = .OldValue) Then
'do nothing
Else
strWhere = "ServiceTag = " & Me.ServiceTag
varResult = DLookup("ServiceTag", "Table1", strWhere)
If Not IsNull(varResult) Then
Cancel = True
MsgBox "Duplicate!" & vbCrLf & _
"Enter a different value, or press Esc to undo."
End If
End If
End With
End Sub

For more help with DLookup(), see:
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html

 

[robkiolbasa]

Use the BeforeUpdate event of the control.
It provides a Cancel argument.
If you set this to True, the user cannot get out of the control until the
fix or undo the value.

This kind of thing:

Private Sub ServiceTag_BeforeUpdate(Cancel As Integer)
Dim strWhere as String
Dim varResult as Variant

With Me.ServiceTag
If IsNull(.Value) Or (.Value = .OldValue) Then
'do nothing
Else
strWhere = "ServiceTag = " & Me.ServiceTag
varResult = DLookup("ServiceTag", "Table1", strWhere)
If Not IsNull(varResult) Then
Cancel = True
MsgBox "Duplicate!" & vbCrLf & _
"Enter a different value, or press Esc to undo."
End If
End If
End With
End Sub

For more help with DLookup(), see:
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html

I am trying to set the focus of a form when some enters a duplicate
value. The logic is correct, but the focus goes to the next field.
All I want to do is blank the field and toggle back to that same
field. The form is a datasheet view of another table. I would like
duplicates to be allowed, but the end user is prevented from entering
them. I am lost as to why this isn't working. Code below:

Function checkTag() As Integer

Dim db12 As Database
Set db12 = CurrentDb
Dim rs12 As Recordset
Set rs12 = db12.OpenRecordset(Me.Name, dbOpenDynaset)
rs12.MoveFirst
rs12.FindFirst "[ServiceTag] = " & Chr(34) & ServiceTag & Chr(34)
If rs12.NoMatch() Then
checkTag = 1
Else
checkTag = 0
End If

Set rs12 = Nothing

End Function

Private Sub ServiceTag_AfterUpdate()

Call checkTag

If checkTag = 0 Then
MsgBox "The Service Tag is already in use."
ServiceTag.Value = ""
ServiceTag.SetFocus
End If

End Sub- Hide quoted text -

- Show quoted text -

Works like a charm!!! thanks!