How to populate a Form with data from different Tables (1 Viewer)

[reddevil1]

Please could someone suggest what is the easiest way to populate a Form with data from different Tables?

In my relationships, the main Table is CONTRACT which is linked to the following Tables:-
OWNER
PROPERTY
RENTER
RENTAL INCOME

There is a ComboBox on my RENTAL INCOME FORM to choose a Contract ID.
When the Contract ID is chosen, I want the RENTAL INCOME FORM to automatically show the following information:-

CONTRACT TABLE (start date, end date, rental amount)
OWNER TABLE (owner ID, owner name, address, phone number)
PROPERTY (property ID, address etc)
RENTER (renter ID, name, phone number)

 

[Mr. B]

Based on your post, I am assuming that your tables are normalized and that they are linked appropriately. If either of these are not true you may not be able to accomplish the steps outlined below.

First, create a query and add the required tables to have it will return the information you already defined that want to see. (Be sure to include the primary key field from the Contracts table in this query.)

Once you have the query returning rows of the data you want to see, Create a form and use this query as the record source. This will cause your form to show each record from the query. You should be able to scroll through each of these records.

Next, in the Form Header area, create an unbound listbox or combo box in your form and have the listbox or combo box show the list of contracts. Be sure to include the record ID for each contract. (It would be simpler if you have the record ID as the first field in this row source.)

When you have the list of contracts being displayed in your listbox or combo box, edit the form record source query that is returning your records and add the value selected from your combox or list box as the criteria for the primary key field from the Contracts table. This will serve as the filter for your form that will only show the record for the selected contract.

Finally, create a macor in the After Update event of your combo box or list box that will just refresh your form.

I realize that this process is easier and quicker said than done, but this is a brief overview and description of the proces to accomplish what you are wanting to do. Hope it helps.

 

[reddevil1]

Mr.B,

Thank very much for your very detailed reply. It is fantastic to think there are people like you who don't mind helping relative beginners like me.

I have understood all your comments (I think) and I am able to create a Form that changes according to the ComboBox value.. However, there seems to be one problem.

The Form I am using is called RENTAL INCOME and this has it's own Rental Income ID and it's record source is the Rental Income Table.

The Contract ID ComboBox is on the Rental Income Form (I need it to link the rental payment to a specific Contract. So when the Contract ComboBox is populated, the other data is displayed so the User is sure that it is the correct Contract.

Once you have the query returning rows of the data you want to see, Create a form and use this query as the record source.

So, I don't think I am not able to design the ComboBox in the way you describe? Or have I not fully understood your comments?

 

[rodmc]

Your using the combo as a way of filtering the records, if that field is included in the query you are building for your record source then you can still use it to filter, just set its source to the new field in your query

 

[reddevil1]

I don't think I am using the combo as a filter, in this case? I am trying to use the combo as a way of showing more ContractTable data even though it is located on the Rental Income Form.

1. The Rental Income Form has its "correct" record source as the Rental Income Table.

2. The Contract Field (which is located on the Rental Income Form) has its Row Source as the Contract Query.

3. Now, how can I get all the columns of the Contract Query onto the Rental Income Form? A drag and drop would be perfect?!

 

[Mr. B]

Because the relationship between the Rental Income and the Contract is a many to one, you would want to have a subform in your main form that will be filtered to only show the rental income records for the currently selected contract. Additionally you could use a list box on your initial form instead of a sub form.

To add a new income record, you would have a button that would open a new form where you can enter the new record. You would need to pass the record ID for the current contract to the data entry form.

Take a look at the methods available when opening a form using a macro or VBA code. You will see that the last method is the "OpenArgs" method. You can use this to provide the value from the contract combo box, to the entry form for the new Rental Income record. You would need a macro in the On Current event of the data entry form and that macro would read the value passed to the form in the OpenArgs methog and assign that value to a hidden, bound control on the form when it is opened.

Hope this helps.

 

[reddevil1]

Thanks Mr.B.
I learned something about "OpenArgs" today.

Actually, the problem is the othewr way round. Eg. I have the Rental Income Form open and just need to click "New" to get a new data record.

On that form is a field to input the Contract ID. When the Contract ID is chosen, I want the form to automatically include about 10 more fields from the Contract onto the Rental Income form. Thanks for the suggestion about sub-forms. I did try this (well I fumbled about with them). I think it became too difficult for me and so I gave up.

 

[KAsad]

Pass data from a Form to another form

Hi

I have a form which I ask user for date from, date to, and Pratice. Then I want to pass them to second form which is based on a query. I want to use the items from the first form to filter the query for the second form like this
SELECT DISTINCT t1.CUST_PRACTICE, t2.CUST_NAME, t3.EMP_NAME
FROM
(
(SELECT CUSTOMER_PRACTICE.CUST_PRACTICE, TIMESHEET.TIME_DATE, TIMESHEET.TIME_ID, CUSTOMER.CUST_NAME, CUSTOMER.CUST_PRACTICE_ID
FROM (TIMESHEET
LEFT JOIN CUSTOMER ON TIMESHEET.CUST_ID = CUSTOMER.CUST_ID)
LEFT JOIN CUSTOMER_PRACTICE ON CUSTOMER.CUST_PRACTICE_ID = CUSTOMER_PRACTICE.CUST_PRACTICE_ID
WHERE ( (TIMESHEET.TIME_DATE) >=[Forms]![frmClientbyPractice].[dtFrom] AND (TIMESHEET.TIME_DATE)<=[Forms]![frmClientbyPractice].[dtTo])
AND (CUSTOMER_PRACTICE.CUST_PRACTICE=[Forms]![frmClientbyPractice].[cboTSPractice])) AS t1
LEFT JOIN
( SELECT CUSTOMER.CUST_NAME, TIMESHEET.TIME_ID
FROM TIMESHEET
LEFT JOIN CUSTOMER ON TIMESHEET.CUST_ID = CUSTOMER.CUST_ID) AS t2 ON t1.TIME_ID = t2.TIME_ID)
LEFT JOIN
( SELECT EMPLOYEE.EMP_NAME, TIMESHEET.TIME_ID
FROM (TIMESHEET
LEFT JOIN [ORDER] ON TIMESHEET.ORD_ID = ORDER.ORD_ID)
LEFT JOIN EMPLOYEE ON ORDER.ORD_PM = EMPLOYEE.EMP_ID) AS t3 ON t1.TIME_ID = t3.TIME_ID;

but the [Forms]![... items are always empty??? Please help me.
Thank you

 

[dstone10]

Re: Pass data from a Form to another form

Hi

I have a form which I ask user for date from, date to, and Pratice. Then I want to pass them to second form which is based on a query. I want to use the items from the first form to filter the query for the second form like this
SELECT DISTINCT t1.CUST_PRACTICE, t2.CUST_NAME, t3.EMP_NAME
FROM
(
(SELECT CUSTOMER_PRACTICE.CUST_PRACTICE, TIMESHEET.TIME_DATE, TIMESHEET.TIME_ID, CUSTOMER.CUST_NAME, CUSTOMER.CUST_PRACTICE_ID
FROM (TIMESHEET
LEFT JOIN CUSTOMER ON TIMESHEET.CUST_ID = CUSTOMER.CUST_ID)
LEFT JOIN CUSTOMER_PRACTICE ON CUSTOMER.CUST_PRACTICE_ID = CUSTOMER_PRACTICE.CUST_PRACTICE_ID
WHERE ( (TIMESHEET.TIME_DATE) >=[Forms]![frmClientbyPractice].[dtFrom] AND (TIMESHEET.TIME_DATE)<=[Forms]![frmClientbyPractice].[dtTo])
AND (CUSTOMER_PRACTICE.CUST_PRACTICE=[Forms]![frmClientbyPractice].[cboTSPractice])) AS t1
LEFT JOIN
( SELECT CUSTOMER.CUST_NAME, TIMESHEET.TIME_ID
FROM TIMESHEET
LEFT JOIN CUSTOMER ON TIMESHEET.CUST_ID = CUSTOMER.CUST_ID) AS t2 ON t1.TIME_ID = t2.TIME_ID)
LEFT JOIN
( SELECT EMPLOYEE.EMP_NAME, TIMESHEET.TIME_ID
FROM (TIMESHEET
LEFT JOIN [ORDER] ON TIMESHEET.ORD_ID = ORDER.ORD_ID)
LEFT JOIN EMPLOYEE ON ORDER.ORD_PM = EMPLOYEE.EMP_ID) AS t3 ON t1.TIME_ID = t3.TIME_ID;

but the [Forms]![... items are always empty??? Please help me.
Thank you

Was answered in his own thread.