Solved

setting a unbound subform to a recordource in a adodb array

Posted on 2016-11-08
12
29 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

943 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

4 Experts available now in Live!

Get 1:1 Help Now