Creating, Opening and Closing Connection and Recordset Objects

Most of my programming experience predates object-oriented languages and while I am familiar with variable scopes I would really appreciate some advice on the placement of declarations and open/close statements when it comes to ADODB connections and recordsets. The attached code is my first try at populating a user form with notes from two text fields in a SQL view. It works just fine but the final code would have many more options such as sales history (probably in a listbox) and various other details about any customer chosen from hundreds or thousands of rows.

I realize that computers are much, much faster than they were when I was in my programming prime (such as it was) but I've always tried to find the most-efficient way to code whenever possible and it seems redundant to open and close the connection so often. I can understand populating the recordset one customer at a time but even there I wonder if dimensioning the objects outside this subroutine would be better.

Probably the simplest way to put it is it seems to me that there could be a function to which the strCustID could be passed which would repopulate the recordset with that cust_id's row. If in fact it's just as fast to do it all each time as in the attached code that's fine. Again, I just want to learn to code efficiently in VBA.

Sorry for the long-winded question. And thanks in advance for any advice you have.

David

Please Login or Register  to view this content.