navigation and validation on continous form

[Jhon Hendri via AccessMonster.com]

Hi, I am new here and also very new to vba access.

I am writing a continous form and I want to do validation on user input for
one of the field, say Type (which is the PK in db) if it already exists.

form fields for each record fields: Type, Desc, Display, DeleteButton

Currently i am using form BeforeUpdate event and check against the db. so
the check is done right after the user get off the record.

After the checking,
if it already exists, i need the focus to be back on Type.
if not, i need the focus to be natural which is to the next record. i have
been trying to set the focus to the next record, but failed...

pasted below is the beforeUpdate event. Could someone help me??
and could also advise me a better way of doing such validation in continous
form??

thanks,
jhon



Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim db As database
Dim rs As DAO.Recordset

Set db = CurrentDb()

'Set focus here because the prev it throws error saying that to access the
property of a control, it needed to be focused first.
MediumType.SetFocus

If IsNull(Me!MediumType.Text) Or Me!MediumType.Text = "" Then
MsgBox "Type cannot not be empty value"
Exit Sub
End If

'Get the number of record
Set rs = db.OpenRecordset("SELECT Count(Type) AS Total FROM [Channel Type]
WHERE Type ='" & MediumType.Text & "' ")

'Check if exists
If rs!Total >= 1 Then
MsgBox "Type has already existed in the database.", vbOKOnly, "Type
Existed!"

Else
'
'how do i make the navigation of the record flows naturally again..
'because prev the focus is already set to the MediumType
'
End If

End Sub

 

[bill beran]

Why do you use the .setfocus method? You don't need to set the focus t
get values from the fields. To assign the value of any field from th
current record on your form: my_variable = me.field_name. If you nee
to set the focus to automatically navigate to another field, use th
AfterUpdate event