Solved

Run query based on specific criteria

Posted on 2007-04-10
31
699 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
[X]
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
  • 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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
 

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

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

623 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