Problem with delete record

[rdcody]

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

[honeybee]

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.

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.

.

[rdcody]

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