Solved

ADO recordset Error

Posted on 2014-04-07
16
595 Views
Last Modified: 2014-04-11
I have combo box “cbo.CodeID” on the main form named  frm_Main.
On mani form I have sub form named “frm_SubForm”

I pulled ADO recordset "rsSub" from table without error,now I want next:
On after update event in combo box  I want to refresh my subform  “ frm_subForm” with ADO recordset “rsSub”  as subform recordset.
I have this code in comb box after update event:
Set  Froms!frm_Main!frm_subForm.Form.Recordset = rsSub

I am getting error
Run-time error '7965'
the object you entered is not a valid recordset property
0
Comment
Question by:Taras
  • 6
  • 5
  • 3
  • +1
16 Comments
 
LVL 7

Expert Comment

by:COACHMAN99
ID: 39983556
are you using ADO and disconnected recordsets for a reason (as opposed to bound forms?)?

also: maybe Froms should be forms?
Set  Froms!frm_Main!frm_subForm.Form.Recordset = rsSub
0
 

Author Comment

by:Taras
ID: 39983769
COACHMAN99
If I am going with bound forms I need to set up DNS on users PC’s and I preferred to go with disconnect ADO.
It was Forms (Set Forms!frm_Main!frm_subForm.Form.Recordset = rsSub ) I made typo mistake.
0
 
LVL 7

Expert Comment

by:COACHMAN99
ID: 39983777
so we are good?
0
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.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39983874
change the name of the combo from

cbo.CodeID

to

cboCodeID   -   remove the dot (.)
0
 

Author Comment

by:Taras
ID: 39984016
I removed dot it is the same.
Still the same error.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39984157
post the codes that you are using and indicate which line is raising the error.


.
0
 

Author Comment

by:Taras
ID: 39984303
Dim cnn As ADODB.Connection
     Dim rstExp As ADODB.Recordset


    Set cnn = New ADODB.Connection
    Set rstExp = New ADODB.Recordset
   
    '*************
    cnn.Open "Provider=SQLOLEDB.1;" & _
        "Integrated Security=SSPI;" & _
        "Persist Security Info=True;" & _
        "Initial Catalog=ProductionDB;" & _
        "Data Source=MM-LLL-PRO\ProdDB;" & _
        "Use Procedure for Prepare=1;" & _
        "Auto Translate=True;" & _
        "Packet Size=4096;" & _
        "Workstation ID=AAAA-NNN1;" & _
        "Use Encryption for Data=False;" & _
        "Tag with column collation when possible=False"



    rstExp.Open "Select * from ProductionDB.Product", cnn, adOpenDynamic, adLockOptimistic


   rstExp.AddNew
      rstExp.Fields("CustomerID") = IntCustomerID

      rstExp.Fields("MonthID") = intMonthID

      ...
      ...

   rstExp.Update

   rstExp.close


'This is working ok,and I can see records in table.

'Now I would like to select all records for particular customer for that month and display it on subform.


rstExp.Open "Select * from ProductionDB.Product where CustomerID =" & intCustomerID & " And MonthID =" & intMonthID, cnn, adOpenDynamic, adLockOptimistic
'Now I want to allocate recordset to my subform.
Set Forms!frm_Main!frm_subForm.Form.Recordset = rstExp << error line.

Me.Refresh





I posted this question a couple days ago and got answer that
This should work, (but is not working):

Private Sub Form_Current()
Set Me.sf.Form.Recordset = rs ' Assuming that rs is module global, otherwise you could also build it here End Sub
0
 
LVL 12

Expert Comment

by:jkaios
ID: 39984547
"Forms!frm_Main!frm_subForm.Form.Recordset" is a DAO recordset object and not of type ADO.Recordset, hence the run-time error 7965.

Try replacing rstExp As ADODB.Recordset with rstExp As DAO.Recordset and use:

Set rstExp = CurrentDb.OpenRecordset(...) instead
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39984693
.
0
 

Author Comment

by:Taras
ID: 39985049
jkaois.


Database tables are seating on Server and it is SQL Server8 database.

Access forms are just front end for entry and editing records.

I do not want to use DAO. I used DAO before when my tables where in Access database.
I want to use ADO!

I already said that  I made connection with database, I get out recordset , I can add new records and it is working ok.

And you are suggesting me to go To DAO why?
What ADO recordset can not do and DAO can???


And if as you said:
"Forms!frm_Main!frm_subForm.Form.Recordset" is a DAO recordset object and not of type ADO.Recordset"

Ok, then gave me ADO version of this? Or it does not exists??

I need ADO solution not DAO.
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 250 total points
ID: 39985738
in this line

Set  Forms!frm_Main!frm_subForm.Form.Recordset = rsSub

replace "frm_subForm" -  with the name of the subform control


Set  Forms!frm_Main!nameOfSubFormControl.Form.Recordset = rsSub
0
 

Author Comment

by:Taras
ID: 39985920
Rey I tried it not working.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39985969
can you upload a copy of the db?
0
 
LVL 12

Expert Comment

by:jkaios
ID: 39987666
What version of Access are you using?

The reason for DAO is that Access uses DAO by default.  However (and yes! absolutely!) ADO is newer and better so I would definitely use it.

In your Connection String, try setting Persist Security Info = False and also remove the other parameters as the provider will automatically set them for you.


cnn.Open "Provider=SQLOLEDB.1;" & _
        "Integrated Security=SSPI;" & _
        "Persist Security Info=False;" & _
        "Initial Catalog=ProductionDB;" & _
        "Data Source=MM-LLL-PRO\ProdDB"
0
 
LVL 12

Accepted Solution

by:
jkaios earned 250 total points
ID: 39987681
...or try:

'Set Forms!frm_Main!frm_subForm.Form.Recordset = rstExp << error line.

' remove "Form" from frm_subForm
Set Forms!frm_Main!frm_subForm.Recordset = rstExp

' or another method:
Set Forms("frm_Main").Forms("frm_subForm").Recordset = rstExp
0
 

Author Closing Comment

by:Taras
ID: 39995602
Thansk a lot
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

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…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
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.

813 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

14 Experts available now in Live!

Get 1:1 Help Now