Remove row(record) from recordset

[yamus]

Hi
My questions is simple(i hope the answer woule be the same )
Can i remove an item (a row) from the recordset only(not from the database table)?

 

[CJ_London]

depends on the recordset, if it is a DAO recordset then no, because it links directly to the underlying table(s)

same with ADO, however with ADO you can disconnect the recordset from the table(s), after which you can delete a record from the recordset and not touch the table.

 

[arnelgp]

if you mean delete a Record, Yes!

 

[Sun_Force]

depends on the recordset, if it is a DAO recordset then no, because it links directly to the underlying table(s)

same with ADO, however with ADO you can disconnect the recordset from the table(s), after which you can delete a record from the recordset and not touch the table.

How about if he alters the filter?
Or add a filter like "WHERE PK NOT IN (thispk)"

I'm new to Access and trying to learn. Not arguing...

 

[MajP]

I doubt you actually meant what you asked, but if you did the answer is yes. However, this is rarely done. Normally you would create a new recordset based on a criteria excluding the record. You normally would not start with a recordset and remove a record. You can however do this.

If you actually have a recordset, you can remove a record from it.

Dim rs as dao.recordset

Private Sub Command35_Click()
  Dim rs As DAO.Recordset
  Set rs = Me.Recordset
  MsgBox rs.RecordCount
  rs.Filter = "productID not in (1,4)"
  Set rs = rs.OpenRecordset
  rs.MoveLast
  rs.MoveFirst
  MsgBox rs.RecordCount
End Sub

In that Demo I start with a recordset with 79 records. I exclude 2 from the recordset and apply the filter directly to the recordset. As I said it is rarely done and more efficient to open a new recordset based on a criteria.

 

[arnelgp]

How about if he alters the filter?
Or add a filter like "WHERE PK NOT IN (thispk)"

very simple yet it will do the requirement on post#1.

 

[CJ_London]

How about if he alters the filter?

possibly, the question is simple, but not clear!

 

[gemma-the-husky]

What is being said is that the recordset for the query needs to select the data you want.
So you could have a field in the query for showitem, with a criteria of "true"
Now if the field showitem is in the table you are querying then you can deselect the item with code like

update thetable set showitem = false where recordid = whatever AND THEN
recordset.requery (which will now exclude the item where showitem is now set to false)

you have to be careful though, as multiple users might be doing this, and records that shouldn't be set to hidden for you, might become hidden by the actions of others - so you need to consider how you can process the data so your system works for all users. You could have a temporary table with all the records you want, and the temporary table carries the showitem flag. This way the view you get is different to the view others get, as the records you hide are just the ones you want to hide, and each user is independent - but it's all more complicated to programme.

so now you get
update temporarytable set showitem = false where recordid = whatever AND THEN
recordset.requery (which will now exclude the item where showitem is now set to false, according to your private control table)

but it's not a simple solution as you requested - because it's just not a simple request.

 

[Pat Hartman]

Can i remove an item (a row) from the recordset only(not from the database table)?

Only if you have criteria that can be used in the Form's RecordSource query to exclude it OR criteria that you can use in a filter to exclude it.

Why exclude ONE record. That sounds like a design issue.