Solved

Updating ComboBox Query, Doesn't "take"

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
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…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

743 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now