Listing all related information

  • Thread starter Thread starter Robert Neville
  • Start date Start date
R

Robert Neville

My database has complex relationships between companies, contacts,
projects, and addresses. I have been exploring solutions that would
allow me to pick a record and cut & paste all data from the record;
its labels; and all related records. The idea stems from filtering a
report with many sub-reports and exporting it rtf format. I would
prefer to achieve this while in my database where I cut & paste data
for usage in an email or letter. Presently, my database has several
driving forms which have sub-forms that relate the data to driving
tables.

For example, the Company form has a address sub-form; contact sub-form
on one tab; project sub-form on one tab; task sub-form on one tab.
The forms perform as expected, yet it takes a long time to compile the
information and copy it to an email. I would like to create a memo
field on another tab that would consolidate all the information in
text format after pressing a command button. My ideas relate to
building an adhoc sql statement in code; based on the index field like
CompID; loop through the fields; use the print method to output data
to the memo field; then move to the next SQL relating the records to
other tables; and repeat the process until all data is output to the
memo field. I am still learning a lot and could use examples and
suggestions about a possible solution. Let me know if anyone could
lead me in the right direction.
 
I am still developing an approach to output one record and all its
related records to text. The code below is my rough draft. The
function requires a driving table, a primary index, and the record ID
number; then it creates a SQL and loops through the fields printing
labels & values to the form's memo field; then the code loops through
the tabledef objects matching the primary index name with all tables
in the tabledef collection; when a table contains the same primary
index then it loops through the fields printing labels & values to the
memo field. The code has one major shortcoming AS IS. The tabledef
approach only goes one level deep; thus does not obtain values outside
the relationship table.

For example, the driving table is tblProj. The code should relate
tblProj -> trelProjAddr -> tblAddr; tblProj -> trelCompProj ->
tblComp; tblComp -> trelCompAddr -> tblAddr; tblProj -> trelContProj
-> tblCont; and tblTask. Plus, the code should account for lookup
tables. Presently, it relates tblProj -> trelCompProj; tblProj ->
trelContProj; and tblTask. These tables have the primary index,
ProjID.

I have some ideas for dealing with this scenario; like building case
statements with an Array containing several pre-made queries; or
building the logic to determine related tables and driving tables. But
I am stuck at an impasse right now. Let me know if anyone has any
suggestions or examples for my objective. Your assistance could lead
me in the right direction.

********************************Code Begin**************************
Private Function ListALL(varTable As String, strWhere As String) As
String
' ListAll(tblProj, "ProjID=" & [Proj]")
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim strSQL As String
Dim strArray() As String

Const cstrProc As String = "ListAll"

On Error GoTo ListAll_Err

strSQL = "Select * FROM " & varTable & " "
strSQL = strSQL & "WHERE " & strWhere & "; "

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)

With rst
For x = 0 To rst.Fields.Count - 1
Do While Not rst.EOF
Me!txtListAll.Print .Fields(x).Name & ":» " &
..Fields(x).Value
Loop
Next
End With
rst.Close
Set rst = Nothing

For Each tdf In dbs.TableDefs
If tdf.Name <> varTable Then
For Each fld In tdf.Fields
If fld.Name = strID Then
strRelatedTable = tdf.Name

strSQL = "Select * FROM " & strRelatedTable &
" "
strSQL = strSQL & "WHERE " & strID & "=" &
strWhere & "; "
Set rst = dbs.OpenRecordset(strSQL)

With rst
For x = 0 To rst.Fields.Count - 1
Do While Not rst.EOF
Me!txtListAll.Print
..Fields(x).Name & ":» " & .Fields(x).Value
Loop
Next
End With
End If
Next fld
End If
Next tdf

ListAll_Exit:
Set fld = Nothing
Set tdf = Nothing
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
Exit Function

ListAll_Err:
Call ErrMsgStd(Me.Name & "." & cstrProc, Err.Number,
Err.Description, True)
Resume ListAll_Exit
End Function
********************************Code End**************************
 
Back
Top