Select reports to pring

Topic

New Reply

Using Access 2003

A form has a list box, “lstRetirementHomes” which includes the names of 20 Retirement Homes in which members of a church might live.

I have established the ability for the church secretary to select an individual Retirement Home from the list and print a list of residents in that Home.

However, the church secretary would like to be able to click one command button, and print a list for the minsters which shows each retirement home, the residents, etc.

One of the problems is that some of the Retirement Homes might have no residents from the church living in them.

What I need to be able to do is, when the command button “Preview All” is pressed have a routine that will cycle through and prepare the reports for those Retirement Homes in which people from the church reside.

Any pushes in the right direction would be appreciated.

Thanks.

Tom

Reply | Quote

Replies

Using Access 2003

A form has a list box, “lstRetirementHomes” which includes the names of 20 Retirement Homes in which members of a church might live.

I have established the ability for the church secretary to select an individual Retirement Home from the list and print a list of residents in that Home.

However, the church secretary would like to be able to click one command button, and print a list for the minsters which shows each retirement home, the residents, etc.

One of the problems is that some of the Retirement Homes might have no residents from the church living in them.

What I need to be able to do is, when the command button “Preview All” is pressed have a routine that will cycle through and prepare the reports for those Retirement Homes in which people from the church reside.

Any pushes in the right direction would be appreciated.

Thanks.

Tom

Reply | Quote

Using Access 2003

A form has a list box, “lstRetirementHomes” which includes the names of 20 Retirement Homes in which members of a church might live.

I have established the ability for the church secretary to select an individual Retirement Home from the list and print a list of residents in that Home.

However, the church secretary would like to be able to click one command button, and print a list for the minsters which shows each retirement home, the residents, etc.

One of the problems is that some of the Retirement Homes might have no residents from the church living in them.

What I need to be able to do is, when the command button “Preview All” is pressed have a routine that will cycle through and prepare the reports for those Retirement Homes in which people from the church reside.

Any pushes in the right direction would be appreciated.

Thanks.

Tom

Reply | Quote

Using Access 2003

A form has a list box, “lstRetirementHomes” which includes the names of 20 Retirement Homes in which members of a church might live.

I have established the ability for the church secretary to select an individual Retirement Home from the list and print a list of residents in that Home.

However, the church secretary would like to be able to click one command button, and print a list for the minsters which shows each retirement home, the residents, etc.

One of the problems is that some of the Retirement Homes might have no residents from the church living in them.

What I need to be able to do is, when the command button “Preview All” is pressed have a routine that will cycle through and prepare the reports for those Retirement Homes in which people from the church reside.

Any pushes in the right direction would be appreciated.

Thanks.

Tom

Reply | Quote

Using Access 2003

A form has a list box, “lstRetirementHomes” which includes the names of 20 Retirement Homes in which members of a church might live.

I have established the ability for the church secretary to select an individual Retirement Home from the list and print a list of residents in that Home.

However, the church secretary would like to be able to click one command button, and print a list for the minsters which shows each retirement home, the residents, etc.

One of the problems is that some of the Retirement Homes might have no residents from the church living in them.

What I need to be able to do is, when the command button “Preview All” is pressed have a routine that will cycle through and prepare the reports for those Retirement Homes in which people from the church reside.

Any pushes in the right direction would be appreciated.

Thanks.

Tom

Reply | Quote

Using Access 2003

A form has a list box, “lstRetirementHomes” which includes the names of 20 Retirement Homes in which members of a church might live.

I have established the ability for the church secretary to select an individual Retirement Home from the list and print a list of residents in that Home.

However, the church secretary would like to be able to click one command button, and print a list for the minsters which shows each retirement home, the residents, etc.

One of the problems is that some of the Retirement Homes might have no residents from the church living in them.

What I need to be able to do is, when the command button “Preview All” is pressed have a routine that will cycle through and prepare the reports for those Retirement Homes in which people from the church reside.

Any pushes in the right direction would be appreciated.

Thanks.

Tom

Reply | Quote

Using Access 2003

A form has a list box, “lstRetirementHomes” which includes the names of 20 Retirement Homes in which members of a church might live.

I have established the ability for the church secretary to select an individual Retirement Home from the list and print a list of residents in that Home.

However, the church secretary would like to be able to click one command button, and print a list for the minsters which shows each retirement home, the residents, etc.

One of the problems is that some of the Retirement Homes might have no residents from the church living in them.

What I need to be able to do is, when the command button “Preview All” is pressed have a routine that will cycle through and prepare the reports for those Retirement Homes in which people from the church reside.

Any pushes in the right direction would be appreciated.

Thanks.

Tom

Reply | Quote

Using Access 2003

A form has a list box, “lstRetirementHomes” which includes the names of 20 Retirement Homes in which members of a church might live.

I have established the ability for the church secretary to select an individual Retirement Home from the list and print a list of residents in that Home.

However, the church secretary would like to be able to click one command button, and print a list for the minsters which shows each retirement home, the residents, etc.

One of the problems is that some of the Retirement Homes might have no residents from the church living in them.

What I need to be able to do is, when the command button “Preview All” is pressed have a routine that will cycle through and prepare the reports for those Retirement Homes in which people from the church reside.

Any pushes in the right direction would be appreciated.

Thanks.

Tom

Reply | Quote

I’d have two buttons on the form
One to report on an individual home.
One to report on all homes.

Reply | Quote

I’d have two buttons on the form
One to report on an individual home.
One to report on all homes.

Reply | Quote

I’d have two buttons on the form
One to report on an individual home.
One to report on all homes.

Reply | Quote

I’d have two buttons on the form
One to report on an individual home.
One to report on all homes.

Reply | Quote

I’d have two buttons on the form
One to report on an individual home.
One to report on all homes.

Reply | Quote

I’d have two buttons on the form
One to report on an individual home.
One to report on all homes.

Reply | Quote

I’d have two buttons on the form
One to report on an individual home.
One to report on all homes.

Reply | Quote

I’d have two buttons on the form
One to report on an individual home.
One to report on all homes.

Reply | Quote

Perhaps my post was confusing.

I have the two buttons alright. It’s a matter of working out a routine that cycles through the Retirement Homes in the list box, and printing specific homes where church members reside. As I indicated a few of the 20 homes will have none of the church members living in them.

Tom

Reply | Quote

Perhaps my post was confusing.

I have the two buttons alright. It’s a matter of working out a routine that cycles through the Retirement Homes in the list box, and printing specific homes where church members reside. As I indicated a few of the 20 homes will have none of the church members living in them.

Tom

Reply | Quote

Perhaps my post was confusing.

I have the two buttons alright. It’s a matter of working out a routine that cycles through the Retirement Homes in the list box, and printing specific homes where church members reside. As I indicated a few of the 20 homes will have none of the church members living in them.

Tom

Reply | Quote

Perhaps my post was confusing.

I have the two buttons alright. It’s a matter of working out a routine that cycles through the Retirement Homes in the list box, and printing specific homes where church members reside. As I indicated a few of the 20 homes will have none of the church members living in them.

Tom

Reply | Quote

Perhaps my post was confusing.

I have the two buttons alright. It’s a matter of working out a routine that cycles through the Retirement Homes in the list box, and printing specific homes where church members reside. As I indicated a few of the 20 homes will have none of the church members living in them.

Tom

Reply | Quote

Perhaps my post was confusing.

I have the two buttons alright. It’s a matter of working out a routine that cycles through the Retirement Homes in the list box, and printing specific homes where church members reside. As I indicated a few of the 20 homes will have none of the church members living in them.

Tom

Reply | Quote

Perhaps my post was confusing.

I have the two buttons alright. It’s a matter of working out a routine that cycles through the Retirement Homes in the list box, and printing specific homes where church members reside. As I indicated a few of the 20 homes will have none of the church members living in them.

Tom

Reply | Quote

Perhaps my post was confusing.

I have the two buttons alright. It’s a matter of working out a routine that cycles through the Retirement Homes in the list box, and printing specific homes where church members reside. As I indicated a few of the 20 homes will have none of the church members living in them.

Tom

Reply | Quote

So, the big question is do you have something in the residents table,
that would indicate whether that resident is a church member.
If you do then it can be done.
I think you will need to provide a bit more information
otherwise any help will be a very broad example.

Reply | Quote

So, the big question is do you have something in the residents table,
that would indicate whether that resident is a church member.
If you do then it can be done.
I think you will need to provide a bit more information
otherwise any help will be a very broad example.

Reply | Quote

So, the big question is do you have something in the residents table,
that would indicate whether that resident is a church member.
If you do then it can be done.
I think you will need to provide a bit more information
otherwise any help will be a very broad example.

Reply | Quote

So, the big question is do you have something in the residents table,
that would indicate whether that resident is a church member.
If you do then it can be done.
I think you will need to provide a bit more information
otherwise any help will be a very broad example.

Reply | Quote

So, the big question is do you have something in the residents table,
that would indicate whether that resident is a church member.
If you do then it can be done.
I think you will need to provide a bit more information
otherwise any help will be a very broad example.

Reply | Quote

So, the big question is do you have something in the residents table,
that would indicate whether that resident is a church member.
If you do then it can be done.
I think you will need to provide a bit more information
otherwise any help will be a very broad example.

Reply | Quote

So, the big question is do you have something in the residents table,
that would indicate whether that resident is a church member.
If you do then it can be done.
I think you will need to provide a bit more information
otherwise any help will be a very broad example.

Reply | Quote

So, the big question is do you have something in the residents table,
that would indicate whether that resident is a church member.
If you do then it can be done.
I think you will need to provide a bit more information
otherwise any help will be a very broad example.

Reply | Quote

Re “the big question”…

The database ONLY has church members.

There are 20 Retirement Homes in our city. These are all in the list box.

Some of those Retirement Homes have none of our church members living in them. Some have None.

Tom

Reply | Quote

Re “the big question”…

The database ONLY has church members.

There are 20 Retirement Homes in our city. These are all in the list box.

Some of those Retirement Homes have none of our church members living in them. Some have None.

Tom

Reply | Quote

Re “the big question”…

The database ONLY has church members.

There are 20 Retirement Homes in our city. These are all in the list box.

Some of those Retirement Homes have none of our church members living in them. Some have None.

Tom

Reply | Quote

Re “the big question”…

The database ONLY has church members.

There are 20 Retirement Homes in our city. These are all in the list box.

Some of those Retirement Homes have none of our church members living in them. Some have None.

Tom

Reply | Quote

Re “the big question”…

The database ONLY has church members.

There are 20 Retirement Homes in our city. These are all in the list box.

Some of those Retirement Homes have none of our church members living in them. Some have None.

Tom

Reply | Quote

Re “the big question”…

The database ONLY has church members.

There are 20 Retirement Homes in our city. These are all in the list box.

Some of those Retirement Homes have none of our church members living in them. Some have None.

Tom

Reply | Quote

Re “the big question”…

The database ONLY has church members.

There are 20 Retirement Homes in our city. These are all in the list box.

Some of those Retirement Homes have none of our church members living in them. Some have None.

Tom

Reply | Quote

Re “the big question”…

The database ONLY has church members.

There are 20 Retirement Homes in our city. These are all in the list box.

Some of those Retirement Homes have none of our church members living in them. Some have None.

Tom

Reply | Quote

Sorry, my wording was repetitive.

I said, “Some of those Retirement Homes have none of our church members living in them. Some have None.”

What that should read is: “Some of those Retirement Homes have a few of our church members living in them. Some have None.”

Tom

Reply | Quote

Sorry, my wording was repetitive.

I said, “Some of those Retirement Homes have none of our church members living in them. Some have None.”

What that should read is: “Some of those Retirement Homes have a few of our church members living in them. Some have None.”

Tom

Assuming you have a join table for MembersinRetirement homes i.e. three tables, Members, Retirement Homes, and MembersinRH.
MembersinRH has two fields MemberName(or better yet ID), HomeName and the two fields together are the primary key (which means that you can’t accidentally put one person in two homes)
Base your lookup on a query of retirement homes for the report on MembersInRH and set the HomeName to unique values and then the list your secretary sees for this report will only be the homes that have people in them. She chooses one or all. As long as this query is the basis for filtering your records, you won’t get any homes without members resident in them.

Reply | Quote

Sorry, my wording was repetitive.

I said, “Some of those Retirement Homes have none of our church members living in them. Some have None.”

What that should read is: “Some of those Retirement Homes have a few of our church members living in them. Some have None.”

Tom

Reply | Quote

Sorry, my wording was repetitive.

I said, “Some of those Retirement Homes have none of our church members living in them. Some have None.”

What that should read is: “Some of those Retirement Homes have a few of our church members living in them. Some have None.”

Tom

Reply | Quote

Sorry, my wording was repetitive.

I said, “Some of those Retirement Homes have none of our church members living in them. Some have None.”

What that should read is: “Some of those Retirement Homes have a few of our church members living in them. Some have None.”

Tom

Reply | Quote

Sorry, my wording was repetitive.

I said, “Some of those Retirement Homes have none of our church members living in them. Some have None.”

What that should read is: “Some of those Retirement Homes have a few of our church members living in them. Some have None.”

Tom

Reply | Quote

Sorry, my wording was repetitive.

I said, “Some of those Retirement Homes have none of our church members living in them. Some have None.”

What that should read is: “Some of those Retirement Homes have a few of our church members living in them. Some have None.”

Tom

Reply | Quote

Sorry, my wording was repetitive.

I said, “Some of those Retirement Homes have none of our church members living in them. Some have None.”

What that should read is: “Some of those Retirement Homes have a few of our church members living in them. Some have None.”

Tom

Reply | Quote

Sorry, my wording was repetitive.

I said, “Some of those Retirement Homes have none of our church members living in them. Some have None.”

What that should read is: “Some of those Retirement Homes have a few of our church members living in them. Some have None.”

Tom

Reply | Quote

Andrew is correct. You need to have some indicator in your Resident table to show what church they belong to. That indicator would reference (as a foreign key) another table in which you list the churches they might belong to – including, of course, “none”. You can then build a relationship between the tables which will allow you to generate the required report using a query.

Reply | Quote

PeterN
I think I see where you are going with this, but let me make sure I understand you.

You suggest 3 tables: Members, RetirementHomes, MembersInRH

Currently, I have 2 tables: tblTrinity, tblRetirementHome

If I’m to add this 3rd table, MembersInRH, I’m assuming you mean adding a check box to frmTrinity, where members’ data is entered…this box checked if the person’s address is a Retirement Home address.

The problem I see with that is that the Secretary needs to know, by memory, the addresses of all 20 Retirement Homes, so she knows that if a member moves from A to B that B is a Retirement Home. Might work with the regular Secretary, but relief fill-ins have created database problems before. Also if the current Secretary left and a new Secretary was hired, she’d be a while getting up to speed.

Unless you had some method in mind by which this check box were filled in automatically as necessary.

Or maybe I’m missing your point?

The interesting thing here is that, as often happens, I’m spending 20 hours or more, in order to save the Secretary 10 minutes twice a year!

Tom

Reply | Quote

Why don’t you give us a zipped compacted database so we can see the table structures.

Else show us what the table structures are.

What does tblTrinity hold? Does it hold the members?

If tblTrinity holds the members, is the primary key (or a unique key) from tblRetirementHome also in tblTrinity.

Reply | Quote

Hi Tom: I revised my thinking. I have a stripped down database that does what you need unfortunately I can’t get it to upload. I’ll try to do it later today.

Retirement Homes is nothing more than a lookup table with a list of all the homes. For printing, you have a query that filters out the homes with no residents. The form for printing the report has two options, all or any single home. (It is possible to do a multi select so you can choose more than one home, but that is a lot more complicated).

Let me know if you have any questions.

Reply | Quote

Peter, I will await the db you are going to try to send.

patt
The database is far too large and filled with too many objects to attempt to strip and zip and post.
Right, tblTrinity holds the members and their demographical data

The PrimaryKey from tblRetirementHome is not in tblTrinity and it won’t work to put it there. I have worked through several possibilities as to how it might be put there but there’s too great a margin for error. The principal problem is that, well e.g.: Member John Jones resides at 3 – 144 Anywhere Street. Turns out that 144 Anywhere Street is Retirement Home A but that is incidental. Trying to match up the right string of 3 – 144 with the Street address of Retirement Home A is where the margin for error comes in, as it relies on the secretary recognizing that the address is a Retirement Home.

I know that I’m applying handcuffs by not being able to provide more information than that. I have to look for broad concepts and not specific methods to work through this.

Thanks for trying to help.

Tom

Reply | Quote

For those interested. Here is the database I talked about previously.

Reply | Quote

I have used a modification of PeterN’s suggestion.

1. Created a new field in tblTrinity for the Apartment/Suite/Unit/Room number
2. Linked tblTrinity with tblRetirementHome by RetHomeID (one to many)
3. On frmTrinity, the RetHomeID field is hidden. The BeforeUpdate event has the following code:

Code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
   On Error GoTo Form_BeforeUpdate_Error

If IsNull(Me.HouseNbr) Or IsNull(Me.Street) Then
Exit Sub
End If

Dim strAddress As String
Dim varTemp As Variant
Dim streetTemp As String
Dim retStreetTemp As String
Dim retStreetNbrTemp As Variant
Dim pcodeTemp As Variant

streetTemp = Nz(Trim(Left(Me.Street, InStr(Me.Street, " "))))
retStreetTemp = Nz(DLookup("TrimStreet", "tblRetirementHome", "TrimStreet = '" & streetTemp & "'"))
retStreetNbrTemp = Nz(DLookup("StreetNbr", "tblRetirementHome", "StreetNbr = '" & Me.HouseNbr & "'"))
pcodeTemp = Nz(DLookup("Code", "tblRetirementHome", "StreetNbr = '" & Me.HouseNbr & "' And TrimStreet = '" & streetTemp & "'"))

strAddress = Me.HouseNbr & " " & streetTemp & ", " & Me.City & " " & Format(Me.Code, "@@@ @@@")

varTemp = Nz(DLookup("RetHomeID", "tblRetirementHome", "TrimCombinedAddress = '" & strAddress & "'"), "")
Me.RetHomeID = varTemp

If (Me.HouseNbr = retStreetNbrTemp) And (streetTemp = retStreetTemp) And (Me.Code  pcodeTemp) Then
    Call MsgBox("You have entered the address for a Retirement Home." _
                            & vbCrLf & "" _
                            & vbCrLf & "The Postal Code for that address is " & Format(pcodeTemp, "@@@ @@@") _
                            & vbCrLf & "That will be entered as the Postal Code for this record." _
                            & vbCrLf & "" _
                            & vbCrLf & "    Please verify all data to ensure that the member" _
                            & vbCrLf & "shows on the list of persons in that Retirement Home." _
                            & vbCrLf & "" _
                            & vbCrLf & "ALSO enter a Unit, Apt. or Room # if you have one." _
                            , vbExclamation, "Retirement Home address check")

Me.Code = pcodeTemp
strAddress = Me.HouseNbr & " " & streetTemp & ", " & Me.City & " " & Format(Me.Code, "@@@ @@@")
varTemp = Nz(DLookup("RetHomeID", "tblRetirementHome", "TrimCombinedAddress = '" & strAddress & "'"), "")
Me.RetHomeID = varTemp

End If

   On Error GoTo 0
   Exit Sub

Form_BeforeUpdate_Error:
    
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Form_BeforeUpdate of VBA Document Form_frmTrinity"
    
End Sub

4. There is also a command button on frmTrinity that opens a frmRetirementHomeList on which there is a list box which contains the names and addresses of the respective Retirement Homes. (this is to cover the situations where the secretary knows only the name of the Retirement Home to which the member has moved.)
When the secretary double clicks a row in the list box, the StreetNbr, Street, City and Postal Code fields are replaced by those of the relevant Retirement Home.

5. It goes without saying that I had to modify several queries and reports to accommodate the additional Apartment/Suite/Unit/Room number field.

– – – – – – – –
One small bugbear remains. There has been an inconsistency of data entry as to the Street name and Direction.
Sometimes Avenue is entered as Avenue, sometimes as Ave., sometimes as Ave. The same holds for Street (Street, St., St) and Road (Road or Rd. or Rd)
Street directions are sometimes East or West and sometimes E. or W.
Therefore a Street will appear as Abernathy Street West, or Abernathy St. W. or whatever.

Canada Post prefers abbreviations and no punctuation and Uppercase letters. i.e. AVE, ST, RD, N, W, S, E

My Question:
I can fix existing entries easily. Aside from breaking out the Street into 3 fields (Street, Street Type, Direction) is there a way to force, for example, an entry of Avenue or Ave. to AVE, Street or St. to ST, etc.?

Tom

Reply | Quote

The other thing I can do, I suppose, is to put a label on the bottom of frmTrinity, indicating Canada Post guidelines for entering Streets and Street Directions

Tom

Reply | Quote

You can fix the existing data using the find and replace function if you don’t have a large number of address – say < 10,000. For more than that you would probably want to create some smart queries. You could also get more sophisticated with your data entry process by adding an AfterUpdate event on the control that contains the street address and checking for the string "avenue" or "ave." using the InStr function and replace them with the appropriate string – but that will involve some fairly complex code.

Reply | Quote