Display info from one table onto a form belonging to another table

[BBK]

I have two tables

Property Table and Tenant Table

I have TenantID in both tables:
Tenant Table -> TenantID is the primary key
Property Table -> TenantID is the foregin key

Joined with a one to one relationship, because my thinking is only one tenant can have one property at one time

I have a form running on top of the Property table.

I have a textbox in the Property form where i would like to display the First and Last name of the person that is currently leaseing the selected property

I have spent 5 hours trying to get is to display the name but i have lost most of my hair at this stage trying to get it to work.

Keep getting the same error more or less, which is as follows:

Can anyone please point me in the right direction to rectify this problem.

Thank you in advance for any help.

 

[ghudson]

Ensure the names data fields are in your record source of your form. Add the fields to the record source [query, SQL] if neccessary.

 

[BBK]

Ok, i tried your suggestion ghudson for which im grateful, but am still unable to get it to work how i want it to. Im still getting the same error

 

[vbaInet]

Pick the control source from the drop down list. I believe the comma in the field name might be causing problems too.

 

[BBK]

My lack of knowledge is very furstrating.....

My form is getting info from two different tables and i can get the form to show the last name of the person leasing the selected property by using the following code:

SELECT Tenant.LastName, Lease.TenantID AS Lease_TenantID, Property.TenantID AS Property_TenantID
FROM Tenant INNER JOIN (Property INNER JOIN Lease ON Property.[PropertyID] = Lease.[PropertyID]) ON Tenant.[TenantID] = Lease.[TenantID];

And i can get the rest of the details on the same form using the following code:

SELECT Property.*, [Address] & " " & [Town] & ", " & [County] AS AddressLong
FROM Property;

I just cant get it to do both at the same time, i can only have one or the other . I cant get it all into one query because i dont understand how to do the join correctly i think is the problem.

Can anyone give me the correct syntax from using the two queries above. Please

 

[vbaInet]

If you pick from the list in the PROPERTY SHEET all you will find is TenantID not SELECT Tenant ... etc. So open up the property sheet for that textbox, go to the Data tab, in the CONTROL SOURCE property drop down the list and select TenantID

 

[BBK]

I cant. I dont have a tenantID to select and if i try and change it when i go to form view it displays the form with all the fields missing..... basically a blank page.

The bottom query i post above. If i select the form and use that query as the control source it will fill in my Address, town, county fields in my form, but i cant get the name of the tenant leasing the particular property.

The top code i posted i created a seperate query and when i add the to the form as the control source i can get the form to display the name of the tenant leasing the particular property but all the other fields just display

#Name?

My two queries need to be joined in some way i think. Maybe im totally wrong

 

[vbaInet]

Might just be quicker if you post your db and I'll have a quick look. Get rid of sensitive info, compact and repair, input some dummy data for testing, then post.

 

[Brianwarnock]

Since the property table is in the first query why can't you get all that you want at that point?

Brian

 

[BBK]

Problem solved thanks for the help vbaInet, greatly appreciated

Also thank you BrianWarnock and ghudson