Two tables for One form

[Michael Conroy]

I need advice on how to handle a form where I need two tables to be the
recordsource. One table (Request) is an ODBC connection to which I have read
only access, and the other (ReqStat) is a local table I created. Because I
can't add a field to the Request table, I have created a local table with
only two fields, the PK from the linked table and a status code (0 closed, 1
open) entered by a combo box. Normally, I would use a query, but because I
can't force referencial integrity, the join between the Request table and
ReqStat table must be a left join. This makes the combo box usless when it is
empty because the recordsource will not be updateable.

So my question is this: how can I populate the form with the Request table
information, (two fields, code and description) with the recordsource set to
the local table ReqStat so changes can be made? I want to be able to see the
status, code and description on the form, allow the user to change the status
on the form, and if there is a new code, add the code and status to the local
table.

In the past I have built a seperate form for new entries. When the person
enters a field that is empty the new form opens populated with information
from that record. If the field is populated on entry the combo box can be
changed. Building a new form to change one combo seems like too much. It made
me wonder if I was making a mistake by doing something basic like useing
queries and tables as the recordsource, rather that recordsets.

I realize it is a complicated question, but any help would be greatly
appreciated. Thanks.

 

[Clifford Bass]

Hi Michael,

You may be stuck with another form; as a subform. You could try
creating a subform for the ReqStat data that displays only the request status
field. Then place that subform into the Request form, linked on your primary
key fields. If you make the border of the subform transparent the user will
never see but one form. And, no need to open two forms.

Hope this helps,

Clifford Bass

 

[Michael Conroy]

Clifford,
Thanks for the suggestion, I never really thought of a sub-form for one
field, but that would work. I apologize for my tardy response, I need to
avoid posting a question on a Friday afternoon. Anyway, what I did is
manually fill the table with a few records from the "everything" table and
the form then allowed me to enter the status from the combo. I thought
referencial integrety would prevent this, but I guess it doesn't. Thanks for
the suggestion.

 

[Clifford Bass]

Hi Michael,

No problem. And you are welcome!

Clifford Bass