DataGrid search using combo box

[toon10]

Hi

I have a datagrid which displays a lot of data from an SQL table. The first row is a part number.

I have a combo box with the relevant part numbers listed. All I want to do is select a part number from the combo box and have the datagrid select the row where the part number matches.

I originally had the datagrid set up so that when you selected a part number, it filtered the grid to display only that part number which worked well but unfortunately, I can't modify the data in that filtered view, only in the full list view.

Any ideas?

Thanks

My original code which filters on part number but doesn't remember any changes made. This is why I wish to select the row on the datagrid which matches the combo box entry and then I will be able find the record and edit it without it losing the changes.

Set cn = New ADODB.Connection
Set RS = New ADODB.Recordset

If cboPart.Text = "" Then 'if no part number is selected then list all parts
    SQL = "SELECT Part, TaskType, TaskStatus, Batch, Source, Destination, Item, SourceQty, DestinationQty, UOM, UserId, Serial, Sort, DTAdded, DTUpdated, TaskSubType, SAPQty, Land1, Priority, HasHeaderText, HasLineText, HeaderText, LineText, Override, TaskStage From BR_TaskList ORDER BY Part"
Else 'else only list the records relating to the selected part
    SQL = "SELECT Part, TaskType, TaskStatus, Batch, Source, Destination, Item, SourceQty, DestinationQty, UOM, UserId, Serial, Sort, DTAdded, DTUpdated, TaskSubType, SAPQty, Land1, Priority, HasHeaderText, HasLineText, HeaderText, LineText, Override, TaskStage From BR_TaskList WHERE Part = '" & cboPart.Text & "' ORDER BY Part"
End If

cn.Open "Provider=SQLOLEDB;Data Source=10.225.100.150;Initial Catalog=Live_MBC;User Id=sa"
cn.CursorLocation = adUseClient '***VERY IMPORTANT TO AVOID ERROR 7004!
RS.Open SQL, cn, adOpenDynamic, adLockBatchOptimistic

Set DataGrid1.DataSource = RS
DataGrid1.Refresh

 

[Glasgow]

Instead of using separate queries, why not use a single query (retrieveing all parts) and then either use:

The recordset's .Filter property so that you only see the matching records.

or

The recordset's .Find method to locate the first row with a matching part number.