Solved

Run query based on specific criteria

Posted on 2007-04-10
31
654 Views
Last Modified: 2008-02-01
Hi,
I am building a Check Claim Database for our Treasury team and would like to be able to have a certain query run based on the entry made in a combo box on a form.  For example, if Treasury is selected as the department I want to run the "qryTreasury" to update the Treasury Review check box.  If EDMS is selected then run the "qryEMDS query.  I have 7 possible departments that may be selected.  

Thanks.  
0
Comment
Question by:SMolbeck
  • 14
  • 10
  • 7
31 Comments
 
LVL 19

Expert Comment

by:dmitryz6
ID: 18884791
On combobox after update

if Me!comboboxNAme = "Treasury" then

    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qryTreasury"
    DoCmd.SetWarnings True

elseif Me!comboboxNAme = "EDMS" then

    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qryEMDS"
    DoCmd.SetWarnings True
end if
0
 
LVL 19

Expert Comment

by:dmitryz6
ID: 18884829
you can use    elseif sttement
or you can use   case statement

On combobox after update
Example for elseif

dim strQryName as string
if Me!comboboxNAme = "Treasury" then
  strQryName="qryTreasury"
elseif Me!comboboxNAme = "EDMS" then
  strQryName="qryEDMS"
elseif Me!comboboxNAme = "test" then
  strQryName="qryTest"
end if

    DoCmd.SetWarnings False
    DoCmd.OpenQuery strQryName
    DoCmd.SetWarnings True

0
 
LVL 19

Expert Comment

by:dmitryz6
ID: 18884853
case example

combo after update
dim strQryName as string

  Select Case Me!comboboxNAme
        Case "Treasury"
            strQryName="qryTreasury"
        Case "EDMS"
            strQryName="qryEDMS"
    End Select

    DoCmd.SetWarnings False
    DoCmd.OpenQuery strQryName
    DoCmd.SetWarnings True
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 18884856
Private Sub Combo10_AfterUpdate()
select case me.Combo10.column(0)
Case "Treasury"
currentdb.execute "qrytreasury"
Case "EDMS"
currentdb.execute "qryEDMS"
Case "other"
...
end select
End Sub
0
 

Author Comment

by:SMolbeck
ID: 18884897
I like using the case vba.  I will give it a try and see what I come up with.  Thank you all for the quick response.  I will let you know.
0
 

Author Comment

by:SMolbeck
ID: 18885113
I have added the code to the after update click but I get an error that states "the action or method requires a Query Name argument."  It highlights the DoCmd.OpenQuery strQryName statement at the end.  Thanks.
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 18885580
who are you referring to?
0
 
LVL 19

Expert Comment

by:dmitryz6
ID: 18885615
in my code

dim strQryName as string comboboxNAme should be change to your actual combobox name.

if Me!comboboxNAme = "Treasury" then
  strQryName="qryTreasury"
elseif Me!comboboxNAme = "EDMS" then
  strQryName="qryEDMS"
elseif Me!comboboxNAme = "test" then
  strQryName="qryTest"
else
  strQryName="qryTest"
end if

    DoCmd.SetWarnings False
    DoCmd.OpenQuery strQryName
    DoCmd.SetWarnings True

If you will have problem Just paste your code,please




0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 18885652
in your combo box...is there more than one column? like an ID and then a query name?
0
 

Author Comment

by:SMolbeck
ID: 18888797
Here is what I have....
Private Sub cboDeptPostedBy_AfterUpdate()

Dim strQryName As String

  Select Case Me!cboDeptPostedBy
        Case "Treasury"
            strQryName = "qryTreasUpdate"
        Case "EDMS"
            strQryName = "qryEDMSUpdate"
        Case "DI"
            strQryName = "qryDIUpdate"
        Case "Annuity"
            strQryName = "qryAnnuityUpdate"
        Case "RS"
            strQryName = "qryRSUpdate"
        Case "Life"
            strQryName = "qryLifeUpdate"
        Case "Tax"
            strQryName = "qryTaxUpdate"
           
    End Select

    DoCmd.SetWarnings False
    DoCmd.OpenQuery strQryName
    DoCmd.SetWarnings True
End Sub
0
 
LVL 19

Expert Comment

by:dmitryz6
ID: 18888908
try this and post what message  it will give


Private Sub cboDeptPostedBy_AfterUpdate()

Dim strQryName As String

msgbox Me!cboDeptPostedBy  '<-Added

  Select Case Me!cboDeptPostedBy
        Case "Treasury"
            strQryName = "qryTreasUpdate"
        Case "EDMS"
            strQryName = "qryEDMSUpdate"
        Case "DI"
            strQryName = "qryDIUpdate"
        Case "Annuity"
            strQryName = "qryAnnuityUpdate"
        Case "RS"
            strQryName = "qryRSUpdate"
        Case "Life"
            strQryName = "qryLifeUpdate"
        Case "Tax"
            strQryName = "qryTaxUpdate"
           
    End Select

    DoCmd.SetWarnings False
    DoCmd.OpenQuery strQryName
    DoCmd.SetWarnings True
End Sub
0
 

Author Comment

by:SMolbeck
ID: 18889050
The message box appears.  I click OK and then I receive the following....
Run Time error 2496
     The action or method requires a query name argument

Thanks.
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 18889229
>>in your combo box...is there more than one column? like an ID and then a query name?
0
 
LVL 19

Expert Comment

by:dmitryz6
ID: 18890784
1.message box what is say?
2.Combo box property
Post please what row source.
How many number of columns
And bound column property
0
 

Author Comment

by:SMolbeck
ID: 18891302
Hi Dmitryz6
The message box says Microsoft Office Access
         DI
                       OK

Then I get the run time error I posted above.  When I hit debug is goes to the following line of code....
DoCmd.OpenQuery strQryName
Thanks.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:SMolbeck
ID: 18891313
I forgot to add that there is only one column.
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 18891323
Seems you don't have a case for "DI"
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 18891378
take out the set warnings...it can't find that query name. Is it a typo? does it find any query?

try changing your method from the Docmd to the CurrentDB.Execute method I pasted above.
J
0
 
LVL 19

Expert Comment

by:dmitryz6
ID: 18891574
You have parametr in your need to pass it
0
 
LVL 19

Expert Comment

by:dmitryz6
ID: 18891669
You canuse query def DAO.
Open code->Tools->references->check Microsoft DAO 3.6

Private Sub cboDeptPostedBy_AfterUpdate()

dim qdef As QueryDef

msgbox Me!cboDeptPostedBy  '<-Added

  Select Case Me!cboDeptPostedBy
        Case "Treasury"
            Set qdef = db.QueryDefs("qryTreasUpdate")
        Case "EDMS"
            Set qdef = db.QueryDefs("qryEDMSUpdate")
        Case "DI"
            Set qdef = db.QueryDefs("qryDIUpdate")
        Case "Annuity"
            Set qdef = db.QueryDefs("qryAnnuityUpdate")
        Case "RS"
            Set qdef = db.QueryDefs("qryRSUpdate")
        Case "Life"
            Set qdef = db.QueryDefs("qryLifeUpdate")
        Case "Tax"
            Set qdef = db.QueryDefs("qryTaxUpdate")
           
    End Select
  qdef.Parameters(0)=Yourparametr              
qdef.Execute
0
 
LVL 19

Expert Comment

by:dmitryz6
ID: 18891786
Actualy i missed

You canuse query def DAO.
Open code->Tools->references->check Microsoft DAO 3.6

Private Sub cboDeptPostedBy_AfterUpdate()

dim qdef As QueryDef,dim db as DAO.database

set db = currentdb

  Select Case Me!cboDeptPostedBy
        Case "Treasury"
            Set qdef = db.QueryDefs("qryTreasUpdate")
        Case "EDMS"
            Set qdef = db.QueryDefs("qryEDMSUpdate")
        Case "DI"
            Set qdef = db.QueryDefs("qryDIUpdate")
        Case "Annuity"
            Set qdef = db.QueryDefs("qryAnnuityUpdate")
        Case "RS"
            Set qdef = db.QueryDefs("qryRSUpdate")
        Case "Life"
            Set qdef = db.QueryDefs("qryLifeUpdate")
        Case "Tax"
            Set qdef = db.QueryDefs("qryTaxUpdate")
           
    End Select
  qdef.Parameters(0)=Yourparametr              
qdef.Execute
0
 

Author Comment

by:SMolbeck
ID: 18892773
Hi again,
This time a recieve a new run-time error '91'.
     Object variable or With block variable not set  - qdef.parameters (0) = yourparameters is highlighted.  

Thanks for all of your help.
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 18892875
SMolbeck,
You should try to be more responsive on your question. Try to stay with the person that is trying to help you..to answer questions and try suggestions.
0
 
LVL 19

Expert Comment

by:dmitryz6
ID: 18892879
Could you post sql for one query.
To find it open query in design->view->sql view
0
 

Author Comment

by:SMolbeck
ID: 18892947
Hi Dmitryz6,
Here is the SQL for the Life Udpate query.  Individuals are entering checks that they are unable to identify within their respective departments.  I want to consider them entering the check as their review.  In other words, once they have selected the Department from the drop down list I want to "check off" the review button.

UPDATE [Master Data] SET [Master Data].[Life Review] = -1
WHERE ((([Master Data].[Dept Posted By])="Life"));

Jeff - I will try and be more responsive when replying.  Thanks.
0
 
LVL 19

Expert Comment

by:dmitryz6
ID: 18893200
It could be misspeling of query name or
Do you have Access Back End or SQL Server?

this query shoul be run by statements

DoCmd.OpenQuery strQryName
as well
currentdb.execute "qryEDMS"
as well
set db = currentdb

  Select Case Me!cboDeptPostedBy
        Case "Treasury"
            Set qdef = db.QueryDefs("qryTreasUpdate")
        Case "EDMS"
            Set qdef = db.QueryDefs("qryEDMSUpdate")
        Case "DI"
            Set qdef = db.QueryDefs("qryDIUpdate")
        Case "Annuity"
            Set qdef = db.QueryDefs("qryAnnuityUpdate")
        Case "RS"
            Set qdef = db.QueryDefs("qryRSUpdate")
        Case "Life"
            Set qdef = db.QueryDefs("qryLifeUpdate")
        Case "Tax"
            Set qdef = db.QueryDefs("qryTaxUpdate")
           
    End Select
  'qdef.Parameters(0)=Yourparametr    '<-comment this          
qdef.Execute
0
 

Author Comment

by:SMolbeck
ID: 18893808
Dmitryz6,
I have reviewed all of the spelling in my queries and everything appears to be in order.  In answer to your other question I have Access Back End.  It is now failing on the dqry.execute line after I commented out the line above it.  I will take a closer look at in the am to see if I see anything.  This is the final piece to the entire project.  I hope to demo it next Tuesday.  With that said I appreciate all of your help.
0
 
LVL 19

Expert Comment

by:dmitryz6
ID: 18895256
not
"is now failing on the dqry.execute "

it

qdef.Execute
if this will not fix problem just for test
in your code put as very first line

docmd.run sql "UPDATE [Master Data] SET [Master Data].[Life Review] = -1
WHERE ((([Master Data].[Dept Posted By])='Life'))"
0
 

Author Comment

by:SMolbeck
ID: 18897101
Good morning Dmitryz6,
You were correct.  I typed the line incorrectly.  It was QDEF.EXECUTE.  I moved it to the beginning of my code and recieved the following error ' Run-Time error 91' Object variable or with Block variable not set.
SAM
0
 
LVL 19

Accepted Solution

by:
dmitryz6 earned 250 total points
ID: 18897166
No I asked just correct speling, not move it back
code should be just copy it and paste

dim qdef As QueryDef,db as DAO.database

set db = currentdb

  Select Case Me!cboDeptPostedBy
        Case "Treasury"
            Set qdef = db.QueryDefs("qryTreasUpdate")
        Case "EDMS"
            Set qdef = db.QueryDefs("qryEDMSUpdate")
        Case "DI"
            Set qdef = db.QueryDefs("qryDIUpdate")
        Case "Annuity"
            Set qdef = db.QueryDefs("qryAnnuityUpdate")
        Case "RS"
            Set qdef = db.QueryDefs("qryRSUpdate")
        Case "Life"
            Set qdef = db.QueryDefs("qryLifeUpdate")
        Case "Tax"
            Set qdef = db.QueryDefs("qryTaxUpdate")
           
    End Select            
qdef.Execute

0
 
LVL 19

Expert Comment

by:dmitryz6
ID: 18901295
I am glad it finaly worked,good luck with your project
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
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…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

708 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

15 Experts available now in Live!

Get 1:1 Help Now