Solved

Updating ComboBox Query, Doesn't "take"

Posted on 2011-03-01
3
237 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
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…

679 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