Doable??? Label Report with a grouping

[sneuberg]

The attached zip file contains a word document with instructions on how to use the GetField function in your system. Also included is a demo database which is referred to in the instructions.

The report in the demo database cannot be run directly and must be run from the form. The form demonstrate the use of parameters. It allows the selection of Male, Females or All and the selection of only tenants on the mailing list.

I created a larger table of tenants (tblTenantsBig) for testing which have left behind in case you want to test it. This table has 928 records in it. On my system it took about 30 seconds to produce the report with this table. This isn't too bad considering the GetField function is called about 2560 times. Anyway I suggest enclosing the report open with the hourglass as I have in the demo database like this:

DoCmd.Hourglass True
DoCmd.OpenReport "rptLabels", acViewReport, "", "", acNormal
DoCmd.Hourglass False

If you have any problems using any of this please let me know.

One more thing. The order of the names on the labels is controlled by the TenantID which is an Autonumber. It's probably a good idea for it to be an autonumber, but you can't modify an autonumber value. So if you want to change the order of the tenants on the list you will have to delete them and readd them in the order you want. If you want some code to help you do this let me know.

The order of on the label could be determine by something else, e.g., sorted by last name. If you want to do something like that let me know.

 

[Valery]

OMG!!! What beautiful work! Just opened the form! I will see if I can adapt it... I will need to make it so they select their labels like below.

Originally, I thought I could have a given report, let's say one that produces a lease for all new tenants. The user would print the report and then click on "labels" and it would print the labels for THAT report... Too much, right?

In any case, they still need this flexibility to select/print labels:

Note: In every scenario, the MailiList must be true (y/n field - so = -1)

User Select Labels:

All Members with Occupants
All Members without Occupants
Select specific Tenants (here the user will select the tenants - I will need to add a "tick box" field)

The sort is always by:
1) UNIT ID
2) Members, alpha by last name
3) Occupants, alpha by last name

so not by TenantID or anything.


This is an absolute remarkable job - you went beyond every effort to assist me. I have no idea how to thank you!

 

[sneuberg]

Oops on the sort. Should have asked before. To sort on last name you will have to change the GetField function code. Change the Order By from TenantID to LastName as shown in red below

Public Function GetField(UnitNo As Long, TenantPosition As Long, FieldName As String, Optional QueryName As String = "qryTenants") As Variant
'This function returns the value of the field in the input query (QueryName) which in the UnitNo at the position Tenantposition
'
'Inputs
'UnitNo:  The Unit_Num in the input query
'TenantPosition:  A number between 1 and the number of tenants in the unit
'FieldName:  The name of the field whose value is to be returned
'QueryName:  The input query.  This query must have fields named Unit_Num and TenantID

Dim UnitRecordSet As DAO.Recordset  'Record set of the tenants in a unit
Dim qdf As DAO.QueryDef             'Querydef for the record set
Dim strSQL As String                'SQL to select the tenants
Dim TenantCount As Long             'Number of tenants in the unit

'Get the number of tenants in the unit
TenantCount = DCount("*", QueryName, "Unit_Num = " & UnitNo)
'if the TenantPosition is not between 1 and the number of tenants or the there are no tenants in the unit Null is returned
If TenantPosition > TenantCount Or TenantPosition < 1 Or TenantCount = 0 Then
    GetField = Null
    Exit Function
End If
'The SQL to obtain a record set of the tenants in the unit is formed
strSQL = "SELECT " & FieldName & "  " & _
    "FROM [" & QueryName & "] " & _
    " WHERE Unit_Num = " & UnitNo & " " & _
    "ORDER BY[COLOR="DarkRed"] LastName[/COLOR];"
Set qdf = CurrentDb.CreateQueryDef("", strSQL)

'****   Form references must be include here as shown in the example below  ****
qdf.Parameters("[forms]![frmLabels]![Gender]") = [Forms]![frmLabels]![Gender]
qdf.Parameters("[Forms]![frmLabels]![MailList]") = [Forms]![frmLabels]![MailList]
Set UnitRecordSet = qdf.OpenRecordset
'Move to the applicate record and return the value
UnitRecordSet.Move TenantPosition - 1
GetField = UnitRecordSet.Fields(FieldName)
qdf.Close
UnitRecordSet.Close

End Function

 

[sneuberg]

Oh and I just notice that my label report wasn't done very well. No spaces between first and last names. It might be easier for you to create one from scratch than to edit what I did.

 

[sneuberg]

Originally, I thought I could have a given report, let's say one that produces a lease for all new tenants. The user would print the report and then click on "labels" and it would print the labels for THAT report... Too much, right?

Not really. I don't see any problem doing that. Unless there can be multiple lease per unit I'd just extend the tblUnit to contain the lease agreement. You'd probably want to have a table with the default boilerplate lease and then copy from that when creating a new lease. But printing the labels for the lease would be just running the label report for a single unit. The down side is using an entire sheet of labels for one label.

But of course you could fill up the sheet with that label and keep them in a file for future use.

 

[Valery]

Not sure I understand. But in the example I made up, it would be several units that would have ONE lease each.

So (for example) the report could have :

Unit 1 - Jane Doe - Member + John Doe - Member + Kate Doe - Occupant > 1 lease
Unit 7 - Mike Fry - Member + Jack Fry - Occupant > 1 lease

etc..

 

[sneuberg]

Oops again. I neglected to put in the status. Since M comes before O it just needs to be added to the ORDER BY. Please change the code as shown in red

Public Function GetField(UnitNo As Long, TenantPosition As Long, FieldName As String, Optional QueryName As String = "qryTenants") As Variant
'This function returns the value of the field in the input query (QueryName) which in the UnitNo at the position Tenantposition
'
'Inputs
'UnitNo:  The Unit_Num in the input query
'TenantPosition:  A number between 1 and the number of tenants in the unit
'FieldName:  The name of the field whose value is to be returned
'QueryName:  The input query.  This query must have fields named Unit_Num and TenantID

Dim UnitRecordSet As DAO.Recordset  'Record set of the tenants in a unit
Dim qdf As DAO.QueryDef             'Querydef for the record set
Dim strSQL As String                'SQL to select the tenants
Dim TenantCount As Long             'Number of tenants in the unit

'Get the number of tenants in the unit
TenantCount = DCount("*", QueryName, "Unit_Num = " & UnitNo)
'if the TenantPosition is not between 1 and the number of tenants or the there are no tenants in the unit Null is returned
If TenantPosition > TenantCount Or TenantPosition < 1 Or TenantCount = 0 Then
    GetField = Null
    Exit Function
End If
'The SQL to obtain a record set of the tenants in the unit is formed
strSQL = "SELECT " & FieldName & "  " & _
    "FROM [" & QueryName & "] " & _
    " WHERE Unit_Num = " & UnitNo & " " & _
    "ORDER BY [COLOR="DarkRed"]Status, LastName[/COLOR];"
Set qdf = CurrentDb.CreateQueryDef("", strSQL)

'****   Form references must be include here as shown in the example below  ****
qdf.Parameters("[forms]![frmLabels]![Gender]") = [Forms]![frmLabels]![Gender]
qdf.Parameters("[Forms]![frmLabels]![MailList]") = [Forms]![frmLabels]![MailList]
Set UnitRecordSet = qdf.OpenRecordset
'Move to the applicate record and return the value
UnitRecordSet.Move TenantPosition - 1
GetField = UnitRecordSet.Fields(FieldName)
qdf.Close
UnitRecordSet.Close

End Function

 

[sneuberg]

Not sure I understand. But in the example I made up, it would be several units that would have ONE lease each.

So (for example) the report could have :

Unit 1 - Jane Doe - Member + John Doe - Member + Kate Doe - Occupant > 1 lease
Unit 7 - Mike Fry - Member + Jack Fry - Occupant > 1 lease

etc..

Sorry but that sounds a bit odd to me at least if I understand you. Why would anyone want their name on a lease for a unit they don't occupy? Is it that investors lease a bunch of these and sub lease them? And even for cases like that, wouldn't it be administratively easier to have separate leases? These questions are rhetorical. I don't need the answers.

Anyway I'll think about how this could be done and get back to you.

 

[Valery]

Update done! Thank you

--------------------------------------------------------------------------- Other question:

You are right! What I meant is:

It is ONE lease for Unit 1 - with many people who live in Unit 1 and have to sign that lease (document/report).

It is ONE lease for Unit 7 - with many people who live in Unit 7 and have to sign that lease (document/report).

So the ONE report would produce two pages - one for Unit 1 people, another for Unit 7 people > 2 leases.

NOTE on wording: Members and occupants are both tenants. The difference between them is that the members have a voting right, the occupants do not. So "member" and "occupant" are the status of a given tenant.

Hope this helps so sorry for the confusion.