Use Recordset as Form Recordsource

[Kevin_S]

Use recordset as form recordsource... with a twist

Hi Everybody

I've racked my brains on this for a few days off and on now so I'm turning to the forum of saviors for some much needed assistance...

I have a Client/Server app that I'm attempting to development using only ADO and parameter to send/retrieve recordsets that result from running Stored Procedures in SQL Server 2000. So far (with the much appreciated assistance of my man Java) I have had good results but I've hit a snag and I'm not sure what I'm doing wrong. I have a form with a listbox that, when the user selects a record from the list, code in the double-click event connects to the backend - passes the SubID(PK) from the selected record to a Stored Procedure which returns a recordset based on the parameter input. From here I am trying to bind this recordset to an unbound form and open the form (This is EXACTLY like having a form based on a query with a WHERE clause and passing data to the query and opening the form for that specific record)

However...

I am having problems as I keep getting an error telling me that I have a compile error and that there is an object required. The error highlights this line in the debug window:

Set strRecordset = rst

Here is the whole piece of code (minus the connection objects as these are stored in a module and called when required):

Public Sub OpenWIP(strData As String)
Dim objCmd As ADODB.Command
Dim rst As ADODB.Recordset
Dim strRecordset As String

Call EstablishConnection

Set objCmd = New ADODB.Command
objConn.CursorLocation = adUseClient

With objCmd
.ActiveConnection = objConn
.CommandText = "sproc_WIPMAIN"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("SubID", adVarChar, adParamInput, 8, strData)
Set rst = .Execute
Set rst.ActiveConnection = Nothing
Call ReleaseConnection
End With

If rst.RecordCount > 0 Then 'there are rows returned
Set strRecordset = rst
Forms!frmWIP.RecordSource = strRecordset
Else 'no rows
End If

Set rst = Nothing
End Sub

I have a feeling that the recordset that is returned is not in the correct format but I'm not sure as to the format required... if anyone has a suggestion or a place to get more info I'd be most greatful...

Thanks,
Kev

 

[dcx693]

Just looking at it quickly:
Set strRecordset = rst
attempts to set the contents of a string variable to the contents of a recordset. Perhaps strRecordset should be dimmed at a recordset object.

 

[Rob.Mills]

Hey Kevin, been awhile.

I've tried to do this before and have never been successful. But now that I know a little more I think I might be able to.

First question, is the form set as continuous or single. I think single would be easier in this situation.

Then you would have to setup custom navigation buttons that would look up another record depending on the button and then fill in the fields on the form with the recordset in memory.

A lot more complicated than just setting the recordset of the form to a recordset variable.

 

[Kevin_S]

dcx693/Rob - Thanks for taking an interest:

I tried dimentioning the variable strRecordset to recordset but that doesn't work either as it still highlights the .recordsource property and gives me Error 13: Compile Error. Here is what it says about the error in help:

Visual Basic is able to convert and coerce many values to accomplish data type assignments that weren't possible in earlier versions. However, this error can still occur and has the following causes and solutions:

The variable or property isn't of the correct type. For example, a variable that requires an integer value can't accept a string value unless the whole string can be recognized as an integer.
Try to make assignments only between compatible data types. For example, an Integer can always be assigned to a Long, a Single can always be assigned to a Double, and any type (except a user-defined type) can be assigned to a Variant.

This is really frustrating as I know that it is very easy to set a recordsource on the fly using something like:
strRecordset = "SELECT * FROM Customers"
Forms!frmName.Recordsource = strRecordset

But I can't figure out how to get this way to work...

Edit* Rob the form is single and will only display 1 record at any time

Any/All help is appreciated,
Kev

 

[Rob.Mills]

I think the issue is that the form needs a string and will calculate the recordset on open. But an ADO recordset is not a string so can't be assigned.

Not sure where to take it from here.

 

[Kevin_S]

I think your right Rob but that leads me to... How do I convert an ADO Recordset to a string???? ?????

I've have three main "bibles" SQL Server 2000 Professional Programming, Access 2002 VBA, & SQL Server with Access 2000 (all by Wrox) and there isn't any topics/examples of this conversion.....

Kev

 

[Rob.Mills]

Don't think you can. You can take the SQL of the recordset and plug that in. But then there's no reason to actually create the recordset.

I really don't think Access was designed for that. And although, a lot of us have figured out ways around Access' design, I think this is one that's stumped us all.

 

[Kevin_S]

There HAS to be a way to do this.... Does anyone know the method for tacking a recordset returned from a stored procedure and using it as the recordsource for a form...?

Thanks,
Kev

 

[Rob.Mills]

Are you using a mdb file or adp? I didn't pick up early that you were accessing a SQL Server.

 

[Kevin_S]

MS Access .mdb & MS SQL Server 2000

 

[Rob.Mills]

Lol! I just realized you're a different Kevin than I thought I was talking to.

Anyway, better to use an access project (adp) to access SQL Server. That way you set the recordset of a form to a stored procedure. Man I wish I had realized that earlier.

I know you put it in your original post. Guess my brain is focused on Access. I wish I could talk my boss into getting a server.

 

[Kevin_S]

Lol! I just realized you're a different Kevin than I thought I was talking to.

I get that all the time!

Seriously though - I considered an .adp file but honestly I didn't like the fact that the adp shows EVERY table in the linked db in the client (Access) and I want to stay away from queries on linked tables and concentrate on keeping as much as the "muscle" work on the server as possible + I still have code in DAO which doesn't work so well in .adp files...

Soo...

I'm still trying to work out this problem if anyone has a suggestion I'm all ears!!

Kev

 

[Rob.Mills]

Sounds like you know more about adp files than I do. But I thought that when you created a stored procedure in adp that the processing occurs on the server. But I could be wrong.

Hope you find the answer.

 

[Kevin_S]

No - your right - the stored procedure does process on the server wether accessed by an adp/mdb/mde/powerbuilder/ASP/etc... I am just trying to create a totally disconnected app (without the linked tables) so I went with the mdb (also like to make things hard on myself )

Thanks for the help and the time....

If anyone has a suggestion I'd love to hear 'em,
Kev

 

[RichMorrison]

Uhhhhhh Kevin,

You want to use the "RecordSet" property, not the "RecordSource" property.

See Access Help on the RecordSet property.

RichM

 

[Kevin_S]

Hi Rich - thanks for the interest & assistance,

When I use this:

Set strRecordset = rst
Forms!frmWIP.Recordset = strRecordset

I get error 91 'object variable or with block variable not set

and when i mouseover Forms!frmWIP.Recordset = strRecordset in the debug window it flashes that Forms!frmWIP.Recordset = Nothing... BUT I know and can confirm there are records being returned...?

Any ideas why I'm getting this?

Thanks in Advance,
kev

 

[rockman]

Seems like Rich has the answer there...

Syntax should be:

Set Forms!frmWIP.Recordset = rst

HTH,

Jeff

 

[RichMorrison]

Rockman wrote
<<
Syntax should be:

Set Forms!frmWIP.Recordset = rst
>>

Right. So.....

Dim myRS As ADO.Recordset

' do something to fill myRS

Set Forms![YourForm].Recordset = myRS

RichM

 

[Kevin_S]

Thanks Rich - That got it working!

For anyone else that is having problems connecting an ADO recordset to a for use Rich's example:

Set Forms!YourFormName.Recordset = myRS

Thanks again and take care,
kev

 

[Rob.Mills]

Kevin,

If you do this using an Access backend and a DAO recordset, will it keep it disconnected from the backend? I've been trying to figure out a way to do that since my dbs run so slow due to all the people connected to them all the time.