showing several queires results running from 1 command button

[7fttalllaura]

Hi,

If anyone could provide help with a little problem I am having in creating a rent payment schedule in Access 2007 it would be great

I am trying to currently create a command button to carry out several queries to run, however I need all the results shown all within one either list (report) or in a table to allow for tracking of payments due. There are 12 months of rent due for each lease.

For example the query’s are below:-

Overdue rent 1st month
Overdue rent 2nd month


The queries set up are as follows (each different for the number of month that the rent is due)

SELECT [Rent Schedule].*, [Rent Schedule].[Rent 1st Month Due], [Rent Schedule].[Rent 1st Month Paid]
FROM [Rent Schedule]
WHERE ((([Rent Schedule].[Rent 1st Month Due]) Between Date() And Date()+2) AND (([Rent Schedule].[Rent 1st Month Paid])=No));

The results I require is all current rents that are due from all properties no matter what month as all properties are checked in at different months.

I have tried the following command button however I require the coding to allow for all results to be shown from each query.

Private Sub Command54_Click()
DoCmd.SetWarnings False
DoCmd.OpenQuery "Rent 1st Month Due", acViewNormal
DoCmd.OpenQuery "Rent 2nd Month Due", acViewNormal
DoCmd.OpenQuery "Rent 3rd Month Due", acViewNormal
DoCmd.SetWarnings True

End Sub


If anyone could help with this it would be amazing.

Thanks,
Laura J

 

[HiTechCoach]

Laura J,

Welcome to AWF!

Are the There are 12 months of rent due for each lease all in one record?

If yes, then this is what is causing you lots of issues. This is really a design that violates the "rules of data normalization." This should be fixed first. You do not have to fix tis, but it will only make it a lot more work to get what you want.

TIP:
To combine the output of the multiple queries if they all have the same fields is to use a UNION query to combine them into a single recordset.

 

[7fttalllaura]

Hiya,

Thanks for the quick response.

The queries i have made are to show rent that is due to be paid within 2days of the query being run.

I think i may have made a design error in this, however I am unsure which is the best way to design a button show run through all the rent schedules to show rent that is due to be paid.

It is to help manage the properties to ensure that we know when money is due to be paid and also to make sure it is paid when due.

Do you have suggestions that i could use to create a query / command button to run this to be selected each day to flag up any payments that are due / overdue? or would it be suggestable to redesign the table for rent schedule to just run from 1 date instead of 12 due dates for each lease?

Thanks,
Laura