checking data in a field and then setting focus

[Chris]

I have a form where one field is a combo field. Also on the form is a
subform that has a query that shows all the records related to a customer.

What I need to do is look at one item in particular and if it's INT (for
initial) to run a query against all records related to that customer. If it
finds an INT already existing I need it to go back to that combo filed and
say pick another choice because one already exists.

I can get the query to report back okay (if a record exists or not) but I
never can seem to get the setfucus to work. It always seems to go to the
next field.

What I need to do do is never allow you to enter a new entry if an INT
already exists. In other words never pass go till they select another
choice.

Oh the subtable that contains the INT shows it to have a value of "6".

Here is the code i use.

Thanks in advance Chris C

If (cboContactType) = "6" Then
Dim rstCheck As New ADODB.Recordset
Dim strSQL As String
strSQL = "SELECT tblContactNotes.ContactType,
tblContactNotes.MaternityID FROM tblContactNotes " & _
"WHERE tblContactNotes.ContactType=6 AND
tblContactNotes.MaternityID = " &
[Forms]![frmClient]!chldMaternity![MaternityID]
rstCheck.Open strSQL, CurrentProject.Connection, adOpenStatic,
adLockReadOnly
If rstCheck.RecordCount > 0 Then
cboContactType.SetFocus
MsgBox "You already have one Interview listed. Please select
another type of Contact."

Else

End If
End If

 

[Rick Brandt]

I have a form where one field is a combo field. Also on the form is a
subform that has a query that shows all the records related to a
customer.
What I need to do is look at one item in particular and if it's INT
(for initial) to run a query against all records related to that
customer. If it finds an INT already existing I need it to go back to
that combo filed and say pick another choice because one already
exists.
I can get the query to report back okay (if a record exists or not)
but I never can seem to get the setfucus to work. It always seems to
go to the next field.

What I need to do do is never allow you to enter a new entry if an INT
already exists. In other words never pass go till they select another
choice.

Oh the subtable that contains the INT shows it to have a value of "6".

Here is the code i use.

Thanks in advance Chris C

If (cboContactType) = "6" Then
Dim rstCheck As New ADODB.Recordset
Dim strSQL As String
strSQL = "SELECT tblContactNotes.ContactType,
tblContactNotes.MaternityID FROM tblContactNotes " & _
"WHERE tblContactNotes.ContactType=6 AND
tblContactNotes.MaternityID = " &
[Forms]![frmClient]!chldMaternity![MaternityID]
rstCheck.Open strSQL, CurrentProject.Connection,
adOpenStatic, adLockReadOnly
If rstCheck.RecordCount > 0 Then
cboContactType.SetFocus
MsgBox "You already have one Interview listed. Please
select another type of Contact."

Else

End If
End If

Any time you want to run validation against a control you should use the
BeforeUpdate event of that control. If the validation fails you can display a
message and set the Cancel argument of the event to True. Thta way the control
never loses focus so there is no need to try to set the focus back to it.