Drawing Data from A Record ADO VBA

**split_atom18** · 11-03-2010, 01:03 PM

So here's the deal:

Normally in my forms, in each procedure I write an ADO/SQL open a recordset and take the specific data I want from that recordset and close it.

I do this for every sub on a form.

Here is what I would like to do:

Create a Function in my modContactUtils module, that will when given a "ContactID" make all of the Contacts Info available(probably public variables) and I get to then select what I want from all that info. Close it and put it away.

Here is what I know:

It is not effective to open a recordset, return one value, close the recordset, and repeat this for each of the 8 random pieces I want to pull from the contacts info.
(This is what I currently have written) I do this with Select Case. Basically the function "GetContactInfo(ContactID,ItemToReturn)". This only returns 1 item each time it opens the recordset.

I don't know enough about Type Statements:

Please Login or Register  to view this content.

I believe this basically allows me to quickly declare my variables if I have a lot of them. I think this might be on the right track, but am at a loss at the moment.

Any Suggestions/Recommendations would be fantastic. I have read conflicting statements about declaring lots of public variables. I just want to have smart well thought out code in my database. (it works as is, just refining it at the moment)

Thanks in advance,

Dan