Removing Blank Entries From a Combo Box in a Form

  • Thread starter Thread starter silva
  • Start date Start date
S

silva

I'm currently using the following code to use a combo box as a search tool in
a form:

Private Sub ItemSearchBox_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[guest_id] = " & Str(Nz(Me![ItemSearchBox], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

The combo box's list is populated with data from a particular field in a
table, but not all recoards have data in this field. Is there any way to
modify this code so that the blank entries don't appear in the combo box? If
not, is there an alternate method? There are a lot of records (somewhere well
over 8,000), and a lot of them have this field blank, therefor taking quite
some time to scroll through to where actual entries begin.
 
I'm currently using the following code to use a combo box as a search tool in
a form:

Private Sub ItemSearchBox_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[guest_id] = " & Str(Nz(Me![ItemSearchBox], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

The combo box's list is populated with data from a particular field in a
table, but not all recoards have data in this field. Is there any way to
modify this code so that the blank entries don't appear in the combo box? If
not, is there an alternate method? There are a lot of records (somewhere well
over 8,000), and a lot of them have this field blank, therefor taking quite
some time to scroll through to where actual entries begin.
Click to expand...

Create a query from your table. Include just the field(s) you need.
As criteria on the [ParticularField] in the query, write:
Is Not Null
Sort the query however you wish.

Then use this query as the row source for the combo box, not the
table.
 
Back
Top