Form with data from two tables

[Opid]

Hello all, I am currently trying to create a form that allows the user to create a new record on a certain table named "transactions". Another table named "Tenant" holds information such as amount of rent due. I would like this form to display the amount of rent from the tenant table next to the pay rent text field of the transactions table. I have a combo box that allows the user to select a specific room number. This combo box should then populate the rent due field by getting the information from the Tenant table and the transaction table should be updated when the user clicks process. The room number should be then given to the transaction.unit field. Any help or suggestions would be appreciated. Let me know if this makes sense. Thanks in advance

[June7]

Room number is the pk/fk linking fields? If so, one way is to have the form RecordSource be a query that joins the two tables. The query jointype would be 'show all records from Transactions and only those from Tenant that are equal'. Bind textbox to the rent field from Tenant table and set properties: Locked to Yes and TabStop to No.

Another method is multi-column combobox, one column has the rent field and can be hidden. A textbox has a ControlSource set to the rent column.

[Opid]

Thanks for the help . Couple of questions though. How can you hide the columns easily and how do I access the rent column? When I select the controlSource for the textbox, the rent column does not appear. Is it some variation of combobox.rent?

[June7]

Review this tutorial about multi-column combobox http://datapigtechnologies.com/flash...combobox3.html

Textbox ControlSource refers to the rent column of combobox with: =comboboxname.Column(x)

x is the index of the column. Column index starts with 0 so if rent is column 2 the index is 1.

[Opid]

Thank you. Works perfectly.