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
