How do I stop a macro at the end of a recordset? (1 Viewer)

[Ali Edwards]

I have a macro which opens a qery in Datasheet view, selects the first record, copies it then pastes it into an unbound text box in a form. Then another macro runs instructing it to go to the next record in the query, copy and paste it into the same form, after the first record, inserting a comma between each record. This macro repeats and all works well but when it gets to the end of the recordset in the query it warns "Can't go to specified record" (because it has come to the end of the list)followed by three 'Halt' type messages. The form does complete so the job is done but how do I make a condition that will stop the macro when it gets to the end of the recordset so that I don't get all the warning messages? As the size of the recordset changes I can't give the macro a predefined repeat count.
Greatly appreciate any help with this.....

 

[Rich@ITTC]

Hi Ali Edwards

I don't know if what you want to do is possible using a Macro ... my instinct tells me that it almost certainly isn't.

I think you need to convert this Macro to Coding/VBA. You can then use BOF/EOF (beginning and end of recordsets) to programmitically find when you are at the end.

I am sure someone on this forum will come up with the required coding - it will take a bit of time to work out (and I haven't got the time at the moment to look into it). Good luck on this one.

Rich Gorvin

[This message has been edited by Rich@ITTC (edited 04-19-2001).]

 

[Ali Edwards]

Many thanks for your reply - I appreciate that. Unfortunately I don't know diddley about converting to code but I guess I'll have to start learninig!!

 

[charityg]

right click on your macro object. select save as export then save as visual basic module. this will convert your macro to code. add the code "do until rst.eof" after you declare what your recordset is, and add "loop" after your procedures. let me know if you need anymore help.