Setting the focus to the first field on the form

[gaugust]

When a new record is entered on a form I am checking the form recordsetclone
to be sure no other record has the same Study number. If a record is found
with the study number I display a message box and ask the user to enter a new
study number, however when I set the focus back to the study number, which is
the first field on the form, it will not set the focus back on the study
number field. The focus is being set to the next field on the form. I tried
to set the focus to other fields on the form and the set focus works. Any
idea why the set focus will not work for the first field? Here is my code:

With Me.RecordsetClone
.MoveFirst
.FindFirst "[StudyN] = " & Forms!frmPatient.txtStudyn.Value
If .NoMatch Then
Else
MsgBox "Study Number already found, please enter new study
number", vbExclamation, "Duplicate Study Number"
Forms!frmPatient!txtStudyn.SetFocus
End If
End With

 

[Al Campagna]

gaugust,
You don't mention what triggers this code...
You should be using the BeforeUpdate event of your StudyN control. Before update is
cancellable.

Private Sub StudyN BeforeUpdate(Cancel as Integer)

With Me.RecordsetClone
.MoveFirst
.FindFirst "[StudyN] = " & Forms!frmPatient.txtStudyn.Value
If .NoMatch Then
Else
MsgBox "Study Number already found, please enter new study
number", vbExclamation, "Duplicate Study Number"
Cancel = True StudyN.Undo
End If
End With

That should take you back to StudyN with the dupe value you just entered deleted.

I would suggest trying to figure out a method to auto-magically assign a StudyN that is
not a dupe in the first place, rather than program around duplicate entries. For ex., a
DefaultValue for StudyN that involves a Dlookup against the StudyN table to find the next
available (non-dupe) value.
Better to prevent mistakes, than code around them.
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

When a new record is entered on a form I am checking the form recordsetclone
to be sure no other record has the same Study number. If a record is found
with the study number I display a message box and ask the user to enter a new
study number, however when I set the focus back to the study number, which is
the first field on the form, it will not set the focus back on the study
number field. The focus is being set to the next field on the form. I tried
to set the focus to other fields on the form and the set focus works. Any
idea why the set focus will not work for the first field? Here is my code:

With Me.RecordsetClone
.MoveFirst
.FindFirst "[StudyN] = " & Forms!frmPatient.txtStudyn.Value
If .NoMatch Then
Else
MsgBox "Study Number already found, please enter new study
number", vbExclamation, "Duplicate Study Number"
Forms!frmPatient!txtStudyn.SetFocus
End If
End With