Add to the ProductsService class a method that returns distinct IEnumerable<T> elements where T is a model that encapsulates properties represent the required database fields. Remove the DISTINCT predicate from the SELECT statement if the database table does not contain duplicate products.

Public Class ProductsService
    Private Shared Function GetOledbConnectionString() As String
        Return "Provider=Microsoft.ACE.OLEDB.12.0;
                Data Source=|DataDirectory|test03092024.accdb;
                Persist Security Info=False;"
    End Function

    ' ...

    Friend Shared Function GetDistinctProducts() As IEnumerable(Of Product)
        Dim sql = "SELECT DISTINCT ProductName, Unit 
                   FROM Products 
                   ORDER BY ProductName"
        Using _conn = New OleDbConnection(GetOledbConnectionString())
            Return _conn.Query(Of Product)(sql)
        End Using
    End Function
End Class

Public Class Product
    Public Property ProductName As String
    Public Property Unit As String

    Public Overrides Function ToString() As String
        Return $"{ProductName} {Unit}".Trim()
    End Function
End Class

Override the Form’s OnLoad method to set the grid up. Bind an empty BindingList<Product>, and initialize a class field of type Dictionary<Of String, String) where the Keys are the products and the Values are the corresponding units. The dictionary is used to:

• Fill the empty DataGridViewTextBoxEditingControl.AutoCompleteCustomSource string collection with the Keys (the products) in the DataGridView.EditingControlShowing event.

• Validate the ProductName cell in the DataGridView.CellValidating event. The Keys must contain the user input to be pushed to the underlying data source. Otherwise, the cell remains focused in edit mode and the current row displays the error icon.

• Set the corresponding Unit of the valid product in the DataGridView.CellValidated event.

• Set the corresponding unit of the last selected product – if any – in the DataGridView.CellEndEdit event if the user chooses to cancel editing by pressing the Esc key.

Public Class Form1
    Private dictProducts As Dictionary(Of String, String)

    Protected Overrides Sub OnLoad(e As EventArgs)
        MyBase.OnLoad(e)

        dictProducts = ProductsService.GetDistinctProducts().
            ToDictionary(Function(p) p.ProductName, Function(p) p.Unit)

        DataGridView1.DataSource = New BindingList(Of Product)
        DataGridView1.Columns("Unit").ReadOnly = True
    End Sub

    Private Sub DataGridView1_EditingControlShowing(
        sender As Object,
        e As DataGridViewEditingControlShowingEventArgs) Handles _
        DataGridView1.EditingControlShowing
        If TypeOf e.Control Is TextBox Then
            Dim dgv = DirectCast(sender, DataGridView)
            Dim tb = DirectCast(e.Control, TextBox)

            If dgv.CurrentCell.ColumnIndex = dgv.Columns("ProductName").Index Then
                If tb.AutoCompleteCustomSource.Count = 0 Then
                    tb.AutoCompleteSource = AutoCompleteSource.CustomSource
                    tb.AutoCompleteCustomSource.AddRange(dictProducts.Keys.ToArray())
                End If
                tb.AutoCompleteMode = AutoCompleteMode.Suggest
            Else
                tb.AutoCompleteMode = AutoCompleteMode.None
            End If
        End If
    End Sub

    Private Sub DataGridView1_CellValidating(
        sender As Object,
        e As DataGridViewCellValidatingEventArgs) _
        Handles DataGridView1.CellValidating
        Dim dgv = DirectCast(sender, DataGridView)

        If e.ColumnIndex = dgv.Columns("ProductName").Index AndAlso
           e.RowIndex <> dgv.NewRowIndex Then
            Dim key = e.FormattedValue?.ToString()

            If String.IsNullOrEmpty(key) OrElse
               Not dictProducts.ContainsKey(key) Then
                Dim boundItem = DirectCast(dgv.Rows(e.RowIndex).DataBoundItem, Product)

                boundItem.Unit = Nothing
                dgv.Rows(e.RowIndex).ErrorText = "Invalid Product!"
                dgv.UpdateCellValue(dgv.Columns("Unit").Index, e.RowIndex)
                e.Cancel = True
            End If
        End If
    End Sub

    Private Sub DataGridView1_CellValidated(
        sender As Object,
        e As DataGridViewCellEventArgs) Handles DataGridView1.CellValidated
        Dim dgv = DirectCast(sender, DataGridView)

        If e.ColumnIndex = dgv.Columns("ProductName").Index Then
            Dim boundItem = TryCast(dgv.Rows(e.RowIndex).DataBoundItem, Product)
            If boundItem IsNot Nothing Then
                Dim value = dictProducts(boundItem.ProductName)
                If value <> boundItem.Unit Then
                    boundItem.Unit = value
                    dgv.UpdateCellValue(dgv.Columns("Unit").Index, e.RowIndex)
                End If
                dgv.Rows(e.RowIndex).ErrorText = Nothing
            End If
        End If
    End Sub

    Private Sub DataGridView1_CellEndEdit(
        sender As Object,
        e As DataGridViewCellEventArgs) Handles DataGridView1.CellEndEdit
        Dim dgv = DirectCast(sender, DataGridView)

        If e.ColumnIndex = dgv.Columns("ProductName").Index Then
            If Not String.IsNullOrEmpty(dgv.Rows(e.RowIndex).ErrorText) Then
                Dim boundItem = DirectCast(dgv.Rows(e.RowIndex).DataBoundItem, Product)
                Dim unit As String = Nothing

                If String.IsNullOrEmpty(boundItem.Unit) AndAlso
                   dictProducts.TryGetValue(boundItem.ProductName, unit) Then
                    boundItem.Unit = unit
                    dgv.UpdateCellValue(dgv.Columns("Unit").Index, e.RowIndex)
                    dgv.Rows(e.RowIndex).ErrorText = Nothing
                End If
            End If
        End If
    End Sub
End Class

To get the underlying data source.

Dim src = DirectCast(DataGridView1.DataSource, BindingList(Of Product))

If the database table contains products with duplicate names and different units and changing the names is not an option, then you need to control what the grid should display and what it should produce.

Override the Onload method to populate a class field of type Dictionary(Of String, Product). To get unique keys for the dictionary, we get here a collection of what the model’s ToString method override returns.

Public Class Form1
    Private dictProducts As Dictionary(Of String, Product)

    Protected Overrides Sub OnLoad(e As EventArgs)
        MyBase.OnLoad(e)

        dictProducts = ProductsService.GetDistinctProducts().
            ToDictionary(Function(p) p.ToString(), Function(p) p)

        DataGridView1.DataSource = New BindingList(Of Product)
        DataGridView1.Columns("Unit").ReadOnly = True
    End Sub
End Class

Keep the EditingControlShowing event as is. The TextBox auto complete list displays the ProductName and the corresponding Unit values so the user knows which duplicate product to select.

Edit the CellValidating, CellValidated, and CellEndEdit events as follows:

Private Sub DataGridView1_CellValidating(
    sender As Object,
    e As DataGridViewCellValidatingEventArgs) _
    Handles DataGridView1.CellValidating
    Dim dgv = DirectCast(sender, DataGridView)

    If e.ColumnIndex = dgv.Columns("ProductName").Index AndAlso
        e.RowIndex <> dgv.NewRowIndex Then
        Dim key As String

        If dgv.IsCurrentCellInEditMode Then
            Dim p = dictProducts.Values.
                FirstOrDefault(Function(x) x.ProductName.
                Equals(e.FormattedValue.
                ToString(), StringComparison.InvariantCultureIgnoreCase))

            If p IsNot Nothing Then
                key = p.ToString()
            Else
                key = e.FormattedValue.ToString()
            End If
        Else
            key = dgv(e.ColumnIndex, e.RowIndex).Value?.ToString().Trim()
        End If

        If String.IsNullOrEmpty(key) OrElse
            Not dictProducts.ContainsKey(key) Then
            Dim boundItem = DirectCast(dgv.Rows(e.RowIndex).DataBoundItem, Product)

            boundItem.Unit = Nothing
            dgv.Rows(e.RowIndex).ErrorText = "Invalid Product!"
            dgv.UpdateCellValue(dgv.Columns("Unit").Index, e.RowIndex)
            e.Cancel = True
        End If
    End If
End Sub

Private Sub DataGridView1_CellValidated(
    sender As Object,
    e As DataGridViewCellEventArgs) Handles DataGridView1.CellValidated
    Dim dgv = DirectCast(sender, DataGridView)

    If e.ColumnIndex = dgv.Columns("ProductName").Index Then
        Dim p As Product = Nothing
        Dim key = dgv(e.ColumnIndex, e.RowIndex).Value?.ToString().Trim()

        If key Is Nothing Then Return
        If dictProducts.TryGetValue(key, p) Then
            Dim boundItem = TryCast(dgv.Rows(e.RowIndex).DataBoundItem, Product)

            If p.Unit <> boundItem.Unit Then
                boundItem.Unit = p.Unit
                dgv.UpdateCellValue(dgv.Columns("Unit").Index, e.RowIndex)
            End If
            dgv.Rows(e.RowIndex).ErrorText = Nothing
        End If
    End If
End Sub

Private Sub DataGridView1_CellEndEdit(
    sender As Object,
    e As DataGridViewCellEventArgs) Handles DataGridView1.CellEndEdit
    Dim dgv = DirectCast(sender, DataGridView)
    Dim src = DirectCast(dgv.DataSource, BindingList(Of Product))

    If src.Count = e.RowIndex Then Return
    If e.ColumnIndex = dgv.Columns("ProductName").Index AndAlso
        Not String.IsNullOrEmpty(dgv.Rows(e.RowIndex).ErrorText) Then
        Dim key = dgv(e.ColumnIndex, e.RowIndex).Value?.ToString().Trim()
        Dim p As Product = Nothing
        Dim boundItem = DirectCast(dgv.Rows(e.RowIndex).DataBoundItem, Product)

        If String.IsNullOrEmpty(boundItem.Unit) AndAlso
                dictProducts.TryGetValue(key, p) Then
            boundItem.Unit = p.Unit
            dgv.UpdateCellValue(dgv.Columns("Unit").Index, e.RowIndex)
            dgv.Rows(e.RowIndex).ErrorText = Nothing
        End If
    End If
End Sub

Handle the grid’s CellFormatting event to display the ProductName value instead of the value selected from the auto-complete list.

Private Sub DataGridView1_CellFormatting(
    sender As Object,
    e As DataGridViewCellFormattingEventArgs) Handles _
    DataGridView1.CellFormatting
    Dim dgv = DirectCast(sender, DataGridView)
    Dim src = DirectCast(dgv.DataSource, BindingList(Of Product))

    If src.Count = e.RowIndex Then Return
    If e.ColumnIndex = dgv.Columns("ProductName").Index Then
        Dim key = dgv(e.ColumnIndex, e.RowIndex).Value?.ToString().Trim()
        Dim p As Product = Nothing

        If Not String.IsNullOrWhiteSpace(key) AndAlso
            dictProducts.TryGetValue(key, p) Then
            e.Value = p.ProductName
            e.FormattingApplied = True
        End If
    End If
End Sub

To get the underlying data source with the original ProductName values:

Dim displayList = DirectCast(DataGridView1.DataSource, BindingList(Of Product))
Dim outputList = displayList.
    Select(Function(p) New Product With {
    .ProductName = dictProducts(p.ProductName).ProductName,
    .Unit = p.Unit
})
' ... etc.