How do I bind a control on a form to a different table than the form is bound to?

[Mark Gonyea]

Background:
MainDoc table = serial number field only.
NameList = SerNum
Name
Address
CompanyList = SerNum
Company
Address

I have to program a way to input data using this form which is bound to
MainDoc table, but all the fields in the form are fields in either the
NameList or CompanyList tables.

I am using drop down boxes on the form with Record Source being 'Select *
from NameList' or 'Select * from CompanyList'. But when I try to bind these
drop downs the only field names are the fields from MainDoc table. I have
the proper 1 to Many relationships between these tables using SerNum as the
key.

Question: How do I get these to bind to the right table?

The goal is for the user to open the form and add a new serial number, then
select from drop downs the Name and Company. If they are not in the drop
down there is a button to add the new entry to those table with separate
form.

I just cannot seem to save any new records in the MainDoc table using this
form.

Thanks for any help.

\M

 

[Steve Schapel]

Mark,

The simple answer to your question is "You can't". That's just not how
it works. If you want to use your form for data entry/editing, the
controls on the form have to be bound to fields that exist in the form's
Record Source. But before we get onto that, it would seem that your
table design needs to be reviewed. I haven't quite figured out what
your data is about, but at first glance it doesn't really hang together
correctly. Would you post back with more details, maybe with examples,
of what the data is that is in the 3 tables, and how it they are
supposed to relate to each other?

 

[John Vinson]

The goal is for the user to open the form and add a new serial number, then
select from drop downs the Name and Company. If they are not in the drop
down there is a button to add the new entry to those table with separate
form.

It appears that you have your relationships backwards. If a given
Company can be linked to several records, and the same for names, a
better design would be to have three tables like:

MainTable
SerNum
CompanyID
NameID

Companies
CompanyID <Primary Key>
CompanyName

Names
NameID <Primary Key>
NameValue Text