Hallo!

Ich programmiere gerade unter Excel 2003 VBA an einer Datenauswahl über 4 ComboBoxen in einer UserForm. Die Comboboxen sind hierarisch jeweils der vorigen untergeordnet und werden je nach Inhalt des Vorgängers über das Enter-Ereignis mit entsprechenden Daten gefüllt. Da der User die Auswahl in der ersten Box beginnen soll, habe ich die weiteren Boxen deaktiviert und aktiviere diese erst nach und nach mit dem Klick-Ereignis in der Vorgängerbox. Wenn ich in allen Boxen eine Auswahl getroffen habe und dann wieder in die 3te Box klicke, passiert etwas merkwürdiges. Auf einmal wird das Enter-Ereignis der ersten Box ausgelöst.

Kann mir das jemand erklären?

Schönen Gruß aus Erlangen,

Kolja

Mit Quelltext bestimmt

Gut, hier der Quelltext:

Private Function Db_Query(Sqlreq As String) As Recordset

Dim wsp As Workspace

Dim db As Database

Set wsp = DBEngine.Workspaces(0)

Set db = wsp.OpenDatabase("D:\Test\db.mdb")

Set Db_Query = db.OpenRecordset(Sqlreq)

End Function

Private Sub ComboBox_RSInsert(ComboBox As MSForms.ComboBox, rec As Recordset, RowName As String)

ComboBox.Clear

If rec.RecordCount > 0 Then

For X = 0 To rec.RecordCount - 1

ComboBox.AddItem rec(0).Value, X

rec.MoveNext

Next X

ComboBox.ListIndex = -1

End If

End Sub

Private Sub ComboBox1_Click()

If Not UserForm1.ComboBox1.ListIndex = -1 Then

UserForm1.ComboBox2.Enabled = True

End If

Debug.Print "ComboBox1_Click"

End Sub

Private Sub ComboBox1_Enter()

UserForm1.ComboBox2.Clear

UserForm1.ComboBox3.Clear

UserForm1.ComboBox4.Clear

UserForm1.ComboBox2.Enabled = False

UserForm1.ComboBox3.Enabled = False

UserForm1.ComboBox4.Enabled = False

UserForm1.CommandButton1.Enabled = False

Dim rec As Recordset

Set rec = Db_Query("SELECT DISTINCT Type FROM ASSET WHERE Tracking_Number > '1000' ORDER BY Type ASC")

Call ComboBox_RSInsert(UserForm1.ComboBox1, rec, "Type")

Debug.Print "ComboBox1_Enter"

End Sub

Private Sub ComboBox2_Click()

If Not UserForm1.ComboBox2.ListIndex = -1 Then

UserForm1.ComboBox3.Enabled = True

End If

Debug.Print "ComboBox2_Click"

End Sub

Private Sub ComboBox2_Enter()

UserForm1.ComboBox3.Clear

UserForm1.ComboBox4.Clear

UserForm1.ComboBox3.Enabled = False

UserForm1.ComboBox4.Enabled = False

UserForm1.CommandButton1.Enabled = False

If Not UserForm1.ComboBox1.Text = "" Then

Dim rec As Recordset

Set rec = Db_Query("SELECT DISTINCT Serial_Number FROM ASSET WHERE Type = '" + UserForm1.ComboBox1.Text + "' AND Tracking_Number > '1000' ORDER BY Serial_Number ASC")

Call ComboBox_RSInsert(UserForm1.ComboBox2, rec, "Serial_Number")

End If

Debug.Print "ComboBox2_Enter"

End Sub

Private Sub ComboBox3_Click()

If Not UserForm1.ComboBox3.ListIndex = -1 Then

UserForm1.ComboBox4.Enabled = True

End If

Debug.Print "ComboBox3_Click"

End Sub

Private Sub ComboBox3_Enter()

UserForm1.ComboBox4.Clear

UserForm1.ComboBox4.Enabled = False

UserForm1.CommandButton1.Enabled = False

If Not UserForm1.ComboBox2.Text = "" Then

Dim rec As Recordset

Set rec = Db_Query("SELECT DISTINCT Tracking_Number FROM ASSET INNER JOIN CALIBRATION_DATA ON ASSET.SEARCH_VALUE = CALIBRATION_DATA.SEARCH_VALUE WHERE ASSET.Serial_Number = '" + UserForm1.ComboBox2.Text + "' AND ASSET.Tracking_Number > '1000' ORDER BY Tracking_Number ASC")

Call ComboBox_RSInsert(UserForm1.ComboBox3, rec, "Tracking_Number")

End If

Debug.Print "ComboBox3_Enter"

End Sub

Private Sub ComboBox4_Click()

If Not UserForm1.ComboBox4.ListIndex = -1 Then

UserForm1.CommandButton1.Enabled = True

End If

Debug.Print "ComboBox4_Click"

End Sub

Private Sub ComboBox4_Enter()

UserForm1.CommandButton1.Enabled = False

If Not UserForm1.ComboBox3.Text = "" Then

Dim rec As Recordset

Set rec = Db_Query("SELECT DISTINCT Tracking_Number FROM ASSET INNER JOIN CALIBRATION_DATA ON ASSET.SEARCH_VALUE = CALIBRATION_DATA.SEARCH_VALUE WHERE ASSET.Serial_Number = '" + UserForm1.ComboBox2.Text + "' AND ASSET.Tracking_Number >= '" + UserForm1.ComboBox3.Text + "' ORDER BY Tracking_Number ASC")

Call ComboBox_RSInsert(UserForm1.ComboBox4, rec, "Tracking_Number")

End If

Debug.Print "ComboBox4_Enter"

End Sub

'Initialize Userform

Private Sub Userform_Initialize()

Dim rec As Recordset

Set rec = Db_Query("SELECT DISTINCT Type FROM ASSET WHERE Tracking_Number > '1000' ORDER BY Type ASC")

Call ComboBox_RSInsert(UserForm1.ComboBox1, rec, "Type")

UserForm1.ComboBox2.Enabled = False

UserForm1.ComboBox3.Enabled = False

UserForm1.ComboBox4.Enabled = False

UserForm1.CommandButton1.Enabled = False

Debug.Print "Userform_Initialize"

End Sub