Solved

ADO recordset Error

Posted on 2014-04-07
16
583 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
Comment Utility
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
Comment Utility
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
Comment Utility
so we are good?
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
change the name of the combo from

cbo.CodeID

to

cboCodeID   -   remove the dot (.)
0
 

Author Comment

by:Taras
Comment Utility
I removed dot it is the same.
Still the same error.
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
post the codes that you are using and indicate which line is raising the error.


.
0
 

Author Comment

by:Taras
Comment Utility
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
Comment Utility
"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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
.
0
 

Author Comment

by:Taras
Comment Utility
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 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 250 total points
Comment Utility
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
Comment Utility
Rey I tried it not working.
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
can you upload a copy of the db?
0
 
LVL 12

Expert Comment

by:jkaios
Comment Utility
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
Comment Utility
...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
Comment Utility
Thansk a lot
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
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 …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

762 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