CODEWITHSUNDEEP
ms-access
Shawn V. Wilson
Shawn V. Wilson

Reputation: 1111

Access: Showing fields on form from query's underlying tables

I have a query based on two tables. The query is the RecordSource for several forms.

I can easily display fields from the query (such as CompanyID) in text boxes on a form, but I'd also like to display some of those that I left behind on the tables (such as CompanyName).

The simple answer would be to add all those fields from the tables to the query, but many of those fields are used only on one of the forms. I'd hate to clutter the query with lots of such fields if I can just pull "CompanyName" from the underlying table.

I hope there's another way to do it (linking the tables or something. I've seen an SQL query used as a RecordSource, but I don't know if that applies to me).

Upvotes: 0

Views: 687

Answers (1)

June7
June7

Reputation: 21370

Normally a form is used for data entry/edit to only 1 table. Including other tables (such as lookup tables) in form RecordSource is usually not necessary and often just adds confusion.

Use a multi-column combobox to view associated data. For example, a combobox to select company can be designed to display CompanyName but save CompanyID into an Orders record.

RowSource: SELECT CompanyID, CompanyName, CompanyAddress FROM Companies ORDER BY CompanyName;
ColumnCount: 3
ColumnWidths: 0;1;0
BoundColumn: 1
ControlSource: field to save CompanyID into

Then the combobox will display company name.

Can also have a textbox display company address with expression that references combobox: =[cbxCompany].Column(2)

An alternative is DLookup() domain aggregate function expression in textbox but domain aggregate functions can cause slow performance in queries and on forms.

Yes, SQL statements can be used in RecordSource instead of referencing table or query object. Either way, if lookup tables are included, textboxes bound to those fields should be set as Locked Yes and TabStop No to prevent edit of those fields.

Upvotes: 1

Related Questions