?
Solved

setting a unbound subform to a recordource in a adodb array

Posted on 2016-11-08
12
Medium Priority
?
74 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 1200 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 85
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 40

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
 
LVL 3

Author Comment

by:Fordraiders
ID: 41879609
running this search from a command button.
0
 
LVL 40

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 40

Assisted Solution

by:PatHartman
PatHartman earned 800 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
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 …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

864 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