Solved

For the DoCmd.OpenQuery do you have to have a query saved when using VB?

Posted on 2014-01-13
6
410 Views
Last Modified: 2014-01-14
I have a query in Access VB as follows:

            strCurrentYear = "[Dues '" & Format(Now(), "yy") & "]"
           
            strSQL = NormalSelect & " FROM Table1 " & _
                     "WHERE " & strCurrentYear & " <> 0 ORDER BY Last"
            CurrentDb.QueryDefs("Membership").SQL = strSQL
            DoCmd.OpenQuery "Membership"

It worked fine until 2014 hit.  Then it still displayed last year's information.  I tracked it down to the query that was saved as "Membership".  In there was still the "Dues '13 <>0".  Once I deleted that from the saved query the output was correct.

Seems as though I shouldn't have to do that.  Is there a way to do this such that the advancing years will not have an impact?

For those that are wondering, NormalSelect is defined as "SELECT [First], [Last], Street, City, State, Zip, [Phone #] " just so I don't have to keep typing it over all the time.

Thanks,
Ric
0
Comment
Question by:Joppa
  • 3
  • 3
6 Comments
 
LVL 57
ID: 39777826
Ric,

  Use a querydef object:

  Dim dbCur as DAO.Database
  Dim qryDef as DAO.QueryDef

 Set dbCur = CurrentDB()  
 strCurrentYear = "[Dues '" & Format(Now(), "yy") & "]"
 strSQL = NormalSelect & " FROM Table1 " & _
                     "WHERE " & strCurrentYear & " <> 0 ORDER BY Last"
 Set qryDef = OpenQueryDef("Membership")
 qryDef.Properties("SQL") = strSQL
 qryDef.Close          
 set qrydef = nothing
 set dbCur = nothing

 DoCmd.OpenQuery "Membership"

Jim.
0
 

Author Comment

by:Joppa
ID: 39777898
Jim,
  I get a "Sub or Function not defined" error when I go to compile for OpenQueryDef.  I looked around and found that it may be the DAO library was not selected but it was.  Any other ideas on what the problem is?

Thanks,
Ric
0
 
LVL 57
ID: 39778026
Sorry, should be:

Set qryDef = dbCur.OpenQueryDef("Membership")

Jim.
0
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

 

Author Comment

by:Joppa
ID: 39778247
Jim,

  Can't seem to get ahead of the curve here.  Now I get "Function or Interface Marked as Restricted, or the Function Uses an Automation Type Not Supported in Visual Basic"  I tried to find information on this and it said I have to provide a path to Dao350.dll but I don't have that dll.  I have a reference to DAO 3.6 selected.

Thanks for the help in advance.

Ric
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
ID: 39779050
My apologies; old habits die so hard.

You want:

Set qryDef = dbCur.QueryDefs("Membership")

OpenQueryDef() is the old call (but then I'm a old programmer as well<g>).

Jim.
0
 

Author Comment

by:Joppa
ID: 39779171
Jim,


  Know the feeling.

  That fixed my problem.  Thanks for the help.

Ric
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

746 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

16 Experts available now in Live!

Get 1:1 Help Now