Adding more tables to the Record source

[roymcd]

Is there an easy way to add a table to the record source to a Form that is already created?

In my example I have a form that uses all the info from the "Invoices" table. This table has the field "Customer ID" What I want to do is use the customer ID to get the customer name from the "Customer" table and display it in this form.

Now I what i should of done is created the form with the 2 tables using the wizard and it would of worked but I don’t want to do that way. I want to know how I write it so that I can bring in the new table after I have created it. At the moment the record source is just "invoices". I created a second test form that uses the both tables and I get a record source of ....

"SELECT Invoices.InvoiceID, Invoices.InvoiceNumber, Invoices.CustomerID, Invoices.Status, Invoices.InvoiceDate, Invoices.Amount, Customers.CompanyName FROM Customers INNER JOIN Invoices ON Customers.CustomerID=Invoices.CustomerID; "


but is there an easier way of doing this. Writing something like "Invocies + Customers" in the records source section instead of the select statement?

 

[pbaldy]

No; the only way to have more than one table in a record source is with a query.

 

[missinglinq]

If you're just wanting to bring in the single field Customer Name from the Customer table, the usual way would be to set the Control Source of the textbox to display the name using the DLookup() against the Customer table.

The syntax depends on whether CustomerID is Numeric or Text

Numeric

= DLookup("CustomerName", "Customers", "[CustomerID] = " & Me.CustomerID)

Text

= DLookup("CustomerName", "Customers", "[CustomerID] = '" & Me.CustomerID & "'")

Linq ;0)>

 

[pbaldy]

Or you can use a combo to select customer, and have a textbox with a control source of:

=ComboName.Column(1)

which would avoid another trip to the data.