How to clear text boxes on a form

[Rich]

Hello,

I am trying to do some simply data entry on a form. I am entering a
username, firstname and last name. After entering the username, the
Before_Update event fires at which time I check to see if a value already
exists in the table. If it does, pop a message box, then clear the userid
field so that the text box is blank.

Here is the code:

If DCount("Userid", "tblUsers", "[Userid] = Userid") > 0 Then
MsgBox ("This user already exists. Please correct the CDSID.")
frmUsers.UserID = ""
frmUsers.FName = ""
frmUsers.LName = ""
End If

The message box pops OK, then I get "Run-tmie error '424': Object required.

If I use Me.Userid, I get "Compile error: Method or data element not found".

What am I doing wrong, and how can I clear the fields so that data enter can
be corrected and continue?

TIA,
Rich

 

[Proko]

Hi Rich,

Any chance of seeing the before update code? I personally would trigger the
code using a save button on the form. The code should check that each field
has been filled then open a recordset to check for an existing entry. If its
a new entry then add the record; if not then display your message box and set
field values back to null. (Not the zero length string "")
Proko

 

[Proko]

Also is frmUsers an unbound form?
Proko

 

[Proko]

To trap if a UserID has been used before try this:

Private Sub UserID_AfterUpdate()

Dim db As Database
Dim rst As Recordset
Dim strsql As String

Set db = CurrentDb
strsql = "SELECT * FROM UserTableName WHERE UserTableName.UserID = '" &
Me.UserID & "';"

Set rst = db.OpenRecordset(strsql, dbOpenDynaset)

If rst.EOF Then
'this a new UserId
Else
'User ID already exists
MsgBox ("User ID already exists")
Me.UserID = Null
Me.FName = Null
Me.LName = Null
End If
rst.Close

End Sub