saving users data

[mitchney]

hello there! i am developing a registration form of users data...but i got a problem...what i want to happen is that if an id,name, username,password already exists msgbox will appear that it is already existing...here is my initial code..can u add it for what i want to happen with my form? i am using the adodb...

Private Sub cmdreg_Click()

If txtname.Text = "" Or cboposition.Text = "" Or txtusername.Text = "" Or txtpasswrd.Text = "" Then
MsgBox "Fill up the blank fields", vbInformation, "Info"
Else
If rs.State = adStateOpen Then rs.Close
rs.Open "Select * from users", cn, adOpenKeyset, adLockPessimistic
With rs
.AddNew
rs!id = txtid.Text
rs!Name = txtname.Text
rs!Position = cboposition.Text
rs!UserName = txtusername.Text
rs!Password = txtpasswrd.Text
rs!Date = Date
rs.Update
MsgBox "Registered", vbOKOnly
End With

txtname.Text = ""
cboposition.Text = ""
txtusername.Text = ""
txtpasswrd.Text = ""
txtpasswrd.Text = ""
txtrpasswrd.Text = ""
txtname.SetFocus
' MsgBox "password did not match", vbOKOnly (here i have a repeat password textbox..what i want also to happen is that if it mismatch..msgbox will appear that password mismatch..)
'txtrpasswrd.Text = ""
' txtrpasswrd.SetFocus
End If
End Sub

please add my code....

[debasisdas]

before inserting any record into the table take a count of records

Select count(*) from users where id = "txtid.Text"

proceed only if the count returns 0

[mitchney]

where should i insert this code you've given in my code...

[debasisdas]

before AddNew method.

You need to insert new record only if count is 0 (the user id does not exist), for this user id must be primary key of the table.

[mitchney]

do you mean like this?

with rs
Select count(*) from users where id = "txtid.Text"
.addnew
............
.........

pls tell me how? tnx for the help...

or cn u give me a revise code...tnx...

[debasisdas]

use 2 recordset object
open one with the count query and check the data in DB.

If it returns 0(that means the userid does not exists in the DB) then open the other recordset to insert new records.

If it returns 1(that means the userid already exists in the DB) prompt the user for new userid, password and then then open the other recordset to insert new records.