Solved

setting a unbound subform to a recordource in a adodb array

Posted on 2016-11-08
12
26 Views
Last Modified: 2016-11-09
I have a main form
I have a subform on a tabctrl27   page28
child31 = subform name

Once i get the data into an array
rcarray = rst.GetRows

I need to assign the subform the array and display the data ?

is this possible ?

Thanks
fordraiders
0
Comment
Question by:fordraiders
  • 5
  • 3
  • 3
  • +1
12 Comments
 
LVL 19

Accepted Solution

by:
Eric Sherman earned 300 total points
ID: 41879271
<<<Once i get the data into an array
rcarray = rst.GetRows>>>

Use a Query/SQL to change the Record Source of your sub form.

ET
0
 
LVL 3

Author Comment

by:fordraiders
ID: 41879334
I need the array to supply the subform
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 41879421
As far as I can tell ... In Access, a From Object's Record Source is either a Table Object or Query Object.

ET
0
 
LVL 84
ID: 41879494
You can use the Recordset as your data source, without putting it into an Array:

Me.Recordset = rst

Or if you're doing this from the main form:

Me.SubformCONTROL.Form.Recordset = rst
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41879571
A recordset IS an array.  So if you use bound subforms, you don't need to do any coding to handle multiple records.  Why not tell us what you are trying to do that a bound subform won't do for you?
0
 
LVL 3

Author Comment

by:fordraiders
ID: 41879600
Pat.
running a stored procedure from sql server via vba:
' CONNECTIONS FOR SQL SERVER
strConnect = "provider=sqloledb;Server=PRMD.xxxxx.com;Database=Quote;Uid=xxxxx;Pwd=axxx;"
' Instantiate the connection object
Set cnn = New ADODB.Connection
' Open the connection based on the strConnect connect string arguments
cnn.Open strConnect
' Instantiate the command object
Set cmd = New ADODB.Command
' Assign the connection and set applicable properties
cmd.ActiveConnection = cnn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "sp_DefaultSearch"
' Instantiate the recordset object by using the return value
' of the command's Execute method. Supply the parameters by
' packing them into a variant array
cmd.Parameters.Append cmd.CreateParameter("@Token", adVarChar, adParamInput, 10, S_TOKEN)
Set rst = cmd.Execute()

so once i have the rst  I was hoping to put into an unbound subform.
I have to rerun this query for each search..


fordraiders
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 3

Author Comment

by:fordraiders
ID: 41879609
running this search from a command button.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41880694
Why are you using a stored procedure rather than an Access or pass-through query?  Why are you using an unbound form?  Access is a rapid application development tool.  The heart of what makes it "rapid" is bound forms.  You need to think long and hard before deviating from that and if you decide that you absolutely must use unbound forms, then consider using some platform other than Access because you are incurring all the problems of deploying an Access solution without getting the benefits of RAD.
0
 
LVL 3

Author Comment

by:fordraiders
ID: 41880715
Pat , i switched to a pass through query. And bound the subform to that pass through.
0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 200 total points
ID: 41880728
You will be much happier with that solution and it is much less work.  The form is not updateable though.  If that is a problem, replace the pass-through query with a normal Access query against a linked table or view.

Access attempts to make every query a pass-trough query so if your query selects 100 records out of a million, Access sends the query to the server and the server returns 100 records.  It does NOT return the entire table.  You can of course defeat Access by doing things that prevent Access from sending the query to the server.  In that case, Access will send an unqualified query to select all the rows and then finish the process locally.  Things like heterogeneous joins and VBA functions in a where clause cannot be sent to the server and so must be handled locally.  As long as you understand the process, you can control it.  I find that I rarely need pass-through queries for bound forms although sometimes views are helpful.

Make sure you are making decisions on how the app is structured based on an understanding of how Access actually works.
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 41880841
ITA with PatHartman's last comments and is why I originally suggested using a Query or Table object.

ET
0
 
LVL 3

Author Closing Comment

by:fordraiders
ID: 41881034
Thanks guys for the direction.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now