Refreshing combo box on datasheet  view loosing information in previous records

Hi

I have 3 combo boxes on a subform: Product, Stock, part Number
Depending on the selection of the first two the record source of the part number combo box is change to filter records.  This works ok.  The problem is if I enter a second record on the subform (in datasheet view) the record that was entered in the first row loses its data in the part number combo box.  

This is the code I use to refresh the combo box;

Private Sub cboRVNumber_Enter()


    Dim sql As String
   
If IsNull(Me.cboProductType.Column(1)) = False And IsNull(Me.cboStockType.Column(1)) = True Then
        sql = "SELECT tblStock.stockId, tblStock.rvNumber, tblStock.description,tblProductType.productType " & _
              "FROM tblProductType INNER JOIN tblStock ON tblProductType.productTypeID = tblStock.productType " & _
              "WHERE tblProductType.productType='" & Me.cboProductType.Column(1) & "'"
    ElseIf IsNull(Me.cboProductType.Column(1)) = True And IsNull(Me.cboStockType.Column(1)) = False Then
        sql = "SELECT tblStock.stockId, tblStock.rvNumber, tblStock.description,tblStockTypes.stockType " & _
              "FROM tblStockTypes INNER JOIN tblStock ON tblStockTypes.stockTypeID = tblStock.stockType " & _
              "WHERE tblStockTypes.stockType='" & Me.cboStockType.Column(1) & "'"
    ElseIf IsNull(Me.cboProductType.Column(1)) = False And IsNull(Me.cboStockType.Column(1)) = False Then
        sql = "SELECT tblStock.stockId, tblStock.rvNumber, tblStock.description,tblProductType.productType, tblStockTypes.stockType " & _
              "FROM tblStockTypes INNER JOIN (tblProductType INNER JOIN tblStock ON " & _
              "tblProductType.productTypeID = tblStock.productType) ON tblStockTypes.stockTypeID = tblStock.stockType " & _
              "WHERE tblProductType.productType= '" & Me.cboProductType.Column(1) & "' AND tblStockTypes.stockType='" & Me.cboStockType.Column(1) & "'"
    Else
        sql = "SELECT tblStock.stockId, tblStock.rvNumber FROM tblStock"
    End If
   
    cboRVNumber.RowSource = sql
    cboRVNumber.Requery
End Sub

Can anyone help with this please?

ThanksStart Free Trial