More form trouble

[BBK]

Once again Forms are providing me with a problem.

I have two forms

On one form (Lease Form) i enter a monthly rent price and the payment frequency (eg: Weekly or Monthly )
On the second form (Paymeny Form) i want to display the monthly rent price from the Lease form as the amount due and the payment frequency automatically).

On Payment Form i have the control source as:

SELECT Payment.*, Lease.MonthlyRent, Lease.PaymentFrequency FROM Lease INNER JOIN Payment ON Lease.LeaseID = Payment.LeaseID;

AmountDue Control source set to -> MonthlyRent
PaymentFrequency source set to -> PaymentFrequency

Payment still wont display these 2 values for me. I have tried doing it with QBE and still no luck.

Any help would be greatly appreciated.

Thank you.

 

[Galaxiom]

The Inner Join will only show Lease records that have Payment records matching the LeaseID. So if no payment has been made the Record Source will have no records.

Use a Left Join.

 

[BBK]

I tried your advice GalaziomAtHome but no luck.

 

[vbaInet]

I suppose what you want is a RIGHT JOIN since you want to display all Payments. Either you change INNER to RIGHT or in the query builder, right-click the line that joins the two tables, choose option 2 or 3 and see the result until you get what you want.

 

[BBK]

Thanks for the reply vbaInet, i appreciate your time, but still no luck, it wont display the two fields values i want.

I dont think im explaining what i want to do correctly, but here goes again.

The Locate Tenant form i have, i find/search for the tenant i want and the form display the monthly rent amount, the first name and last name and the property address that the tenant is in ( it gets this from the lease table )

I added a button called 'Payment" and when clicked opens up a second form (Payment Form).

Where i have the following fields:

Amount Due
Date Due
Amount Paid
Date Paid
Payment Frequency
Arrears
Payment Type
Payment Category

I wanted to get the Payment form to automatically display:

Amount Due ( It gets this value from the Monthly Rent in my lease table )
Payment Frequency ( It gets This value from the Payment Frequency in my lease table )

for the Tenant i selected on the Locate Tenant Form.

But i dont think i can get to grips with the logic of what i want to do

If i attach the DB would it be any easier to understand what i am trying to do?

If i had brains i would be dangerous

 

[vbaInet]

You want to open up the Payment form to the record based on the ID from the first form?

 

[BBK]

Yes, that is it vbaInet in a nutshell

So i can enter the payment details only for the selected tenant from the first form.

 

[vbaInet]

What you need is to use the WHERE argument in the OpenForm method you're using to open the form.

Here's how:

http://baldyweb.com/wherecondition.htm

Remember, the ID must be present in the record source of the Payments form.

 

[BBK]

Thanks vbaInet once again for your help and time.

I still cant get my thick head around it, even with the link you provided, so im going trying some other way, just dont know yet what way.

Thanks again for your time and help.

 

[vbaInet]

What have you done? What errors did it show?

That's the only quickest.