[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

setting a unbound subform to a recordource in a adodb array

Posted on 2016-11-08
12
Medium Priority
?
71 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 39

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 39

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 39

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

650 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