Recordsource for form with 3 underlying tables

[samyers]

I have a form which contains data from 3 tables:
Table A - the main table
Table B, C - supplemental tables

A is linked to B and C in one-to-many relationships. Because there are 3 tables with one-to-many relatioships, this is not an updatable recordset. I can view records but not add, update or delete.

I have created a form based on table A only. All the fields are unbound. I can add records just fine; however, I can't find or update records. I've used the recordsource property:

strSQL = "SELECT * from A where key1 = '" & txtKey1 & "'"
Me.RecordSource = strSQL

but the form doesn't update.

What am I doing wrong? Do I need to use a recordset and pull each field value in to the form variable?

Thanks.

 

[PHV]

Why using unbound fields if you want to update ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[samyers]

The form displays data from tables B and C. My understanding (maybe I'm wrong) is that I can only use bound fields if all the underlying tables are included in the recordsource. I can't use tables B and C in the recordsource because then I can't update at all.

 

[PHV]

A form may mix bound and unbound fields, so I still don't understand why the A's fields are unbound.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[fneily]

First, PHV is correct about having a mixture of bound and unbound controls.
It would have been helpful to see your tables and fields. Maybe you're not normalized.
If your controls are UNbound and you state that you can add fields to table A, then you must be doing the addition through code. So add code to update the other tables.
You also say you're giving the form a recordsource. So what? THE CONTROLS ARE UNBOUND. So they are not bound to ANY recordsource you give it.