Hi,
Sorry for such a long post, but I desperately need help as this code i
working in one file but not in another. Let me explain the problem-
I have an excel file which has 5 rows of 3 comboBoxes each.
Each row represent a query and the rows are joined by OptionButtons.
My objective is that based on the option selected in First ComboBox i
a row, appropriate relation is defined in second ComboBox and th
values matching for the option selected in the first combobox is give
as a dropdown list in the third combobox.
The first ComboBox picks its options in the dropdownlist throug
ListFillRange. Options in the second Combobox is added through a code.
The third combox picks up the data from Column AR which is fille
dynamically through a query and its value depends on option selected i
ComboBox1.
How three boxes are filled is shown through a sample code he

Private Sub ComboBox1_LostFocus()
direc = Sheet1.Range("BH1").Value 'BH1 is the database path
datab = Sheet1.Range("BH2").Value 'BH2 is the database name
Value = ComboBox1.Text
ComboBox2.Clear
'ComboBox3.Clear
ComboBox2.Value = ""
ComboBox3.Value = ""

ComboBox2.Style = fmStyleDropDownList
ComboBox3.Style = fmStyleDropDownList

' To ensure that there is entry in ComboBox 1

If ComboBox1.Text = Empty Then
val2 = MsgBox("Please Enter Value in the Field Box", vbOKOnly
"Error")
Exit Sub
End If

' Column AR is the result destination

Sheet1.Columns("AR:AR").ClearContents
Sheet1.Columns("AR:AR").NumberFormat = "General"
read1 = 2

'AG has the options of the drop down list

While Sheet1.Range("AG" + CStr(read1)).Value < Empty
If LCase(Sheet1.Range("AG" + CStr(read1)).Value) = LCase(CStr(Value)
Then
'Putting the value of the relation
'if value in the cell is 1 then corresponding options are true

If Sheet1.Range("AH" + CStr(read1)).Value = 1 Then
ComboBox2.AddItem "less than"
ComboBox3.Style = fmStyleDropDownCombo
End If
If Sheet1.Range("AI" + CStr(read1)).Value = 1 Then
ComboBox2.AddItem "less than or equal to"
End If
If Sheet1.Range("AJ" + CStr(read1)).Value = 1 Then
ComboBox2.AddItem "equal to"
End If
If Sheet1.Range("AK" + CStr(read1)).Value = 1 Then
ComboBox2.AddItem "greater than"
End If
If Sheet1.Range("AL" + CStr(read1)).Value = 1 Then
ComboBox2.AddItem "greater than or equal to"
End If
If Sheet1.Range("AM" + CStr(read1)).Value = 1 Then
ComboBox2.AddItem "not equal to"
End If

End If
read1 = read1 + 1
Wend



read2 = 2
' AC is the Brief description. Maps the list options to the brie
description

While Sheet1.Range("AC" + CStr(read2)).Value < Empty
If LCase(Sheet1.Range("AC" + CStr(read2)).Value) = LCase(CStr(Value)
Then
'AA is the name of the tables
'AB is the name of the fieldname

table_name = Sheet1.Range("AA" + CStr(read2)).Value
field_name = Sheet1.Range("AB" + CStr(read2)).Value
End If
read2 = read2 + 1
Wend
field = CStr(table_name) + "." + CStr(field_name)

With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _
"ODBC;DSN=MS Access Database;DBQ=" + CStr(datab)
";DefaultDir=" + CStr(direc) + ";DriverId=25;" _
), Array("FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"))
Destination:= _
Range("AR1"))

'AR1 is the destination range

.Sql = Array( _
"SELECT DISTINCT " + CStr(field) + "" & Chr(13) & "" & Chr(10
& "FROM `" + CStr(datab) + "`." + CStr(table_name) + " "
CStr(table_name) + "" _
)
FieldNames = True 'Field Names appear as Column headings
.RefreshStyle = xlInsertDeleteCells 'Will delete the previou
entries
.RowNumbers = False 'Will not enter row numbers
.FillAdjacentFormulas = False
.RefreshOnFileOpen = False
.HasAutoFormat = True
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SavePassword = True
.SaveData = True
End With

read3 = 2
While Sheet1.Range("AR" + CStr(read3)).Value < Empty
ComboBox3.AddItem CStr(Sheet1.Range("AR" + CStr(read3)).Value)
read3 = read3 + 1
Wend

'With Worksheets("QUERY_BUILDER")
' ComboBox3.ListFillRange = "AR2:AR"
.Range("AR5000").End(xlUp).Row
'End With

'Dim i As Integer
'r = Range("AR5000").End(xlUp).Row
'For i = 2 To r
'ComboBox3.AddItem CStr(Sheet1.Range("AR" + CStr(i)).Value)
'Next


End Sub


My PROBLEM is that when I select a different option in the first
combobox of subsequent rows( 2 to 5), the data/dropdownlist of third
Combobox changes to the current data in the Column AR. The previous
data is lost.
Why is this happening? As this code is running perfectly in another
file from which I have picked this code.
ALSO, here I had to comment the line 'ComboBox3.Clear while this is
working fine in the other file.
What have I failed to pick? Please help.
TIH
Shilps



--
shilps
------------------------------------------------------------------------
Posted via http://www.mcse.ms
------------------------------------------------------------------------
View this thread: http://www.mcse.ms/message575820.html