If adezii's code complains about the recordset object
then you might want to check your references to see that the DAO type library is selected
From the code page of your form
choose the tools menu and the references option
The type library you need is
Microsoft DAO 3.6 Object Library
Not really caring how it's done....I'll just be copying and pasting the output.
And there's nothing wrong with that. If it works and performance is acceptable. By the way I wasn't trying to suggest that this is not the best way, just highlighting that I was simply answering your question, as asked.
Compare adezii's code and my pseudo code and you will see similarities.
I like to write my programs with pseudo code (commented out of course)
and then write the actual code around the pseudo code...automatic comments :)