Fresh Ideas Welcome

[doulostheou]

I have dabbled in object oriented programming in the past but not much. My database is getting quite complicated, so I thought I would begin to implement it to keep things from getting too cumbersome when future changes are necessary.

I have created a Vendor object, which when initialized will pull pertinent stats concerning my various vendors. I have added several properties that will return the results of more complex calculations if their values are not already set.

I can now create and destroy a Vendor object, obtaining the pertinent information I need, in about 235 miliseconds.

Sub TestOne()
    Dim Vendor As Vendor
    Dim stpWtch As StopWatch
    
    Set stpWtch = New StopWatch
    Set Vendor = New Vendor
    
    stpWtch.StartTimer
    Vendor.InitializeFirm (2)
    MsgBox "Name: " & Vendor.Name & vbCrLf _
        & "ID: " & Vendor.ID & vbCrLf _
        & "Rating: " & Vendor.CurrentRating & vbCrLf _
        & "Distance: " & Vendor.Distance("63042") & vbCrLf _
        & "Cost: " & Format(Vendor.EstimatedCost("63042"), "currency") & vbCrLf _
        & "Time: " & stpWtch.Formatted
    
    Set Vendor = Nothing
    Set stpWtch = Nothing
End Sub

This is where I get lost using the object oriented approach. I now need to create vendor objects for each vendor in a pre-defined subset, sort the information, and display it back to the user.

First, I am a little shaky on creating and working with a collection of objects, but I think I can work through that.

My real problem is that with the exception of creating a temp table to store the retrieved information (which doesn't seem preferable in a multiuser environment), I cannot think of a good way to display the data back to the user. I basically am looking to populate a listbox with the results and allow the user the option to sort by rating, estimated cost, or distance (obviously changing the sort order of the underlying record source). I cannot set the RowSource propety directly, as the resulting string will often times be longer than what is allowed.

I am tempted to blow up my vendor object, and just create separate functions that I could include in a query, even though it would increase the amount of processing time necessary to pull the information. I would really appreciate the recommendations of anyone who has experience with an object oriented approach.

 

[MarkK]

Cool question, interesting problem.
I would create a Vendors object also, which exposes a collection of Vendors. Give it a Load method into which you can pass parameters that determine which vendors are included and what order you'd like the collection to have.
Here is an example that I've written for a system that handles cabinet parts. This particular example doesn't re-order the list, but that would be easy to do. Parameters here are only used as filters.
There is a cmGibenPart object, not shown here, and this object exposes a collection of those.

Option Compare Database
Option Explicit

Public Items As New VBA.Collection

Public Property Get Item(index) As cmGibenPart
On Error GoTo handler

   Set Item = Items(index)
   Exit Property
   
handler:
   If Err = 5 Then   'invalid procedure call or argument
      Err.Raise 5, "cmGibenParts.Item()", "Item '" & index & "' was not found in the Giben Parts collection "
   ElseIf Err > 0 Then
      'Err.Raise Err, "cmGibenParts.Item()"
   End If

End Property

Public Function Load(Optional partStatus As cmBSPartstatusEnum, Optional IsChecked As Boolean, Optional MaterialID As String, Optional FileID As Long, Optional HasProgram As Boolean) As cmGibenParts
   Dim tmp As cmGibenPart
   Dim rst As DAO.Recordset
   Dim strWhereStatus As String
   Dim strWhereChecked As String
   Dim strWhereMaterial As String
   Dim strWhere3dxFileID As String
   Dim strWhereHasProgram As String
   
   'filters the collection to include only those parts that have the specified status
   If partStatus > cmBSPartstatusNone Then strWhereStatus = "AND Status = " & partStatus & " "
   'filters the collection to include only those parts that have been checked in the PartManager
   If IsChecked Then strWhereChecked = "AND Checked "
   'filters the collection to only include parts of the specified material
   If MaterialID <> "" Then strWhereMaterial = "AND Material = '" & MaterialID & "' "
   'filters the collection to only include parts from the specified 3dxFile
   If FileID > 0 Then strWhere3dxFileID = "AND FileID = " & FileID & " "
   'filters the collection to only include parts that have programs
   If HasProgram Then strWhereHasProgram = "AND Not nz(NCProgName, '') = '' "
   
   Set rst = CurrentDb.OpenRecordset( _
      "SELECT ID " & _
      "FROM cmtGibenPart " & _
      "WHERE True " & _
      strWhereStatus & _
      strWhereChecked & _
      strWhereMaterial & _
      strWhere3dxFileID & _
      strWhereHasProgram & " " & _
      "ORDER BY Name;")
   With rst
      Do While Not .EOF
         Set tmp = New cmGibenPart
         Set tmp = tmp.Load(!ID)
         Items.Add tmp, CStr(!ID)
         .MoveNext
      Loop
      .Close
   End With
   
   Set Load = Me

End Function

Copy this code into a module for easier viewing.
Note that the Item property of this object returns the type of object that the collection contains, so you can use intellisense when referencing its members.
Most data objects I write now have a companion collection object like this, since you'll almost always want to manipulate a group of them.
To populate a combo with the contents of the collection, give the collection object a Public Property Get RowSource() As String, traverse the collection and construct one.
Hope this helps,
Mark

 

[ghudson]

My real problem is that with the exception of creating a temp table to store the retrieved information (which doesn't seem preferable in a multiuser environment), I cannot think of a good way to display the data back to the user.

Just to comment on your quote... You can easily do this if your database is split and each user has a copy of the front end intsalled onto their computers hard drive. A multi-user db should be split.

 

[doulostheou]

I want to thank both of you for your responses.

ghudson, I had tried about a month ago to split this database into a fe/be; but I was met with an significant slowdown in performance and some new errors that I had not dealt with prior to the split. I ended up pulling it back together, which sped up the application and eliminated the errors. I do plan on going back to this issue when I have more time; but I have some other priorities that I have to address functionally at the moment. If I had it to do all over again, I would start out with a fe/be and worked up from there.

lagbolt, the rowsource property sounds like it will be exactly what I was looking for. When you set the rowsource of the combo/list box equal sto the Collections RowSource property, what Row Source Type do you use? I was just now reading the help and noticed for the first time you can set the row source to a user defined function. I have to admit that the help file confused me a little at first glance, but there is example code to review. I will look into this further, but before I spent too much time with it; I wanted to make sure I was not making this more difficult than it needs to be.

 

[Epic]

Regarding "Multiuser" database....

I would create a logon form and append each logon to a table having an autonumber as ID then I would save this value in a global variable. You can then include this number somewhere in your temporary table name and delete the table if is no longer needed.