Solved: linking another table to a form?

[Dokko]

Hi again,

I'm trying to link two tables to the same form, can it be done?

Example, i have one linked through record source on the form properties, can i link another somehow? When i change it i lose all my text box control sources from the 1st table.

I'm using 07, but it must save in 03,

thanks.

[Demosthine]

Good Morning.

When you say you want to link two tables, do you mean you want data relating to a single entry from two Tables? For instance, having User Login Table and Personal Data Table appear on one form and all of the data is for a single user.

To do this, you would use an INNER JOIN.

On the Property Sheet, select the Data tab. In Record Source, click the elipses (...) to open the Query Builder. Add the two tables you want to have on the data. Create an INNER JOIN by dragging the matching Field from the first Table to the other.

Your actual SQL Statement will look like something like
SELECT * FROM U1 INNER JOIN U2 ON U1.ID=U2.ID;


Now when you go back to your Form, you will have all of the Fields from both Tables available for linking to your Fields.

I hope this helps.
Scott

[CreganTur]

Scott's exactly right, but there is a second way to accomplish the above.

Forms can use Queries for their recordsets. You could create a new query that pulls just the fields you want to work with from all of the tables you desire. Then just choose that query as your record source. The only issue with this is that sometimes this results in a non-updateable recordset.

[Dokko]

Hi, tried Scotts way, i was almost doing it through a query, i think.


anyway i get an error message.

The SELECT statement includes a reserved word or an argument name that is misspelled or missing.


The linked fields are both named CID, one of them is a primary key, is that stopping it working? Do i need to create another table?

[Demosthine]

Hey there.

I'm sure you are aware Cregan, but for everyone else, you are doing essentially the same thing in these two examples. The difference is that the actual Query is always available for separate execution rather than being linked directly to that single form. You would use the same exact type of Query between my method and Cregan's.

:: Cregan ::
When would the Query become non-updateable? I haven't personally ran into this, so I'm definitely curious.

Scott

[CreganTur]

:: Cregan ::
When would the Query become non-updateable? I haven't personally ran into this, so I'm definitely curious.

It happened to a Form I built MONTHS ago... I've been looking through the DB where I think it was, but so far I haven't found it yet. I do remember that I based the Form off of a query that combined 2 or 3 tables and when I tried to update the field values via the Form I got 'The Recordset is not updateable' errors. I ended up using unbound textboxes and SQL Update queries to counter the problem.

If I find the form I'll dig into it some and see if I can reproduce the effects.

[Dokko]

Good Morning.

When you say you want to link two tables, do you mean you want data relating to a single entry from two Tables? For instance, having User Login Table and Personal Data Table appear on one form and all of the data is for a single user.

To do this, you would use an INNER JOIN.

On the Property Sheet, select the Data tab. In Record Source, click the elipses (...) to open the Query Builder. Add the two tables you want to have on the data. Create an INNER JOIN by dragging the matching Field from the first Table to the other.

Your actual SQL Statement will look like something like
SELECT * FROM U1 INNER JOIN U2 ON U1.ID=U2.ID;


Now when you go back to your Form, you will have all of the Fields from both Tables available for linking to your Fields.

I hope this helps.
Scott

Hi.

I just made two dummy tables and it that didn't work, same error even with one table doing nothing.

Here's the generated SQL:

FROM Course INNER JOIN studentdetails ON Course.CID = studentdetails.CID;

Ha, just figured, the code generating was missing out the *