MS Access 2010 run-time error 3061

I have written a bunch of vb modules that use queries to access data from tables.  I usually start with a working query that provides the data I'm after, view the query as SQL and copy and paste the SQL into a vb module and them modify it by adding the appropriate Set statement at the beginning, the dbOpenstatement at the end and quotations marks and continuation syntax throughout.  I've used this approach successfully many times, however yesterday I was developing a new module and as I was testing the code I kept getting the run-time error 3061.  Typically I use the following syntax:

 

  Set dbsHHC = CurrentDb
    'This function uses the following tables: Units
    'This function uses the following queries:
   
        Set rstK1s = dbsHHC.OpenRecordset( _
    "SELECT [Distribution Summary for Taxes].ID " & _
    "FROM [Distribution Summary for Taxes];", _
        dbOpenSnapshot)

 

The above query is an old one that I noticed is no lontger working.  Note that it is quite simple, I'm only requesting one item (ID) from one query.  The query itself requires input from a form.  I have the form open and providing appropriate input to the query.  If I open the query in Access, everything appears as it should be, but I'm still unable to get the simple vb version to do anything more than give me the run-timne error message. 

 

I recently upgraded to Office 2010 from Office xP.  The vb code above was written before the upgrade, and I have to believe this worked previously.  Back in the cobwebs of my mind I think I recall having this problem many years ago and found a solution on the web that required adding in a Reference from the vb Tools menu.  If that makes sense, the concersion from Access xP to 2010 may require this again.  Currently I see only Visual Basic For Applications, Microsoft Access 14.0 Object Library, OLE Automation, Microsoft ActiveX Data Objects 2.1 Library, and Microsoft DAO 3.6 Object Library (in that order) when I click on Tool > References...

 

Might I need additional librarys to allow my code to access the form entry when being called from vb?

 

|
Answer
Answer

I have written a bunch of vb modules that use queries to access data from tables.  I usually start with a working query that provides the data I'm after, view the query as SQL and copy and paste the SQL into a vb module and them modify it by adding the appropriate Set statement at the beginning, the dbOpenstatement at the end and quotations marks and continuation syntax throughout.  I've used this approach successfully many times, however yesterday I was developing a new module and as I was testing the code I kept getting the run-time error 3061.  Typically I use the following syntax:

 

  Set dbsHHC = CurrentDb
    'This function uses the following tables: Units
    'This function uses the following queries:
   
        Set rstK1s = dbsHHC.OpenRecordset( _
    "SELECT [Distribution Summary for Taxes].ID " & _
    "FROM [Distribution Summary for Taxes];", _
        dbOpenSnapshot)

 

The above query is an old one that I noticed is no lontger working.  Note that it is quite simple, I'm only requesting one item (ID) from one query.  The query itself requires input from a form.  I have the form open and providing appropriate input to the query.  If I open the query in Access, everything appears as it should be, but I'm still unable to get the simple vb version to do anything more than give me the run-timne error message. 

 

I recently upgraded to Office 2010 from Office xP.  The vb code above was written before the upgrade, and I have to believe this worked previously.  Back in the cobwebs of my mind I think I recall having this problem many years ago and found a solution on the web that required adding in a Reference from the vb Tools menu.  If that makes sense, the concersion from Access xP to 2010 may require this again.  Currently I see only Visual Basic For Applications, Microsoft Access 14.0 Object Library, OLE Automation, Microsoft ActiveX Data Objects 2.1 Library, and Microsoft DAO 3.6 Object Library (in that order) when I click on Tool > References...

 

Might I need additional librarys to allow my code to access the form entry when being called from vb?

 


If the query [Distribution Summary For Taxes] requires a parameter value from a form, then no, this did not work before as written. DAO does not know about Access forms, so it cannot automatically resolve parameter references to form controls. What you can do is use the Parameters collection of a QueryDef object, in conjunction with the Eval() function, to resolve query parameters and then open a recordset from the querydef. For example:

    Dim dbsHHC As DAO.Database
    Dim rstK1s As DAO.Recordset
    Dim qdf As DAO.QueryDef
    Dim prm As DAO.Parameter

    Set dbsHHC = CurrentDb

    Set qdf = dbsHHC.CreateQueryDef("",  _
       "SELECT [Distribution Summary for Taxes].ID FROM [Distribution Summary for Taxes]")

 

    For Each prm In qdf.Parameters
        prm.Value = Eval(prm.Name)
    Next prm

    Set rstK1s = qdf.OpenRecordset(dbOpenSnapshot)

 

Dirk Goldgar, MS Access MVP 2001-2015
Access tips: www.datagnostics.com/tips.html

1 person found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info


Last updated June 25, 2019 Views 2,193 Applies to: