Form Recordset Failing as SubForm Only

[CreativeStrat]

Hi

I use this method a fair bit in my current project. To the point I am considering making a module to take the form name and SQL and process at one point for all of this project...a lot.

However it is failing me on using on a sub form. I have spent a good 40 minutes looking about on line and cant see anything overly helpful.

The SQL is being passed through to SQL Server for processing.

In simple terms I have a form showing outstanding suppliers for outstanding orders. This sub form should load on the before update event of the supplier being selected to simply display all the OS orders by that supplier.

Dim strSQL As String
Dim rst As ADODB.Recordset

    Set rst = New ADODB.Recordset
    
     If gcn.State = 0 Then Call cssOpenConnection


        strSQL = "SELECT OrderID, AdminID, JobID, SupplierID, PlacedBy, OrderDate, " & _
               "PONumber, RequiredDate, DeliveryID, Freight, ReceiveDate, IsNull([ReceiveDate],0) AS Expr1 " & _               "From dbo.[Order] " & _
               "WHERE (((SupplierID)=" & Me.Parent.Form.cboFindSupplier & "));"

    rst.CursorLocation = adUseClient

    rst.Open strSQL, gcn, adOpenStatic, adLockPessimistic, True
    
    Debug.Print Me.Parent.Form.cboFindSupplier
    
       Set Me.Recordset = rst
        
    rst.Close

I have tried a couple of different locks and checked the SQL is not failing on SQL Server and it is fine.

The error is 2115 and reads "Method 'recordset' of object failed etc"

Anyone got an idea on this?

 

[Andrzejek]

Could you do:

...
strSQL = "SELECT OrderID, AdminID, JobID, SupplierID, PlacedBy, OrderDate, " & _
    "PONumber, RequiredDate, DeliveryID, Freight, ReceiveDate, IsNull([ReceiveDate],0) AS Expr1 " & _               "From dbo.[Order] " & _
     "WHERE (((SupplierID)=" & Me.Parent.Form.cboFindSupplier & "));"
[blue]
Debug.Print strSQL [/blue]
...

and tell us what you have in the Immediate Window?

It looks to me you have [tt]... From SomeTable ...[/tt] missing in your SQL


---- Andy

There is a great need for a sarcasm font.

 

[lameid]

Andy is pointing out a missing carriage return... in the posted code

strSQL = "SELECT OrderID, AdminID, JobID, SupplierID, PlacedBy, OrderDate, " & _
               "PONumber, RequiredDate, DeliveryID, Freight, ReceiveDate, IsNull([ReceiveDate],0) AS Expr1 " & _               
                "From dbo.[Order] " & _
               "WHERE (((SupplierID)=" & Me.Parent.Form.cboFindSupplier & "));"

I don't know that before update is correct timing. I think it would have to be after update. Even so I am not sure about using the recordset... I remember timing is weird working with sub forms and I normally don't use recordsets as recordsources.

 

[Andrzejek]

Oppps! Sorry about it...


---- Andy

There is a great need for a sarcasm font.

 

[lameid]

Looks like we lost OP anyway.

 

[CreativeStrat]

Sorry I found the issue it wasnt a code error at all. It was something happening in the connection.

Resolved and I got busy working on the next part, so lost the thread for a while.

Thanks for the help.