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
Solved

ADO recordset Error

Posted on 2014-04-07
16
597 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
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 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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
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 …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

860 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