Use Recordset as Form Recordsource (1 Viewer)

Kevin_S

Registered User.
Local time
Today, 00:47
Joined
Apr 3, 2002
Messages
635
Use recordset as form recordsource... with a twist

Hi Everybody :D

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

Registered User.
Local time
Today, 00:47
Joined
Apr 30, 2003
Messages
3,265
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

Registered User.
Local time
Today, 00:47
Joined
Aug 29, 2002
Messages
871
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

Registered User.
Local time
Today, 00:47
Joined
Apr 3, 2002
Messages
635
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

Registered User.
Local time
Today, 00:47
Joined
Aug 29, 2002
Messages
871
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. :rolleyes:
 

Kevin_S

Registered User.
Local time
Today, 00:47
Joined
Apr 3, 2002
Messages
635
I think your right Rob but that leads me to... How do I convert an ADO Recordset to a string???? :confused: ?????

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

Registered User.
Local time
Today, 00:47
Joined
Aug 29, 2002
Messages
871
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

Registered User.
Local time
Today, 00:47
Joined
Apr 3, 2002
Messages
635
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

Registered User.
Local time
Today, 00:47
Joined
Aug 29, 2002
Messages
871
Are you using a mdb file or adp? I didn't pick up early that you were accessing a SQL Server.
 

Rob.Mills

Registered User.
Local time
Today, 00:47
Joined
Aug 29, 2002
Messages
871
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

Registered User.
Local time
Today, 00:47
Joined
Apr 3, 2002
Messages
635
Lol! I just realized you're a different Kevin than I thought I was talking to.

I get that all the time! :D

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

Registered User.
Local time
Today, 00:47
Joined
Aug 29, 2002
Messages
871
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

Registered User.
Local time
Today, 00:47
Joined
Apr 3, 2002
Messages
635
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

Registered User.
Local time
Yesterday, 23:47
Joined
Apr 24, 2002
Messages
588
Uhhhhhh Kevin,

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

See Access Help on the RecordSet property.

RichM
 

Kevin_S

Registered User.
Local time
Today, 00:47
Joined
Apr 3, 2002
Messages
635
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

Senior Member
Local time
Yesterday, 21:47
Joined
May 29, 2002
Messages
190
Seems like Rich has the answer there...

Syntax should be:

Set Forms!frmWIP.Recordset = rst

HTH,

Jeff
 

RichMorrison

Registered User.
Local time
Yesterday, 23:47
Joined
Apr 24, 2002
Messages
588
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

Registered User.
Local time
Today, 00:47
Joined
Apr 3, 2002
Messages
635
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

Registered User.
Local time
Today, 00:47
Joined
Aug 29, 2002
Messages
871
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.
 

Users who are viewing this thread

Top Bottom