filter combo box options if something selected

[neemi]

I have 2 combo boxes on a form. the fist one shows all clients and the second one shows all projects. A client can have many projects.

What I am trying to achieve is that when a client is slected from [combobox1] then then only that clients projects are shown in [Combobox2] - THIS I HAVE NO PROBLEM WITH ACHIEVING.

But what i also want to acheive is that if no Client is selected in [COMBOBOX1] then all the projkects of all the clients are still available to view or select from [COMBOBOX2].

I have done this before but it just wont work.

please help the easiest way to do this. I was trying do avaoid changing the rowsource via code.

Is this the only option.

How can I do this.

 

[orna]

If Me!combobox1.ItemsSelected.COUNT = 0 Then
Me!combobox2.RowSource = "your query without parameters"
else
Me!combobox2.RowSource = "The query you have"
End If

 

[fneily]

I was trying do avaoid changing the rowsource via code"
You will never make a user friendly Access database by avoiding code. Or any other application for that matter. There's nothing wrong with education.

Since you don't want any code, why not just create another combobox that shows all the projects? Be sure to label it so users know that'll it's for All projects only.

 

[postermmxvicom]

I base my row source off a query that references a control on my form.

The only problem here is that then, when that control's value changes - the combobox doesn't change unless it is requeried. (like when the form is opened, or I think even a F5 keypress does that - not sure). But that's not really acceptable.

Soo...I use the after update event in the control to requery the combobox.

That still uses a little bit (one line of code) but, it isn't changing the rowsource via code.

One last thing: Sometimes I wonder; "Is that someone's signature? Or do they type that at the end of each post?

 

[neemi]

One thing I forgot to mention is that the combo box that needs the rowsource repopulated is on a continuous form.

Let me explain a little better... on continuous form I have a number of fields... for arguments sake I'll Call then [FEILD_A], [FEILD_B], [FEILD_C].

[FEILD_A] and [FEILD_B] needs to determine what is populated in [FEILD_C] dropdown menu in the continuous form.

I have managed to change the row source via code but it effects all the records and data disappears/reappears etc depending on which record is current.

I need to get this working so that it doesn't make data disapper and I understand that changing the rowsource will do this.

Does anyone have any idea's on how to best acheive this please.

urgent help requuired.

Neemi

 

[PHV]

What is the underlaying SQL code of COMBOBOX2's RowSource ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[neemi]

i populate the code on the "onFocus" event as follow...

Dim strSQLPerson As String
Dim strSQLWhere As String
Dim strSQLCrit As String

'// Populate person dropdown list
    strSQLPerson = "SELECT tblPerson.PersID, [PersSName] & ', ' & [PersFName] AS PersName, tblPerson.Freelance, tblPerson.PersJob, tblPerson.LocationID, tblPerson.CapID, tblPerson.SkillID, tblPerson.LevelID " & _
                   "FROM tblPerson "
    
    If Me.LocationID > 0 Then
        strSQLWhere = "tblPerson.LocationID = " & Me.LocationID & " AND "
    End If
    If Me.CapID > 0 Then
        strSQLWhere = "tblPerson.CapID = " & Me.CapID & " AND "
    End If
    If Me.SkillID > 0 Then
        strSQLWhere = "tblPerson.skillID = " & Me.SkillID & " AND "
    End If
    If Me.LevelID > 0 Then
        strSQLWhere = "tblPerson.levelID = " & Me.LevelID & " AND "
    End If
    
    strSQLCrit = strSQLPerson
    If strSQLWhere > "" Then
        strSQLCrit = strSQLCrit & " WHERE " & Left(strSQLWhere, Len(strSQLWhere) - 5)
    End If
    strSQLCrit = strSQLCrit & " ORDER BY [PersSName] & ', ' & [PersFName];"
    Me.PersID.RowSource = strSQLCrit
    Me.PersID.Requery

 

[neemi]

It works but if effects what data is visible on the other records on the form even if they do not have focus! Which is no good!

 

[postermmxvicom]

Neemi,

Let me go a different direction than you are going.

I think I might have a solution for you. I once needed a query that returned all records if a control on a form was empty and only certain matching records if that control had a value, just like your situation.

I finally achieved this see thread701-1396552.

You could make the query for your row source like I ended up making my query. I think you would also have to add a line of VBA in combobox1's afterUpdate event so that when it is updated the combobox2 is requeried.

i.e.

combobox2.requery

This way uses a small amount of VBA code (just the requery), the rest of your work is setting up the right queries for your rowsource with the query builder. Is this an acceptable strategy?

Aaron

One last thing: Sometimes I wonder; "Is that someone's signature? Or do they type that at the end of each post?

 

[TheAceMan1]

How are ya neemi . . .

[blue]It works but it effects what data is visible on the other records on the form even if they do not have focus! Which is no good![/blue][/code]
So the combobox is [blue]bound[/blue]!

You can't have a bound combobox which returns a [blue]dynamic recordset, because [purple]prior selections not included in the current recordset will show blank.[/purple][/blue]

Make the combo [blue]unbound[/blue] and update a bound textbox in its place . . .

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[postermmxvicom]

Good call TheAceMan1. I bet you are right. I was trying to figure out what was going on from his post, but was unable. You guys are sharp.

One last thing: Sometimes I wonder; "Is that someone's signature? Or do they type that at the end of each post?

 

[neemi]

I sussed it and noticed that your replies were pointing me in the direction of what I ended up doing...

i ended up coding in text boxes over the combo boxes that allowed the info typed in to be fulfilled in the combo box. The script in the code actually 'moves focus' to the combo box behind this textbox when user enters this field'

Thanks for all your help peops.

very much apreciated.
neemi

 

[TheAceMan1]

neemi . . .

As an Idea of shortening code and better methods to perform your task, I tweaked your code that sets up the rowsource for your [blue]Person[/blue] combobox.

To start, in the [blue]Tag[/blue] property of [blue]LocationID[/blue], [blue]CapID[/blue], [blue]SkillID[/blue], and [blue]LevelID[/blue], enter a question mark [red]?[/red] ([purple]no quotations please[/purple]).

Next, disable your code, then copy/paste the following:

[blue]   Dim ctl As Control, SQL As String, Cri As String
      
   SQL = "SELECT PersID, " & _
         "[PersSName] & ', ' & [PersFName] AS PersName, " & _
         "Freelance, PersJob, LocationID, CapID, SkillID, LevelID " & _
         "FROM tblPerson "
    
    For Each ctl In Me.Controls
      If ctl.Tag = "?" Then
         If ctl > 0 Then
            If Cri <> "" Then
               Cri = Cri & " AND [" & ctl.Name & "] = " & ctl & " "
            Else
               Cri = "WHERE [" & ctl.Name & "] = " & ctl & " "
            End If
         End If
      End If
   Next
    
   Me!PersID.RowSource = SQL & Cri & "ORDER BY [PersSName], [PersFName];"[/blue]

[blue]Cheers! . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[neemi]

Cheers aceman for that but my original function developed a little bit into the following:

Private Sub FilterData()
Dim strFilter As String
Dim strSummaryFilter As String
    
If Me.ClientID > "" Then
    strFilter = strFilter & "[ClientID] = " & Me.ClientID & " AND "
End If
If Me.ProjIDNo > "" Then
    strFilter = strFilter & "[ProjID] = " & Me.ProjIDNo & " AND "
End If
If Me.LocationID > "" Then
    strFilter = strFilter & "[LocationID] = " & Me.LocationID & " AND "
    strSummaryFilter = strSummaryFilter & "[LocationID] = " & Me.LocationID & " AND "
End If
If Me.CapID > "" Then
    strFilter = strFilter & "[CapID] = " & Me.CapID & " AND "
    strSummaryFilter = strSummaryFilter & "[CapID] = " & Me.CapID & " AND "
End If
If Me.SkillID > "" Then
    strFilter = strFilter & "[SkillID] = " & Me.SkillID & " AND "
    strSummaryFilter = strSummaryFilter & "[SkillID] = " & Me.SkillID & " AND "
End If
If Me.LevelID > "" Then
    strFilter = strFilter & "[LevelID] = " & Me.LevelID & " AND "
    strSummaryFilter = strSummaryFilter & "[LevelID] = " & Me.LevelID & " AND "
End If
If Me.PersID > "" Then
    strFilter = strFilter & "[persID] = " & Me.PersID & " AND "
    strSummaryFilter = strSummaryFilter & "[persID] = " & Me.PersID & " AND "
End If

With Me.fsbResourceDetails.Form
    If strFilter > "" Then
        Me.cmdRemoveFilter.Visible = True
        strFilter = Trim(Left(strFilter, Len(strFilter) - 5))
        .Filter = strFilter
        .FilterOn = True
        
        If strSummaryFilter > "" Then strSummaryFilter = Trim(Left(strSummaryFilter, Len(strSummaryFilter) - 5))
        CalculateSummaryResource Me.wkEnding, strSummaryFilter
    Else
        Me.fsbResourceDetails.SetFocus
        Me.cmdRemoveFilter.Visible = False
        .FilterOn = False
        
        CalculateSummaryResource Me.wkEnding
    End If
End With

 

[neemi]

OK... I have got the combo box rowsource requerying done with the aid of another text box on top of the combo box which sets focus to the combo box.

Once the rowsource SQL has been built i do the following on the feilds gotfocus property...

    Me.PersID.RowSource = strSQLCrit
    Me.PersID.Requery

These fields also have conditional formatting on it so to highlight ones that match certain criteria. but as i am requerying the field the CF requeries/flashes/reformatts?

Is there any way I can stop this from flashing?

i have only applied the CF to the text box on top of the combo box as if i apply it to the combo box as well when the combobox drop down is selected I have to select it again for it to list the combo options!!

Hope you understand?

regards,
neemi