How to hide a value from a combobox

  • Thread starter Thread starter David
  • Start date Start date
D

David

Finally got back together after being invaded by a worm!
If this question did get posted earlier this week
-please excuse me for reposting it-
I did have to start from scratch.

I have got a field that will have entries that need to be hide from a
specific combobox. Is there a way to use a checkbox to hide these entries.
That way I can give someone the chance to view what is hid using the same
combobox. What I am trying to hide will start with the 3 letters of "old".
If not how would you handle something that you did not want to show up in a
combobox? This entry does not need to be deleted.
Thanks,
David
 
David,

Is the combobox looking up values from a table?

If so, then you could modify the combobox using the click event of the check
box you used. If you assume the following:

The checkbox is named chkShowOld
The combobox is name cboMyCombo
The table is named tblTest
The field to hide data is named strMyField

Then the code for the check box would be as follows:

Private Sub chkShowOld_Click()

Dim strSQL As String
' Check the value of the combobox
If Me.chkShowOld Then ' Combo is ticked - hide old entries
strSQL = "SELECT tblTest.strMyField FROM tblTest " & _
"WHERE strMyField Not Like 'old*';"
Else ' Show all entries
strSQL = "SELECT tblTest.strMyField FROM tblTest;"
End If
' Assign the sql string to the combos rowsource and requery
With Me.cboMyCombo
.RowSource = strSQL
.Requery
End With

End Sub

This is air code so it may contain mistakes, but it should give you an idea
on how to solve your problem.

HTH,

Neil.
 
That worked great! Is there a way to make it work every time the checkbox is
clicked.
Right now the code is in the oncurrent of the form, so that when the form is
open, it hides immediately, then I also put it in the onclick of the check
box.
It will hide and show only one time.
 
I feel like a dummy,
I set the default value to 1 instead of true, setting it to true took care
of that problem.

I see where I could use that to populated a form that show the same field
info.
The forms recordsource is set to a table , how would you modify that SQL
statement to populated a form instead of having it populated by a table?
 
Hi David,

You could use exactly the same SQL string to be a forms recordsource. If you
wanted a form to be synchronised with the combobox, you could modify the On
Click code to be as follows:

Private Sub chkShowOld_Click()

Dim strSQL As String
' Check the value of the combobox
If Me.chkShowOld Then ' Combo is ticked - hide old entries
strSQL = "SELECT tblTest.strMyField FROM tblTest " & _
"WHERE strMyField Not Like 'old*';"
Else ' Show all entries
strSQL = "SELECT tblTest.strMyField FROM tblTest;"
End If
' Assign the sql string to the combos rowsource and requery
With Me.cboMyCombo
.RowSource = strSQL
.Requery
End With
' Assign this same string to be the recordsource for a form
Me.RecordSource = strSQL ' Could be any other form as well
' But it must be open
otherwise an error would occur
Me.Requery

End Sub

HTH,

Neil.
 
That worked great as well, but with only one glitch.
When the recordsource is table(and the same with the first form with the
combobox) the sort is fine, but when it is populated by code, the sort is
out of order. Is there a way to sort with code?
Thanks
David
 
What do you mean 'added by code'?

The Select statement has an ORDER BY line which would sort the records by a
perticular field. Just add this to the string

strSQL = "SELECT tblTest.strMyField FROM tblTest " & _
"WHERE strMyField Not Like 'old*' " & _
"ORDER BY strMyField;"

strSQL = "SELECT tblTest.strMyField FROM tblTest & _
"ORDER BY strMyField;"

The above would sort all data in strMyField in ascending order. This field
can be any field in tblTest.

HTH,

Neil.
 
Back
Top