Updating ComboBox Query, Doesn't "take"

Posted on 2011-03-01
Last Modified: 2012-05-11
I've got a combo box ProjectIDAddLookup that looks up a list of programs and projects.   It's on a form called "Actions", along with the code below.

I'm trying to filter the long list of programs and projects based on a some buttons, one of which is shown in the code below.  The code goes out and modifies the combo-box query.

The code works, sort of.  Because if I go look at the query in the design view (SQL), I see that it is properly formed, and if I run it, it gives the results I want.

The problem is that if just click on the filter button, and then on the combo box drop down, the combo box query results don't change.  It's only if I DO go look at the query in design view, run it out of design view, and then come back and run the combo box on the form, does the query change and give the desired results.

I tried requerying the control and the whole form, no joy.

Any suggestions on how to get the query to update properly on the filter button click would be appreciated.

Private Sub SOX_Only_Button_Click()
Me.ProgramFilter = "SOX"
ProjectIDAddLookupHelperLauncher (1)
End Sub

Sub ProjectIDAddLookupHelperLauncher(varVar As Integer)
Select Case Me.ProgramFilter
    Case "SOX"
        ProjectIDAddLookupHelper (varVar)
        wrkStr = "SOX"
    Case "HASP"
        ProjectIDAddLookupHelper (varVar)
        wrkStr = "HASP"
    Case "OPS_RPTS"
        ProjectIDAddLookupHelper (varVar)
        wrkStr = "OPS RPTS'"
    Case "All_Other"
        ProjectIDAddLookupHelper (0)
        wrkStr = ""
End Select
End Sub

Sub ProjectIDAddLookupHelper(varVar As Integer)

Select Case varVar
    Case 0
        strSql1 = "046_Programs-Projects-Lookup"
        strSql3 = "046_Programs-Projects_Lookup-HOLDER"
        CurrentDb.QueryDefs(strSql1).SQL = CurrentDb.QueryDefs(strSql3).SQL
    Case 1
        strSql1 = "046_Programs-Projects-Lookup"
        strSql3 = "046_Programs-Projects_Lookup-HOLDER"
        strSql2 = CurrentDb.QueryDefs(strSql3).SQL
        strSql2 = Replace(strSql2, "Where ((", "Where ((([018_Programs].[ProgramAbbrev] = '" & Forms![046_Actions_OPM].ProgramFilter & "') AND ")
        CurrentDb.QueryDefs(strSql1).SQL = strSql2
End Select

End Sub

Open in new window

Question by:codequest
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
LVL 34

Accepted Solution

flavo earned 500 total points
ID: 35012669
You can also set the comboxes RowSource, which should also fire a requery, and saves you from playing around with qDefs.


Select Case varVar
    Case 0
        strSql3 = "046_Programs-Projects_Lookup-HOLDER"
        Me.ProjectIDAddLookup.RowSource = strSQL3
    Case 1
        strSql1 = "046_Programs-Projects-Lookup"
        strSql3 = "046_Programs-Projects_Lookup-HOLDER"
        strSql2 = CurrentDb.QueryDefs(strSql3).SQL
        strSql2 = Replace(strSql2, "Where ((", "Where ((([018_Programs].[ProgramAbbrev] = '" & Forms![046_Actions_OPM].ProgramFilter & "') AND ")
        Me.ProjectIDAddLookup.RowSource = strSQL2

End Select

Hope that makes sense...

LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35019034
<welcome back flav... ;-) >

Author Closing Comment

ID: 35020349
Worked slick.  Gracias!

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

717 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