Here is what I need from a Home Inspection Database System. As I am just learning access (scripting) I welcome any commends and suggestions from the pros here. I need to track customers, the properties that I inspect, the type of inspection and findings for each property (ie General Home Inspection, Termite Inspection, Water Test, etc).
Datebase that I would use consist of: 1. CustomerContacts 2. TypeofInspection 3. PropertiesInspected 4. InspectionsFindings. Plus a database for canned findings for each item inspection (about 100).
Jargon alert: These are *tables*, not *databases*. A Database in
Access jargon is the .mdb file containing multiple tables, forms,
reports and other objects. You'll need at least these five tables and
probably several more.
When I do an inspection I report on a number of items (ie Roof, Electric, Plumbing, etc) with subcatagories under each of these (ie Roof Section would have - Condtion of Roof Surface, Gutters, Attic, and Flashings). Each of these sub catagories could have multiple comments (ie Nails loose on shingles, shinges are cracked, roof should be replaced soon).
So you'll need a Comments table related one to many to the
InspectionsFindings table. Each comment would go in its own record.
So here is what I have in mind for the reporting system: I set up a combo box that I select the Section that I want to report on (Roof Section etc.). Once I make my selection, another combo box opens with subcatagories for the choosen Section (Roof: Condition of Roof Surface ...). On that selection a multi-select list-box opens and I select the comments that I want (often more than one). At this point I don't know how to store these multi-selected comments. Can I store them in a single record?
Well, you could with a Memo field and some code, but it would be A Bad
Idea. Instead have a related table. Here's some sample VBA code that
you can adapt to move data from a Listbox into a table:
Private Sub cmdProcess_Click()
' Comments : Update the AnimalCondition table based on the
selections in
' the unbound multiselect listbox lstHealthIssues.
' Newly selected rows will be added to the table,
newly cleared
' rows will be deleted.
' Parameters: None
' Modified : 01/29/02 by JWV
'
' --------------------------------------------------
' Populate the AnimalCondition table with the selected issues
On Error GoTo PROC_ERR
Dim iItem As Integer
Dim lngCondition As Long
Dim db As DAO.Database
Dim rs As DAO.Recordset
' save the current record if it's not saved
If Me.Dirty = True Then
Me.Dirty = False
End If
Set db = CurrentDb
' Open a Recordset based on the table
Set rs = db.OpenRecordset("AnimalCondition", dbOpenDynaset)
With Me!lstHealthIssues
' Loop through all rows in the Listbox
For iItem = 0 To .ListCount - 1
lngCondition = .Column(0, iItem)
' Determine whether this AnimalID-HealthID combination is
currently
' in the table
rs.FindFirst "[AnimalID] = " & Me.AnimalID & " AND " _
& "[HealthIssueID] = " & lngCondition
If rs.NoMatch Then ' this item has not been added
If .Selected(iItem) Then
' add it
rs.AddNew
rs!AnimalID = Me.AnimalID
rs!HealthIssueID = lngCondition
rs.Update
End If ' if it wasn't selected, ignore it
Else
If Not .Selected(iItem) Then
' delete this record if it's been deselected
rs.Delete
End If ' if it was selected, leave it alone
End If
Next iItem
End With
rs.Close
Set rs = Nothing
Set db = Nothing
Me.subAnimalCondition.Requery
PROC_EXIT:
Exit Sub
PROC_ERR:
MsgBox "Error " & Err.Number & " in cmdProcess_Click:" _
& vbCrLf & Err.Description
Resume PROC_EXIT
End Sub
