How to delete a record from recordset but not from table

[Will The Gray]

I am trying to load certain data from a table into a recordset and then working with it without affecting the data in the tables.

The idea is to post all records in a table (given a certain criteria) randomly one by one, and when one has been displayed, it needs to be deleted from the recordset (but not the table!) so it does not randomly show up again.

My problem thus being, if I use rst.delete it will not just delete the record in my recordset, but also the data in the table. Is it possible to create such a unlinked set of records where you can modify as you please without affecting database data?

I use the following code to load up my recordset:

 Set rst = db.OpenRecordset("SELECT * FROM tblVragen WHERE categoriecode = '" & strCategoriecode & "'") 

thanks!

[amitpatel66]

Why dont you try to order by <something> and loop the the records in recordset till the last record is reached and process those one by one?

[Will The Gray]

Thanks for the incredibly quick reply ;). Well, order by only grants a limited way of randomizing things doesn't it? Or can you order in more ways than I know?

Let me explain the full thing, I'm trying to make an application where a question pops up, the user answers, it says it's either a good or wrong answer and moves on to the next question. Everytime the user launches this application the questions have to be in a different random order. So I put in a randomizer to select a record randomly and display that.

The problem with that is that the same record can pop up multiple times. That's why I thought I'd just delete a record from my recordset that's already been displayed so it wouldn't pop up again. But of course I don't want to lose that question from the table.

[hype261]

@Will The Gray
Have you thought of making a temporary table to store the recordset that way it doesn't matter if they are deleted or not?

[Will The Gray]

Yeah thought about it, but hoped there would be an easier way ;)

[hype261]

@Will The Gray
How is your randomizer set up?

I am assuming that your randomizer is a function in vba that randomizes the order in the recordset. If so couldn't you just do Order By the randomizer. Here is a very simple sample that I put together

SELECT test.pk, test.test, randomizer([pk]) AS Random
FROM test
ORDER BY randomizer([pk]);

radomizer is just a function in VBA that I have built

Public Function randomizer(pk As Integer) As Integer
 
  randomizer = Rnd * 100 * pk
 
End Function

This should change the order of the questions every time and you can use a forward only cursor to go from first to last.

[NeoPa]

Apply a filter to your recordset, or include the filter in the WHERE clause.

How your recordset is used determines whether or not the recordset needs to be re-opened.

[Will The Gray]

Must admit I'm not entirely following. Tried the randomizer thing but it says the function isn't defined, although it is set as a public function.

Is it possible to give me a more concrete example using my code below? The categorycode of the question is selected beforehand, as you can see I just move to a random record with this code (and after an answer is given, it does it again). I understand I'm supposed to just order the recordset randomly and move through it instead of doing it this way, but I'm still in the dark on how to do that exactly. As far as I know ordering by simply takes a value and orders it ascending or decending from there on, which wouldn't be a strong randomizer as one question would always follow before or after another.

 
 
Set rsQuestions = db.OpenRecordset("SELECT * FROM tblQuestions WHERE Categorycode = '" & strCategorycode & "'")
 
    rsQuestions.MoveLast
    MaxQuestions = rsQuestions.RecordCount
    intNumber = CInt(Int(MaxQuestions * Rnd() + 1))
    rsQuestions.MoveFirst
    rsQuestions.Move (intNumber)
 

[Will The Gray]

Never mind I figured it out by using the order by randomizer thing.

Thanks a bunch :)

[NeoPa]

A new thread with a question related to code posted in here was split from here and has been set up as Questions on Randomising.