Hi all, first post go easy 
I'm pretty good with forms in Excel but struggling a little with Access. I've been asked to setup a db which 1500 users will occasionally interact with. Probably about 150 max on at any one time. I'm thinking the thing will creak quite a bit with that many users in one file so I had a cunning plan. I'll create multiple db files which link via unbound forms to other dbs which contain actual data. So in theory each area of business only opens their own EUL form, but all access a broken up variety of tables through DAO connections.
Onto business.... I have a listbox, which I can populate just fine with my funky db setup using additem, but this crashes and burns when I make it 25+ columns wide and it goes down 200 odd rows. Just gets super laggy. I'm trying to see if I can get another more direct method working.
I tried the below which ran through and collects the data in the recordset but then mismatches on the rowsource bit. There's something I'm just not quite putting together about how to put an externally referencing SQL statement into the listbox directly -- will this even be quicker??
Old working code below, but too slow.

I'm pretty good with forms in Excel but struggling a little with Access. I've been asked to setup a db which 1500 users will occasionally interact with. Probably about 150 max on at any one time. I'm thinking the thing will creak quite a bit with that many users in one file so I had a cunning plan. I'll create multiple db files which link via unbound forms to other dbs which contain actual data. So in theory each area of business only opens their own EUL form, but all access a broken up variety of tables through DAO connections.
Onto business.... I have a listbox, which I can populate just fine with my funky db setup using additem, but this crashes and burns when I make it 25+ columns wide and it goes down 200 odd rows. Just gets super laggy. I'm trying to see if I can get another more direct method working.
I tried the below which ran through and collects the data in the recordset but then mismatches on the rowsource bit. There's something I'm just not quite putting together about how to put an externally referencing SQL statement into the listbox directly -- will this even be quicker??

Code:
Sub ListEmployeeDetailsByCCTEST()
OpenEmployeeDb
'##### SQL QUERY #####
strCCName = Form_fmMainForm.cmbCCList.Value
Set rs = Db.OpenRecordset _
("SELECT TMDEmployeeDetails.EmpName,StaffNumber,Grade from TMDEmployeeDetails WHERE CostCentreDesc = '" & strCCName & "'")
Form_fmMainForm.EmployeeDetails.RowSource = Db.OpenRecordset("SELECT TMDEmployeeDetails.EmpName,StaffNumber,Grade from TMDEmployeeDetails " & _
"WHERE CostCentreDesc = '" & strCCName & "'")
CloseDb
End Sub
Old working code below, but too slow.
Code:
Sub ListEmployeeDetailsByCC()
OpenEmployeeDb
'##### SQL QUERY #####
strCCName = Form_fmMainForm.cmbCCList.Value
Set rs = Db.OpenRecordset _
("SELECT TMDEmployeeDetails.EmpName,StaffNumber,Grade from TMDEmployeeDetails WHERE CostCentreDesc = '" & strCCName & "'")
'Goto start of list
rs.MoveFirst
'rs.MoveNext
i = 0
With Form_fmMainForm.EmployeeDetails
.RowSource = ""
Do Until i = rs.RecordCount
'.AddItem rs(0)
.AddItem (rs.Fields("EmpName") & ";" & rs.Fields("StaffNumber") & ";" & rs.Fields("Grade"))
i = i + 1
rs.MoveNext
Loop
End With
CloseDb
End Sub