Runtime Error 2455

[sunmorgus]

Hello. I am trying to set the recordset of a form on open using an ado recordset, and I get Runtime Error 2455 - You entered an expression that has an invalid reference to the property Recordset. Below is the code I am using to get the recordset for the form (I have omitted my SQL statement, as it is very long and will take up alot of the page; if you need to see it, let me know and I will post it):

    Dim adRMAConn As ADODB.Connection
    Dim adRMARS As ADODB.Recordset
    Dim RMABaseSQL, srchRMA, srchSN As String

    Set adRMAConn = New ADODB.Connection
    adRMAConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Nick DeMayo;" & _
                    "Data Source=T:\Database Project\Customer Support Tables.mdb;" & _
                    "Mode=ReadWrite|Share Deny None;Persist Security Info=False;" & _
                    "Jet OLEDB:System database=t:\Database Project\Secured.mdw", "Nick DeMayo", "Nic"
        'build the where statement
    If Not (IsNull(Forms!frmRMAProcess!txtRMANumber)) Then
        srchRMA = RMABaseSQL & " WHERE ((([Work orders].[Closed Date]) Is Null) AND (([Work orders].[RMA Number])=" & Forms!frmRMAProcess!txtRMANumber & "));"
    End If

    Set adRMARS = New ADODB.Recordset
    adRMARS.CursorLocation = adUseClient
    adRMARS.Open srchRMA, adRMAConn, adOpenDynamic, adLockOptimistic
    Set Forms("RMA Base Form").Recordset = adRMARS

I do not get any errors on the SQL statement, and the "Set Forms("RMA Base Form").Recordset = adRMARS" is the highlighted line in the error. I have used this method before for other forms in my DB, but never for one this large. Also, this form has a couple of subforms on it, could they be the cause? Any help you can provide would be greatly appreciated! Thanks in advance!

 

[MacCaillean]

Hi sunmorgus

Do you need to have "RMA Base Form" open before you can set its .Recordset?

So, add DoCmd.OpenForm "RMA Base Form" before you try and set the recordset.

Worth a try...

Mac

 

[sunmorgus]

I am sorry, I guess I should have been clearer on that point. This code is in the open event of the RMA Base Form, so the form is open when the code runs. Should I move this code to the load event? Would that help?

 

[PHV]

Have a look here:

http://support.microsoft.com/kb/281998/EN-US/

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[sunmorgus]

Thanks PHV, I just so happened that I ran accross that article last night, and through a little bit of reading discovered that what I was trying to do would not work. I am using Access 2000 for my database, and 2K does not support binding of forms with ADO unless you are using SQL server and a query that only accesses one table, as explained in this article,

http://support.microsoft.com/kb/227053/EN-US/,

linked too on the article you sent me. Thank you all for your help though!