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

Hi,

I have a form whose datasource is set to a query that populates the form (this works fine).

I need to perform some calculations at various stages throughout the form once the form has been opened so I am trying to open an ADODB recordset on the OnLoad event that pulls the values required for the calculations (from tblRates) and makes them available throughout the form as and when required.

I have put the following code in the OnLoad Event of the form but I am getting a "Compile Error: User-Defined type not defined " on line 1 when i open the form.

Any suggestions please?

============================================================
Dim RS As ADODB.Recordset
    Set RS = ADODB.Recordset
   
   
Dim strRate_Pens As Single     ' Dim strRate_Pens As String
   
    RS.Open "SELECT Rate_Pens FROM tblRates", CurrentProject.Connection, adOpenStatic
   
        strRate_Pens = RS!Rate_Pens
   
    RS.Close
    Set RS = Nothing
============================================================
andrewpiconnectAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
0
andrewpiconnectAuthor Commented:
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
0
andrewpiconnectAuthor Commented:
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
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
0
andrewpiconnectAuthor Commented:
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.
0
andrewpiconnectAuthor Commented:
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?
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
i am a little concerned about the recordset being destroyed on unhandled errors
VBA generally handles this sort of garbage collection properly, but to be on the safe side use my suggestion above to handle this.

Also, to be really safe:

If Not RS Is Nothing Then
  RS.Close
  Set RS = Nothing
End If

This would avoid a "Null Object" error.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
FWIW: I have a single form with many textboxes, and those textboxes hold my "session values" in my apps. For example, if I have a login process to an app, I'll include textboxes to hold the UserName (txUserName), the UserLevel (txUserLevel) and so forth.

When I need to refer to them, I simply do this:

Forms("MyHiddenForm").txUserName

With this method I can have a LOT of "session values" that are maintained regardless of what else happens in my app. I've found this to be more stable than other methods (like declaring Global variables, for example, or using a Class object to store this).
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
andrewpiconnectAuthor Commented:
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
0
andrewpiconnectAuthor Commented:
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?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.