Module level combobox fill routine

[fllewis]

Hi all. I have a quick question. I think this should be simple, but I can't get it to work. Throughout my app, I have a number of combo boxes that display all of the US states. The states are loaded from a database, and I'm trying to write a module level procedure that I could call to fill the state combo boxes when I need to, but I cant' get it to work. Any suggestions?

Thanks

FLL

[cafeenman]

Public Sub PopulateStates(lst As Control)
Dim i As Integer
Dim RST As Recordset
Dim sSQL As String
 
' declare the lst as a control so you can pass a combo or a list
 
On Error Goto errHandler
 
sSQL = "SELECT State, StateID FROM States ORDER BY State"
Set RST = db.OpenRecordset(sSQL, dbOpenSnapshot)
 
lst.Clear
 
With RST
    Do Until .EOF
        lst.AddItem .Fields("State")
        lst.ItemDate(lst.NewIndex) = .Fields("StateID")
        .MoveNext
     Loop
End With
 
RST.Close
Set RST = Nothing
 
Exit Sub
 
errHandler:
msgbox Error
 
End Sub

[cafeenman]

I don't get why the forum is smashing all my code together, but here's a more generic version that I like to use:

Public Sub LoadTableIntoList(lst As Control, sTable As String, sField As String, sID As String)
Dim i As Long
Dim RST As Recordset
Dim sSQL As String
 
 
On Error GoTo errHandler
 
 
' Refreshes list only if changes are found
 
 
sSQL = "SELECT " & sField & "," & sID & " FROM " & sTable & " ORDER BY " & sField & " ASC"
Set RST = DB.OpenRecordset(sSQL, dbOpenSnapshot)
 
 
If RecordsetEmpty(RST) Then
  lst.Clear
  GoTo CloseRST
End If
 
 
With RST
  If .Recordcount <> lst.ListCount Then GoTo RefreshList
  .MoveFirst
  For i = 0 To .Recordcount - 1
    If lst.List(i) <> .Fields(sField) Then GoTo RefreshList
    .MoveNext
  Next i
  ' No changes were found so leave list as it is
  GoTo CloseRST
End With
 
 
RefreshList:
 
 
lst.Clear
 
 
With RST
  .MoveFirst
  Do Until .EOF
    lst.AddItem .Fields(sField)
    lst.ItemData(lst.NewIndex) = .Fields(sID)
    .MoveNext
  Loop
End With
 
 
CloseRST:
RecordsetClose RST
 
 
Exit Sub
 
 
errHandler:
LogError Error, Err, vbNullString, "bDatabase.LoadTableIntoList"
Resume CloseRST
 
 
End Sub
 
 
Public Function RecordsetEmpty(ByRef RST As Recordset) As Boolean
 
 
' Checks to see if a recordset is populated
 
 
On Error GoTo errHandler
 
 
' Assume the recordset is nothing or unpopulated
RecordsetEmpty = True
 
 
If RST Is Nothing Then Exit Function
If RST.BOF And RST.EOF Then Exit Function
 
 
' Recordset exists and is populated
RecordsetEmpty = False
 
 
Exit Function
 
 
errHandler:
LogError Error, Err, vbNullString, "bDbUtilities.RecordsetEmpty"
 
 
End Function

[fllewis]

Thanks Cafeenman. I've been trying to get that to work for a while. Another question that you guys may know.

I'm in the process of trying to optimize this app before it goes out. Quicker to load the data into a module level array and eat the memory for the entire time the app is running or quicker to hit the db each time I need to fill the combo boxes?

Thoughts appreciated. Again, thanks for the code.

Flewis

[cafeenman]

Originally posted by fllewis
Thanks Cafeenman. I've been trying to get that to work for a while. Another question that you guys may know.

I'm in the process of trying to optimize this app before it goes out. Quicker to load the data into a module level array and eat the memory for the entire time the app is running or quicker to hit the db each time I need to fill the combo boxes?

Thoughts appreciated. Again, thanks for the code.

Flewis

Well, I actually do load the stuff into an array for the static tables (states, etc). But for the more dynamic tables, I still have to check the database for updates in a multi-user environment, so loading arrays and then loading combos just adds a step. So I optimized based on what table goes into what combo.

[fllewis]

In this case, it's always going to be the fifty states in alphabetical order. What do you think would optimize speed in this case? The static array? Or multiple trips to the db?

[cafeenman]

Just fill it once from the db and leave it. You don't have to worry about the contents changing any time soon.

[fllewis]

But I will be filling several combos with the same data through the instance of the app...not all at the same time, either. The combos reside on different forms, and depending on when the users accesses the forms will depend on when the combos are filled.

So would it be better to fill each different combo when the forms are loaded, or store the data in an array and use the array data to fill the combos.

Array memory vs. trip to the db each time a different States combo must be filled?

[cafeenman]

You're right. When I posted my last response I forgot that you are filling combos on several forms. Just create two arrays (if you need to track the ID's as well). One for the state and one for the id. Then use that to load the combos. That will be much faster than making trips to the server to retrieve the data.

' Don't dimension the array if you don't know how many there are
' for example, are you loading territories as well?
 
Public State() As String
Public StateID() As Long
 
Sub GetStates
Dim RST As Recordset
Dim sSQL As String
Dim iPos as Long
 
sSQL = "SELECT State, StateID FROM States ORDER BY State"
Set RST = db.OpenRecordSet(sSQL, dbOpenDynaset)
 
With RST
    .MoveLast ' Needed to get recordcount
    .MoveFirst
    Redim State(RecordCount - 1)
    Redim StateID(.RecordCount - 1)
    Do Until .EOF
        State(iPos) = .Fields("State")
        StateID(iPos) = .Field("StateID")
        iPos = iPos + 1
        .MoveNext
     Loop
End With
 
RST.Close
Set RST = Nothing
 
End Sub       
 
Sub PopulateStateList(lst As Control)
Dim i As Long
 
lst.Clear
 
For i = LBound(State) To UBound(State)
    lst.AddItem State(i)
    lst.ItemData(lst.NewIndex) = StateID(i)
Next I
 
End Sub

[fllewis]

Thanks for the input. I will go with the array here. Advice appreciated.

FLL