Solved

Updating ComboBox Query, Doesn't "take"

Posted on 2011-03-01
3
234 Views
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.

Thanks!
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
        Me.ProjectIDAddLookup.Requery
        Me.Requery
    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
        Me.ProjectIDAddLookup.Requery
        Me.Requery
End Select

End Sub

Open in new window

0
Comment
Question by:codequest
3 Comments
 
LVL 34

Accepted Solution

by:
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.

Example:

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

Dave
0
 
LVL 74

Expert Comment

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

Author Closing Comment

by:codequest
ID: 35020349
Worked slick.  Gracias!
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

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…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server functions 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 Microsoft Ac…
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.

778 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