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

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

732 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