Access 2007 Using DAO to Class to db Tables

[NaughtyZute]

I've avoided using classes in Access for years, but now I have a need to create a class. The application I'm writing is to track sheriff dept fleet (automobiles). I've created a class for Equipment (radar, radio, gun racks, etc.) with the properties for vehicle id, equipment id (identifies type of equipment), model, serial number and notes. Then I think I need a collection to hold all the equipment that could be on one sheriff car. I'm working on the add new vehicle piece. So once the user has selected (equipment type) from a dropdown and entered model, sn, etc. Click "Add" it goes in the collection. Do I write the DAO in the class itself to update the VehicleEquipment table? Sorry for the lengthiness of this post. I'll post the class I've created so far... Thanks in advance for your help.

[TheSmileyCoder]

I dont understand why you need to create a class for this? Why not simply store it in a table and relate it to your main table?

[NaughtyZute]

Because I want to allow the user to add several pieces of equipment to a vehicle, but not commit the data to a table until the entire vehicle is saved. If I save to a table while they're adding equipment to a new vehicle, they may cancel the vehicle creation. I am very open to ideas.
My current concept of the events and system responses: 1) User enters vehicle data (VIN, Yr, Make, Model, etc.) 2) Adds equipment using a listbox to choose type of equipment and textboxes for entering identifying information about the equipment (model, serial number... if any) 3) User clicks Add Equipment; equipment class object is created and added to a collection (which I'm also having trouble getting to work) 4) Iterate through collection and display the equipment added so far on the form (using subform or textboxes?) 5) User clicks Save Vehicle and iterate through the collection saving the equipment to database.
I guess my question is just: Do I write the DAO in the class itself to save the collection or is that written behind the form?

[TheSmileyCoder]

I honestly have no experience in the method your propossing. I can't say whether it is feasable or not. I think however if it was me, I would prefer using temporary tables, to store the information, before commiting it. I think thats the easier approach (that said, the easiest approach is not always the right approach, thats up to you).

[NeoPa]

It appears that you have come from a programming background and have little experience with database concepts. How can I say that without sounding dismissive? I expect I should just add that my intention is to help here, and I need to understand where you're coming from in order to do that effectively. Apologies for any disrespect inferred. None was implied. I would add that your proposed solution, while not very practical in a database environment, is actually quite a clever one, given the paradigm I assume you are working within.

In a database situation, a collection would be held in a table. Linking the data in the table to wherever it is relevant in your structure is done with Primary & Foreign Indexes. In a situation like this I would expect there to be a table that contained details of all the cars; another table to contain the possible types of equipment available to the cars; another table, with Foreign Indexes to both of the two prior tables, that contains the individual pieces of equipment, such as a short-wave radio in sherrif Bob's new car.

I appreciate that this is a bit of a paradigm-shift for you, but does that sound like it makes sense?

In case it helps, you may want to check out Database Normalisation and Table structures. This gives a bunch of tips on how tables should best be designed to handle the requirements you have of them.

[patjones]

Hi,

It's completely possible to implement classes in VBA, but I have personally found that approach to be more laborious than other standard methods available in Access, as Smiley alludes to. Also bear in mind that one of the motivations for object oriented programming is portability of the classes. If this is a very specialized application then your classes may not have much relevance in other projects.

As NeoPa suggests, the way we usually like to go is build a normalized table structure (relational database). For the user interface, there are various things you can do, but it seems like an unbound form will suit your needs well. This is where you enter all the information for a new record and then complete an action to commit all the information to the tables, such as hitting a command button that you put on the form. The nice thing about this approach is that the user can go back and change information prior to committing it. You can even put another button on the form that allows them to clear it altogether and start over.

Let us know if you might be interested in going this way, and I'm sure we can give you further guidance.

Pat

[NaughtyZute]

Thank you for your response. I've never used classes in Access before and was unsure how to save the data. I must be very confused because the responses here seem to indicate that I'm way off base in my concept of class usage. I could have used a temporary table to save the vehicle equipment selections, but I thought a class and collection would have a lower overhead. I believe I'm quite competent with database design and normalization and the database table that the vehicle equipment is being saved into relates back to the vehicle. I've decided to just iterate through the listbox on the form and save the equipment that has been added by the user in the process of creation the vehicle. Not sure what I've done or said wrong in this post, or maybe I'm just too sensitive, but I definitely feel that the responses have been quite condescending and critical. Sorry to have troubled you all. I'll close this post now.

[patjones]

Hi,

I can understand where we may come across that way, but - and I can only speak for myself - no such condescending attitude is intended.

In your situation, what I like to do (and I'm just laying out a general map) is set a recordset up, attach it to my tables, assign the control values to the recordset, and commit the data. There are numerous other ways.

Dim rs As DAO.Recordset
 
Set rs = CurrentDb.OpenRecorset("tblName", dbOpenDynaset)
 
rs.AddNew
 
rs.Field1 = textbox_value1
rs.Field2 = textbox_value2
rs.Field3 = combobox_value1
 
...
 
rs.Update
 
Set rs = Nothing

Here, "tblName" is the name of the table you're putting the data into. If there are multiple tables, you can either go through this process with multiple recordsets, or stay with the one recordset and specify a query that encompasses all the joined tables.

rs.AddNew tells Access we're going to add a new record, while rs.Update actually commits the data. Even though the user put values in the various controls, nothing is stored in the database until rs.Update happens.

Again, there are other possibilities.

Pat

[NeoPa]

I apologise if anything I posted upset you. I fully accept that my perspective is largely guesswork on my part, built simply from your posts. I'm happy to accept I may be way off base here, but that's how it appeared to me based on admittedly limited information. My intention was never to insult you. I had no idea that you considered yourself more experienced than I assumed (That's not supposed to sound condescending either - It's merely an illustration of the difficulties we face when dealing with people we don't know - a necessary part of contributing on a site like this.)

Personally, I feel that using session-based data (as is typically, though not necessarily, used in a class type structure) would not be appropriate for what I understand you want - hence I suggested an alternative approach. If any of my assumptions are wrong then I'm happy that the choice of what to do is still firmly in your hands. Even if all my assumptions are absolutely correct, it is still you that ends up making the choices (which is absolutely as it should be).

At the end of the day, I'm sure none of us (I've been rubbing shoulders with the others for some years now) had any intention of insulting you, or showing any lack of respect for your skills. If you feel though, that what I contributed was not what you want to hear, I'm happy to leave this thread in other very capable hands.

Good luck with your project.

[NaughtyZute]

* * * * * * * * * *
 
Dim newEq As CEquipment
Dim iListEq As Integer
Dim strListBox as String
 
If Me.lstEquip.Value = "" Then
    iListEq = 0
Else
    iListEq = Nz(Me.lstEquip.Value, 0)
End If
 
Set newEq = New CEquipment
 
If iListEq = 0 Then
    MsgBox "Nothing to add.  Please select Equipment from List."
Else
    With newEq
        .EQID = Me.lstEquip.Value
        .Name = Me.lstEquip.Column(1)
        .Model = Nz(Me.txtModel, "")
        .Serial = Nz(Me.txtSN, "")
        .Note = Nz(Me.txtNote, "")
    End With
 
    strListBox = newEq.EQID & g_SEMI & newEq.Name & g_SEMI & newEq.Model & g_SEMI & newEq.Serial & g_SEMI & newEq.Note
 
    Me.lstMyVehEqp.AddItem strListBox
 
    'CLEAR SOURCE LISTBOX AND TEXTBOXES
    clearEquipBoxes
 
End If
 
End Sub
 
* * * * * * * * * * *
 
'AND THE PERTINENT PART OF THE VEHICLE_SAVE EVENT
 
   'STARTS AT 1 BECAUSE FIRST ROW IS HEADING
    Select Case Me.lstMyVehEqp.ListCount
        Case 1
            bHasEquip = False
        Case Is > 1
            bHasEquip = True
    End Select
 
    If bHasEquip Then
 
        Set rstEquip = dbsFleet.OpenRecordset("VehicleEquip_test", dbOpenDynaset)
 
        With rstEquip
 
            For i = 1 To Me.lstMyVehEqp.ListCount - 1
                .AddNew
                .Fields("V_ID") = Me.V_ID
                .Fields("EQ_ID") = Me.lstMyVehEqp.Column(0, i)
                .Fields("E_Model") = Me.lstMyVehEqp.Column(2, i)
                .Fields("E_SN") = Me.lstMyVehEqp.Column(3, i)
                .Fields("E_Notes") = Me.lstMyVehEqp.Column(4, i)
                .Update
            Next i
 
        End With
            rstEquip.Close
            Set rstEquip = Nothing
    End If
 
* * * * * * * * * * * * *
 
'THE CLASS MODULE....
Option Compare Database
'WHEN ADDING A NEW VEHICLE THERE WILL NOT BE A V_ID UNTIL THE VEHICLE IS SAVED FOR THE FIRST TIME
'BUT I'LL NEED THE VID WHEN IT COMES TO EDITING
Private pVID As Double
Private pEQID As Integer
Private pEQname As String
Private pEQnote As String
Private pEQmodel As String
Private pEQserial As String
Public Property Get VID() As Double
    VID = pVID
End Property
Public Property Let VID(Value As Double)
    pVID = Value
End Property
Public Property Get EQID() As Integer
    EQID = pEQID
End Property
Public Property Let EQID(Value As Integer)
    pEQID = Value
End Property
Public Property Get Name() As String
    Name = pEQname
End Property
Public Property Let Name(Value As String)
    pEQname = Value
End Property
Public Property Get Model() As String
    Model = pEQmodel
End Property
Public Property Let Model(Value As String)
    pEQmodel = Value
End Property
Public Property Get Serial() As String
    Serial = pEQserial
End Property
Public Property Let Serial(Value As String)
    pEQserial = Value
End Property
Public Property Get Note() As String
    Note = pEQnote
End Property
Public Property Let Note(Value As String)
    pEQnote = Value
End Property

[NaughtyZute]

Thank you all for being a sounding board. I apologize if my frustration caused me to lash out, but I do think we all need to be careful making assumptions... and perhaps focusing on the question being asked rather than re-writing someone else's solution. I may not write code in the best and most elegant fashion, but I get the job done. Peace out.

[NeoPa]

NaughtyZute:
Peace out.

That means "I've had my say but the subject's closed now." doesn't it :-D I'm just messing with you of course. I don't believe anyone was too upset by your earlier comments. I'm normally quite strict with members that get too shirty on here, and I didn't feel you'd crossed any line.

I'm afraid that expecting people to help you without making any assumptions though, is just not going to happen except for the very simplest of questions. In a perfect world where all required information is passed in the original post, this may be possible. Otherwise we often have little but assumptions to go on. None of us could have been much help here without making assumptions. Many of which were not even discussed or noticed. It's the nature of the beast. You should appreciate that answering technical questions about a project that we don't even have in front of us is not the easiest thing to manage. We have to fill in the gaps by using our experience. There is no other practicable way to work in the circumstances.

[NaughtyZute]

I didn't mean it that way. My only intention was to show what I ended up doing in case it will help someone else. I actually feel like crying right now because I feel so misunderstood. This is why I normally just google the thing until I find someone else's question answered. I'll make sure to do that in the future. My big feet just seem to step all over everything. Now I am crying so... I'm definitely not suited to this kind of back and forth. Sorry again that I offended anyone and I'm sorry I ever asked a question. Maybe it's not too late to become a line cook at McDonald's.

[NeoPa]

I understand you. At the risk of making myself further misunderstood, I really wasn't trying to have a go at you.

I understood that your posting your code was just showing the solution you came up with that you were happiest with. It wouldn't be the approach I would take, but that's really very unimportant. What's important is that it suits you. We, as a site, and I am in a position to speak for the site, are happiest when members do feed back their solutions, especially in those cases where they don't feel what was offered was quite what they were after. No criticism is inferred in cases such as these. We understand that the internet is a very big place and there will always be people who see things differently. You weren't rude or impolite in expressing your views. Of course there's room here for that.

I hope you don't go away feeling sorry you asked the question. I know many people who don't feel comfortable with too much back and forth, but that isn't a sign they need to consider cooking for McDonalds. I have to say, even if I didn't agree with your approach, I always felt you were reasonably clever to handle it in the first place. Not everyone, even in the software industry, is even comfortable working with classes at all.

[NaughtyZute]

Thank you. That means a lot to me. In retrospect, I think it was this line tht got us off on the wrong foot and painted my attitude for any future postings: It appears that you have come from a programming background and have little experience with database concepts. I appreciate your help. I am always striving to be a better programmer, as I'm sure we all are. It is such a subjective activity -- but that's what makes it fun. Thanks again.