Hi,
I am trying to use valuelists to fill up the column of dropdown list style. I want to fill the item name column depending upon the selection made by the user in the itemGroup column and similary the unit column shd get filled up with the unit of the item selected . I am able to do it for the first row but in the second row if I select another group all the previously filled up rows get changed . I don't know How to solve the problem. I am sending the code

Option Explicit
Dim cn As ADODB.Connection
Dim rs, rs_Data, rs_Data1 As ADODB.Recordset
Dim msql As String
Dim cnstr As String
Dim i, j As Integer
Private Sub Form_Load()
j = 1
Set rs = New ADODB.Recordset
Set cn = New ADODB.Connection
cnstr = "Driver={Microsoft Access driver (*.mdb)};DBQ=C:\Windows\Desktop\Export.mdb"
'cnstr = "Driver={Microsoft Access driver (*.mdb)};DBQ=C:\Documents and Settings\administrator\Desktop\Export.mdb"
cn.Open cnstr
msql = "Select * from On_Enquiry_Trl where 1=2"

rs.Open msql, cn, adOpenKeyset, adLockBatchOptimistic
Set SSUltraGrid1.DataSource = rs
SSUltraGrid1.Bands(0).Columns.Add
SSUltraGrid1.Bands(0).Columns(10).Width = 0
Set rs = Nothing
InitialiseValueLists
PopulateValueLists
SSUltraGrid1.Bands(0).Columns(0).Hidden = True
SSUltraGrid1.Bands(0).Columns(1).Hidden = True
SSUltraGrid1.Bands(0).Columns(2).Hidden = True
SSUltraGrid1.Bands(0).Columns(3).Hidden = True
SSUltraGrid1.AddNewBox.Hidden = False
With SSUltraGrid1.AddNewBox.Appearance
.BackColor = vbBlue
.ForeColor = vbWhite
.Font.Size = 8
End With
With SSUltraGrid1.AddNewBox.ButtonAppearance
.BackColor = vbWhite
.ForeColor = vbBlue
End With
SSUltraGrid1.UpdateMode = ssUpdateOnUpdate
SSUltraGrid1.AddNewBox.ButtonBorderStyle = ssBorderStyleRaised
SSUltraGrid1.AddNewBox.ButtonConnectorColor = vbBlue
SSUltraGrid1.AddNewBox.Style = ssAddNewBoxStyleFull
SSUltraGrid1.AddNewBox.Prompt = "Press to add an enquiry"
End Sub
Public Sub InitialiseValueLists()
With SSUltraGrid1.ValueLists
.Add "ItemGrp_Code"
.Add "Item_Code"
.Add "Unit"
End With
End Sub
Public Sub PopulateValueLists()
'Filling the Value Lists of Grids
Set rs_Data = New ADODB.Recordset
Dim ItemGrpCode, ItemCode, ItemDesc As String
Dim ItemGrpDesc As String
msql = "SELECT distinct ItemGroup_Mst.ItemGrp_Code, ItemGroup_Mst.ItemGrp_Desc" & _
" From Item_Mst, ItemGroup_Mst WHERE (([ItemGroup_Mst]![ItemGrp_Code]=[Item_Mst]![Item_GrCode]))"

If rs_Data.State = 1 Then rs_Data.Close
rs_Data.Open msql, cn, adOpenKeyset, adLockPessimistic
'SSUltraGrid1.ValueLists("ItemGrp_Code").ValueListItems.Clear
'rs.MoveFirst
If rs_Data.RecordCount > 0 Then
For i = 0 To rs_Data.RecordCount - 1
ItemGrpCode = rs_Data.Fields("ItemGrp_Code").Value
ItemGrpDesc = rs_Data.Fields("ItemGrp_Desc").Value
SSUltraGrid1.ValueLists("ItemGrp_Code").ValueListItems.Add ItemGrpCode, ItemGrpDesc
rs_Data.MoveNext
Next i
End If
' SSUltraGrid1.ValueLists("ItemGrp_Desc").Appearance.BackColor = vbRed
Set SSUltraGrid1.Bands(0).Columns("ItemGrp_Code").ValueList = SSUltraGrid1.ValueLists("ItemGrp_Code")
Set SSUltraGrid1.Bands(0).Columns("UOM_Code").ValueList = SSUltraGrid1.ValueLists("Unit")
Set SSUltraGrid1.Bands(0).Columns("Item_Code").ValueList = SSUltraGrid1.ValueLists("Item_Code")

SSUltraGrid1.Bands(0).Columns("ItemGrp_Code").Style = ssStyleDropDown
SSUltraGrid1.Bands(0).Columns("Item_Code").Style = ssStyleDropDown
SSUltraGrid1.Bands(0).Columns("UOM_Code").Style = ssStyleDropDown
SSUltraGrid1.Bands(0).Columns("Qty").Style = ssStyleEdit
End Sub
Private Sub SSUltraGrid1_AfterCellActivate()
If SSUltraGrid1.ActiveCell.Column.Header.Caption = "" Then
SSUltraGrid1.AddNewBox.Hidden = False
End If
End Sub

Private Sub SSUltraGrid1_AfterRowsDeleted()
Dim aRow As SSRow
Dim k As Integer
k = 1
Set aRow = SSUltraGrid1.GetRow(ssChildRowFirst)
aRow.Cells(4).Value = k
Do Until Not aRow.HasNextSibling
Set aRow = aRow.GetSibling(ssSiblingRowNext)
k = k + 1
aRow.Cells(4).Value = k
Loop
SSUltraGrid1.Refresh ssRefreshDisplay
End Sub

Private Sub SSUltraGrid1_BeforeCellListDropDown(ByVal Cell As UltraGrid.SSCell, ByVal Cancel As UltraGrid.SSReturnBoolean)
Dim ItemCode, ItemDesc As String
Dim TypeCode, TypeDesc As String
Set rs_Data1 = New ADODB.Recordset
Dim ItemGrpCode As String
Dim ItemGrpDesc As String

If SSUltraGrid1.ActiveCell.Column.BaseColumnName = "ItemGrp_Code" Then
msql = "SELECT distinct ItemGroup_Mst.ItemGrp_Code, ItemGroup_Mst.ItemGrp_Desc" & _
" From Item_Mst, ItemGroup_Mst WHERE (([ItemGroup_Mst]![ItemGrp_Code]=[Item_Mst]![Item_GrCode]))"

If rs_Data1.State = 1 Then rs_Data1.Close
rs_Data1.Open msql, cn, adOpenKeyset, adLockPessimistic
SSUltraGrid1.ValueLists("ItemGrp_Code").ValueListItems.Clear
'rs.MoveFirst
If rs_Data1.RecordCount > 0 Then
For i = 0 To rs_Data1.RecordCount - 1
ItemGrpCode = rs_Data1.Fields("ItemGrp_Code").Value
ItemGrpDesc = rs_Data1.Fields("ItemGrp_Desc").Value
SSUltraGrid1.ValueLists("ItemGrp_Code").ValueListItems.Add ItemGrpCode, ItemGrpDesc
rs_Data1.MoveNext
Next i
End If
ElseIf SSUltraGrid1.ActiveCell.Column.BaseColumnName = "Item_Code" Then
'SSUltraGrid1.Bands(0).Columns("ItemGrp_Code").Selected = True
' MsgBox SSUltraGrid1.ActiveRow.Cells(5).Value
msql = ""
msql = "Select item_grcode,item_Name,item_code from item_mst where item_grcode in (select itemgrp_code from itemgroup_mst where itemgrp_code='" & SSUltraGrid1.ActiveRow.Cells(5).Value & "')"

If rs_Data1.State = 1 Then rs_Data1.Close
rs_Data1.Open msql, cn, adOpenKeyset, adLockPessimistic
SSUltraGrid1.ValueLists("Item_Code").ValueListItems.Clear
If rs_Data1.RecordCount > 0 Then
For i = 0 To rs_Data1.RecordCount - 1
ItemCode = rs_Data1.Fields("Item_Code").Value
ItemDesc = rs_Data1.Fields("Item_Name").Value
SSUltraGrid1.ValueLists("Item_Code").ValueListItems.Add ItemCode, ItemDesc
rs_Data1.MoveNext
Next i

End If
ElseIf SSUltraGrid1.ActiveCell.Column.BaseColumnName = "UOM_Code" Then
msql = " "
msql = "Select type_code,type_desc from type_mst where type_code='UN' and type_category in (select unit from item_mst where item_code='" & SSUltraGrid1.ActiveRow.Cells(6).Value & "')"
If rs_Data1.State = 1 Then rs_Data1.Close
rs_Data1.Open msql, cn, adOpenKeyset, adLockPessimistic
SSUltraGrid1.ValueLists("Unit").ValueListItems.Clear
If rs_Data1.RecordCount > 0 Then
For i = 0 To rs_Data1.RecordCount - 1
TypeCode = Trim(rs_Data1.Fields("Type_Code").Value)
TypeDesc = Trim(rs_Data1.Fields("Type_Desc").Value)
SSUltraGrid1.ValueLists("Unit").ValueListItems.Add TypeCode, TypeDesc
rs_Data1.MoveNext
Next i
End If
End If
End Sub
Private Sub SSUltraGrid1_InitializeLayout(ByVal Context As UltraGrid.Constants_Context, ByVal Layout As UltraGrid.SSLayout)
SSUltraGrid1.Bands(0).Override.HeaderAppearance.BackColor = &H996633
SSUltraGrid1.Bands(0).Override.HeaderAppearance.ForeColor = vbWhite
SSUltraGrid1.Bands(0).Override.HeaderAppearance.Font.Bold = True
SSUltraGrid1.Bands(0).Override.RowAppearance.BackColor = &HC0FFFF
SSUltraGrid1.Bands(0).ColHeadersVisible = True
SSUltraGrid1.Bands(0).Columns(7).Header.Caption = "Unit"
SSUltraGrid1.Bands(0).Columns(8).Header.Caption = "Quantity"
End Sub
Private Sub SSUltraGrid1_InitializeRow(ByVal Context As UltraGrid.Constants_Context, ByVal Row As UltraGrid.SSRow, ByVal ReInitialize As Boolean)
j = 1
Dim aRow As SSRow
SSUltraGrid1.AddNewBox.Hidden = True
Set aRow = SSUltraGrid1.GetRow(ssChildRowFirst)
Do Until Not aRow.HasNextSibling
Set aRow = aRow.GetSibling(ssSiblingRowNext)
j = j + 1

Loop
SSUltraGrid1.AddNewBox.Hidden = True
Row.Cells("Entry_SrNo").Value = j

End Sub



Thanks in anticipation