Check the recordcount of a combo Box If 0 set additions to false

Hello, I have a combobox (cbo1) that its row source is a sql that allows me to select data and for the next record the data selected previousl wont show in the combo box. This happens for each record until there isn't any data to select in the combo box. I want to set additions to false when there isn't any data in the combo. I tried:

Code:

---

Private Sub cboComponent_Dirty(Cancel As Integer)
    If cboComponent.RecordsetClone.RecordCount > 1 Then
        Cancel = True
    Else
        Me.AllowAdditions = False
    End If
End Sub

---

Use the Combo bx's listcount property
cboComponent.listcount

Quote:

---

Originally Posted by OBP

Use the Combo bx's listcount property
cboComponent.listcount

---

OBP, that works BUT not fully. What I mean is ok there are 11 choices for the combo box and if I stayed on the page and chose all 11 there won't be any choices left and there wouldn't be a new record because of the code BUT if I close the form and go back then there is a new last record in my continuous form. How do I fix that from happening?

Code:

---

Private Sub cboComponent_Dirty(Cancel As Integer)
    If cboComponent.ListCount > 1 Then
        Cancel = True
    Else
        Me.AllowAdditions = False
    End If
End Sub

---

Here is a VBA string that would be the same that's in the combo box.

Code:

---

strSql = "SELECT ComponentID, Component FROM tbl_Components " & _
          " WHERE ComponentID Not In (Select ComponentID from tbl_ComponentParts where ProductID = " & Me.Parent.cboProduct & ") " & _
          " AND IsInactive = False"

---

If you turn that VBA SQL into a Query what result do you get?

Are you saying that the SQL finds records that do not meet it's conditions?

Quote:

---

Originally Posted by OBP

If you turn that VBA SQL into a Query what result do you get?

Are you saying that the SQL finds records that do not meet it's conditions?

---

Ok, I created the query and if I have 3 selections left in the cbo then there are 3 records left showing the 3 selections in the test query. If I used all of the cbo selections then the test query brings up no data.

Quote:

---

Originally Posted by oxicottin

Ok, I created the query and if I have 3 selections left in the cbo then there are 3 records left showing the 3 selections in the test query. If I used all of the cbo selections then the test query brings up no data.

---

OBP, your first suggestion worked I just needed to put it in the Current event also... Thank You!

Great :thumb