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

  • Thread starter Thread starter Mark Gonyea
  • Start date Start date
M

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
 
Hi Mark,

A Form can only have 1 RecordSource (e.g., a Table or Query). Try creating
a query based on the 2 tables and see if binding your Form to this Query
gives you the solution you are looking for.


Regards,

Eric Butts
Microsoft Access Support
(e-mail address removed)
"Microsoft Security Announcement: Have you installed the patch for
Microsoft Security Bulletin MS03-026? If not Microsoft strongly advises
you to review the information at the following link regarding Microsoft
Security Bulletin MS03-026
<http://www.microsoft.com/security/security_bulletins/ms03-026.asp> and/or
to visit Windows Update at <http://windowsupdate.microsoft.com/> to install
the patch. Running the SCAN program from the Windows Update site will help
to insure you are current with all security patches, not just MS03-026."

This posting is provided "AS IS" with no warranties, and confers no rights

--------------------
| From: "Mark Gonyea" <[email protected]>
| Newsgroups: microsoft.public.access.forms
| Subject: How to bind a control on form to a different table than form is
bound to?
| Lines: 34
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1409
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
| Message-ID: <[email protected]>
| Date: Fri, 04 Jun 2004 15:47:17 GMT
| NNTP-Posting-Host: 24.213.244.11
| X-Complaints-To: (e-mail address removed)
| X-Trace: twister.nyroc.rr.com 1086364037 24.213.244.11 (Fri, 04 Jun 2004
11:47:17 EDT)
| NNTP-Posting-Date: Fri, 04 Jun 2004 11:47:17 EDT
| Organization: Road Runner
| Path:
cpmsftngxa10.phx.gbl!TK2MSFTFEED01.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.s
ul.t-online.de!t-online.de!news-spur1.maxwell.syr.edu!news.maxwell.syr.edu!n
ews-rtr.nyroc.rr.com!news-out.nyroc.rr.com!twister.nyroc.rr.com.POSTED!53ab2
750!not-for-mail
| Xref: cpmsftngxa10.phx.gbl microsoft.public.access.forms:268769
| X-Tomcat-NG: microsoft.public.access.forms
|
| 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
|
|
|
 
Does this mean that a given something with a particular serial number could
conceivably have multiple associated Names and multiple related Companies?
And that any given Name and any given Company can be related only to a
single serial number? That is the result of the table design you describe.
If not, post back to clarify and we'll see what we can come up with.

If so, put the Combo Boxes each in their own Subform based on the
appropriate table, with the Serial Number being the LinkMasterFields and
LinkChildFields entry to relate them to the main Form's SerialNumber.
Caution: if you do this, it will *** CHANGE *** the serial number for an
existing Name/Company if you select an existing one!

Just for the record... a ComboBox has a RowSource, from which it obtains the
items from which the user can choose, and a ControlSource, which is the
field where the chosen item is to be stored, but only Forms and Reports have
a RecordSource that includes the table/query in which the ControlSource
fields are included.

Another approach would be to use unbound Combo Boxes and put code in their
AfterUpdate events to write the record to the table.
 
Back
Top