Prev: If .....then ....
Next: Ms Access JDBC Driver?
From: SubyRuby on 3 Jul 2008 15:28
I have a table that contains contact information for people we want to
send something to.
I want to group my mailing labels by company so that if there are
several people from the same company, the main label will looks like
this:
CompanyName
Address1 - Address2
City, State, Zip

Then I would like to have sub-labels for each person that I can
enclose within the main package.

If there is only one person listed for that company, then I want the
label to show the contact name as well.
ContactName
CompanyName
Address1 - Address2
City, State, Zip

So, essentially I want to count the number of related records and if
it's more than 1, remove the contact field and create a new label for
each contact separately.

Is this possible? Either in a query or the report design?

Thanks in advance for any help.
From: strive4peace on 3 Jul 2008 22:25
Hi Suby,

yes, this is possible, but it takes some coding and the data structure
must be known -- to help you with code, we need to know the structure of
your database.

Here is something you can do to document that for us:

create a new general module

paste in this code:

'~~~~~~~~~~~~~~~~~~
'NEEDS REFERENCE to Microsoft DAO library
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'TO DOCUMENT -->
'--- click your mouse into the appropriate Sub below
'--- press F5 to run

Sub RunShowFieldsForTable()
'edit this line for the tablename you wish to document
ShowFields "Your tablename"
End Sub

Sub RunShowFieldsForAllTables()
'click HERE
'press F5
Dim i As Integer _
, mTablename As String
For i = 0 To CurrentDb.TableDefs.Count - 1
mTablename = CurrentDb.TableDefs(i).Name
If Left(mTablename, 4) <> "Msys" Then
Debug.Print 'blank line
ShowFields mTablename
End If
Next i
End Sub

'~~~~~~~~~~~~~~~~~~
Sub ShowFields(pstrTable As String)
'by DuaneHookom
'modified by Crystal

Dim fld As DAO.Field
Dim tbl As DAO.TableDef
Dim db As DAO.Database

Set db = CurrentDb
Set tbl = db.TableDefs(pstrTable)

Debug.Print tbl.Name
Debug.Print "=========================="

For Each fld In tbl.Fields
'modified by Crystal
Debug.Print fld.OrdinalPosition & " " & fld.Name _
& ", " & fld.Type & " (" & GetDataType(fld.Type) & ")" _
& ", " & fld.Size
Next

'release object variables
set fld = nothing
set tbl = nothing
set db = nothing

End Sub

'~~~~~~~~~~~~~~~~~~
Function GetDataType(pDatType) As String
'by Crystal
Select Case pDatType
Case 1: GetDataType = "Boolean"
Case 2: GetDataType = "Byte"
Case 3: GetDataType = "Integer"
Case 4: GetDataType = "Long"
Case 5: GetDataType = "Currency"
Case 6: GetDataType = "Single"
Case 7: GetDataType = "Double"
Case 8: GetDataType = "Date"
Case 10: GetDataType = "Text"
Case 12: GetDataType = "Memo"
Case Else: GetDataType = Format(Nz(pDatType), "0")
End Select
End Function
'~~~~~~~~~~~~~~~~~~~~~~~~~

then
Debug, compile

click in the RunShowFieldsForAllTables sub
press the F5 key to run

then press CTRL-G to show the debug window

copy the results and paste into a Reply to this thread



Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*




SubyRuby wrote:
> I have a table that contains contact information for people we want to
> send something to.
> I want to group my mailing labels by company so that if there are
> several people from the same company, the main label will looks like
> this:
> CompanyName
> Address1 - Address2
> City, State, Zip
>
> Then I would like to have sub-labels for each person that I can
> enclose within the main package.
>
> If there is only one person listed for that company, then I want the
> label to show the contact name as well.
> ContactName
> CompanyName
> Address1 - Address2
> City, State, Zip
>
> So, essentially I want to count the number of related records and if
> it's more than 1, remove the contact field and create a new label for
> each contact separately.
>
> Is this possible? Either in a query or the report design?
>
> Thanks in advance for any help.
 | 
Pages: 1
Prev: If .....then ....
Next: Ms Access JDBC Driver?