Filling unbound Listbox from external data source

dweben

New member
Local time
Today, 16:22
Joined
Feb 15, 2013
Messages
2
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?? :confused:

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
 
Welcome.

Please define interact in your situation. Is this a read only set up?
As you are probably finding, Access is not Excel. They are different animals.
If your intention was to use a listbox and make it look like a spreadsheet, I think you will have many issues.

I think readers will need to know more of your set up, requirements and data before more focused responses can be made.

Are you aware of database design principles? and Normalization?
http://forums.aspfree.com/attachment.php?attachmentid=4712&d=1201055452

Good luck.
 
A RowSource SQL is limited to 32K characters. I expect this would also apply to an ItemList string. However I have no doubt it would be struggling long before it got there.

I think you need to go back and start again with your plan. Look at using a database server such as MS SQL Server or MySQL as the back end.
 
Hi guys,

Thanks for the quick replies. I've worked as a super user on the functional side of Oracle Dbs for years so I'm all good with Db setup, using lookup sets, IDs not names, keeping data stored as basic as possible, no duplication, etc.

The project is creating a Roster in Access. I have it running in Excel with an Access backend for logging and lookup sets, record locks, etc, but the shared workbook side is proving to be too slow to work with as the file size grows.

With the Listbox I was trying to create a list of Employees (+ couple columns of org data) and column heads of 4 weeks of dates. Then in the data the SQL query would populate shift times, leave, etc. I'd have a couple forward/backward buttons to scroll along weeks which would re-query data.

I thought about running the one large query but this as above proving cumbersome. I also thought about breaking either the Listbox up into smaller ones, or maybe running the query once for each day or employee multiple times until the Listbox is full of Stuff. Max number of rows of data would be say (being generous) be 2000 employees if some lunatic selected everyone, but I'd force a parameter selection which would in reality restrict to maybe 300 max. I'm 100% sure I can get this working as required, just pondering the most efficient solution speedwise for the user experience.

I also don't have access to anything other than Excel or Access as the funding for IT developments was frozen, so this is all in house with whatever kit we have stuck on the PCs. Interaction wise in Excel I have three (mainly two) levels of users. One restricted to viewing and requesting leave, and others who are a more central controlling team much more writable access to the roster and table data, then admin with everything access.

While my Excel/Access solution does do the job with its 7000 odd lines of code it's not a long term solution and have its own issues/compromises which are troublesome!
 
Unless you know 100% what you are doing in Access, then attempting to refer to form objects in this manner:

Code:
Form_fmMainForm.cmbCCList.Value
will cause you many problems. Closed forms will have intstances opened invisibly and linger in the background, doing debugging can again open instances of forms etc etc.

The safer way is to follow conventions:

Code:
Forms!fmMainForm!cmbCCList.Value
 

Users who are viewing this thread

Back
Top Bottom