Access 2010 vba to open an ADODB recordset on form load/open

Do you have a Reference set to the Microsoft ActiveX Data Objects xx library (where "xx" is the version you want to use)?

You can set that in the VBA Editor window, under Tools - References. Scroll down to find the library. Generally you would use the "lowest" number version which still suits your needs. If all you're doing is opening a recordset, then use 2.5 or so.

Hi LSM,

Yep i have just set the reference library to 2.7. However, the code still never ran so i have made the following changes which works when the form is loaded, however, i cant work out how to reference the strRate_Pens throughout the form. I need to make the recordset available throughout the form at various stages


Sub Form_Open(Cancel As Integer)

    Dim RS As ADODB.Recordset
    Set RS = New ADODB.Recordset
   
    Dim strRate_Pens As Single
   
    RS.Open "SELECT Rate_Pens FROM tblRates", CurrentProject.Connection, adOpenStatic
   
        strRate_Pens = RS![Rate_Pens]
   
    RS.Close
    Set RS = Nothing
   
    MsgBox "Rate = " & strRate_Pens
   
End Sub

to test the availability of the recordset throughout the form i have used this:

Private Sub Command1405_Click()
   
    MsgBox "RS: " & RS![Rate_Pens]
   
End Sub

but i am getting run-timer error 91
Object variabe or with block variable not set

?? any ideas

You need to declare the RS variable at the Form level.

In the General Declarations section of the Form's code module (the very top portion of the code document), do this:

Dim RS As ADODB.Recordet

Then REMOVE that line in the Load event (be sure to leave the Set RS = New ADODB.Recordset, however).

Your RS object should then be available to the Form.

FWIW: I've found it best to use hidden textboxes for these sorts of things. If you're only referring to one or two data points from the recordset, then just create two new hidden textboxes and write the value from your recordset to those textboxes, and then refer to them as needed. The trouble with declaring recordsets in this manner is with unhandled errors - the RS object could be destroyed, and your code would need to account for that.

yep ive done what you have suggested as follows:

Dim RS As ADODB.Recordset (in general declarations at the top of the module)

This on the Sub Form Open(Cancel As Integer)
Set RS = New ADODB.Recordset
   
    Dim strRate_Pens As Single
    Dim strRate_PensSwitch As Single
   
    RS.Open "SELECT * FROM tblRates", CurrentProject.Connection, adOpenStatic
   
        strRate_Pens = RS!Rate_Pens
        strRate_PensSwitch = RS!Rate_PensSwitch
   
    RS.Close
    Set RS = Nothing
   
    MsgBox "Rate = " & strRate_Pens & " " & strRate_PensSwitch

This bit still doesnt work however.
Private Sub Command1405_Click()
   
    MsgBox "RS: " & RS!Rate_Pens
   
End Sub

IRO using hidden textboxes i will have 40+ RS values pulled from the recordset so hidden textboxes is probably not a viable option. However, with unhandled errors possibly destroying the recordset i may have to think of solutions fo rthis.

ive just taken the following lines out

RS.Close
    Set RS = Nothing

and the recordset is now available. I guess i will have to put the above lines on the onClose event of the form instead.

All works fine now but i am a little concerned about the recordset being destroyed on unhandled errors. On every event procedure i will have an error handler but have you any suggestions on how to deal with this issue?

I missed this originally, sorry:

You can't Close the recordset, since you're referring to it throughout the "life" of your form. Remove these two lines, or move them to your Form Close event:

 RS.Close
 Set RS = Nothing

Okay all seems to be working fine. I have put all the code below so that other ppl can see the entire solution a bit easier.

Please pass any comments iro error handling to avoid losing the recordset.

1) Global Declarations at top of form module:
Dim RS As ADODB.Recordset

2) the Forms Open Event
Sub Form_Open(Cancel As Integer)
If gcfHandleErrors Then On Error GoTo ErrorHandler

    Set RS = New ADODB.Recordset
    RS.Open "SELECT * FROM tblRates", CurrentProject.Connection, adOpenStatic
   
    MsgBox "Rate = " & RS!Rate_Pens & " " & RS!Rate_Pens2
   
ExitProcedure:
   Exit Sub

ErrorHandler:
    MsgBox "Error No. " & Err.Number & vbCrLf & Err.Description, vbExclamation, "Rates Load: Load Rates Error"
    Resume ExitProcedure
End Sub

3) To Test the recordset exists so that i can use the values throughout the form:
Private Sub Command1405_Click()

    MsgBox "RS: " & RS!Rate_Pens & " " & RS!Rate_Pens2
   
End Sub

4) on the form Close event:
Private Sub Form_Close()
    If Not RS Is Nothing Then
      RS.Close
      Set RS = Nothing
    End If
End Sub

Regarding your last comment.
I have a similar form that stores the current user and their permission level but thought that having storing 40+ values may be too much load on the database.

As the form for this question is only part of the project i could use a workaround like the following i suppose?

When i open the first form that performs the calculations i could open a second hidden form, pull the recordset values and store them in textboxes, refer to these as and when required from the first form, close both forms when i close the first form.

This way it might be more stable as opposed to using global variables?

What do you think?