Listbox performance issue with value list - Access - VBA
Posted on 2014-03-25
Hi experts, I have an issue with a list box performance when loading the data, It is loading fast when the form open first time based on a combo box value, But after some form operations (successful insert/update data to table using the form) - this list box taking too much time to load (more than 3 minutes). When i checked the code with break point - seems like it is hanging on the while loop to add stritem to the list box.
Can somebody please help me.
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL1 As String
Dim strItem As String
Set db = CurrentDb
strSQL1 = "SELECT * FROM Employee WHERE ((Person_Number) not in (select person_number from Employee_Room_Status WHERE Employee_Room_Status.Employee_Active = true and Employee_Room_Status.Room_ID =" & Me.Combo_Room.Value & "))"
Set rs = db.OpenRecordset(strSQL1)
Do Until rs.EOF
strItem = rs.Fields("Last_Name").Value & ";" _
& rs.Fields("First_Name").Value & ";" _
& rs.Fields("Middle_Initial").Value & ";" _
Me.List1Edit.AddItem strItem ' Row Source Type must be Value List
Set rs = Nothing
Set db = Nothing