Problem with delete record

Hi,

I have a database (DAO) that has two tables - Users (Fields are User ID, and Password), and Categories (Fields are UserID, Category, and SubCategory).

The following is the code I use to delete a Category...


Private Sub cmdCatDelete_Click()

Set db = OpenDatabase(App.Path + "\" + "mydb.mdb")
Set rs = db.OpenRecordset("SELECT Categories.Category From Categories WHERE Categories.Category = " + Chr$(34) + cmbCategory.Text + Chr$(34) + ";")

rs.MoveFirst
Do Until rs.EOF

With rs
.Delete
End With

rs.MoveNext
Loop
db.Close

End sub


This works fine except that if two differrent users have named a category the same name, it deletes the other users category also.

How can I delete just the category of the user that is logged in?

I hope I explained this well enough, and I appreciate any help I can get on this.

Thanks,
Ron

When you create the recordset, include the UserID in the WHERE clause. You can use the logged in user's UserID for this.

Also, instead of creating a recordset, you can directly issue an SQL Delete statement.

Code:

---

strSQL = "DELETE FROM Categories WHERE Category = '" & Something & "' AND UserID = '" & Something & "'"
db.Execute strSQL

---

Change the tablename and fieldnames along with types as necessary. I am assuming both the Category and UserID to be text fields.

.

Hey Honeybee,

Thanks for the input! Although I didn't try your idea, it got me
thinking. Since this is my first database, and I have been using
recordsets...I thought I would stick with them.

Not only did I need to include the UserID in the WHERE clause,
but also in the SELECT clause. This is what eventually worked...


Set rs = db.OpenRecordset("SELECT UserID AND Category From Categories WHERE UserID = " + Chr$(34) + cmbUserID.Text + Chr$(34) + " AND Category = " + Chr$(34) + cmbCategory.Text + Chr$(34) + ";")


Again, thanks a lot, and have a good one,
Ron