Textbox to display combobox value AFTER filtering

[excellenthelp]

Hello Accessers,



How may I have the textbox to display the value of the combobox AFTER filtering?

The combobox has a list of items that is used to filter the records (i.e. Certified, Suspended and Revoked). I would like the Textbox to display after the form has been filtered but not before. The filter macro has been designed for a separate button that is clicked to perform the filter after choosing an item in the combobox drop-down menu.

What I do not want (which is happening now) is that the textbox displays the combobox live just as the item is being selected and remains visible. It could confuse the users whether or not if it has been filtered or not.

The value in the textbox should remain blank until after the filter button I had developed is clicked upon. After the filter button is clicked on and the form is filtered, the textbox then should display the combobox value.

How? I know this is sooooo simple!

Any help will be appreciated!

[June7]

Why do you need textbox to show value in combobox if the combobox already shows the value?

If the combobox purpose is just to input filter criteria it should be UNBOUND.

A textbox should be BOUND to corresponding field to show value actually in the record.

[excellenthelp]

Hmm Okay, I have changed all of this... I am going to move forward with =TRIM function.

How may I trim values from Combobox (not its ID but text in the second column).

tbl Person
Address
Phone
Cert

tbl Cert
-ID
-Cert Status

tbl Cert is joined at tbl Person.Cert and tbl Cert.ID

The TRIM I am attempting to use is:

=Trim("# of total" & " " & [Cert] & " " & "certifiers")

After execution, the textbox displays "# of total 1 certifiers" instead of "# of total suspended certifiers". I want the second one with 'suspended' in it...

[excellenthelp]

Wait, there is no joined tables between these two!

[June7]

I don't know why you need Trim().

Refer to combobox columns by column property index value. Index begins with 0.

="# of total " & [Cert].[Column](1) & " certifiers"

However, the literal text will show even when the combobox is empty, so:

=IIf(IsNull([Cert]), Null, "# of total " & [Cert].[Column](1) & " certifiers")

[excellenthelp]

Thank you June7 for the help-

It still shows #NAME?

[excellenthelp]

I got it to work. YaY! However, how may I make sure this updates after the filter button is pressed NOT when the item is selected in the combobox?

[June7]

That would require code (macro or VBA) that sets value of textbox, instead of expression in ControlSource property.

Or

Code that sets Visible property of textbox.

[excellenthelp]

I have written a VBA code to execute these:
-When the form is filtered, the textbox is to change.
-The textbox will say "# of total certifiers" when the form is unfiltered.
-After "suspended" has been selected from the drop-down menu in the combobox and then the form has been filtered, the textbox will say "# of total suspended certifiers".
-When the filter is cleared, the textbox will return to "# of total certifiers".

Here is the attempted but failed code I wrote:

TotalCerts.ControlSource = "IIf(Forms!frmCertifyingAgentsDirectory.FilterOn=true,IIf(IsNull([LstAccredStatus]), ""# of total certifiers"", ""# of total "" & [LstAccredStatus].[Column](1) & "" certifiers""),""# of total certifiers"")"

Is there something I am writing incorrectly?

[excellenthelp]

Also to aid in the VBA writing, I am including the comprehensive code where the in-executable code is nested in:

Private Sub btnFilterCert_Click()    If IsNull(Me.LstAccredStatus) Then
        Me.FilterOn = False
    Else
        Me.Filter = "[AccredStatus] = " & Me.LstAccredStatus & ""
        Me.FilterOn = True
        TotalCerts.SetFocus
        TotalCerts.ControlSource = "IIf(Forms!frmCertifyingAgentsDirectory.FilterOn=true,IIf(IsNull([LstAccredStatus]), ""# of total certifiers"", ""# of total "" & [LstAccredStatus].[Column](1) & "" certifiers""),""# of total certifiers"")"
        LstAccredStatus.SetFocus
    End If
End Sub

[June7]

Is this textbox on a Single View form? Don't set the ControlSource property. Do the calc in VBA and set the textbox Value with the calculated result.

[excellenthelp]

I figured that out. =D Here's the code that works now below:

Private Sub LstAccredStatus_AfterUpdate()    Dim intCount As Integer
    
    If IsNull(Me.LstAccredStatus) Then
        intCount = DCount("*", "tblAIA_ACAList", "ACAName")
        Me.TotalCerts = intCount
    Else
        intCount = DCount("*", "tblAIA_ACAList", "AccredStatus = " & Me.LstAccredStatus)
        Me.TotalCerts = intCount
    End If
    
End Sub

[excellenthelp]

June7,

Thank you for the help!

Marking this as SOLVED.