Thread: Drawing Data from A Record ADO VBA

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:

Type ContactInfo
    ContactID as Long
    FirstName as String
    SubDealer as Boolean
End Type

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

Still looking for advice, is there any other information you would need?