Can I add data to two tables, using one form?

[David K]

Our "Main" form generates a bill for service, and requires name, customer #,
date, etc. It was built to populate a "many" Table.

Customers can be new (to us) or repeat. We are a small part of the company,
and don't create the customer numbers. After creating the Main table and the
form that populates it, I realized I need a "One" table with names and the
unique customer # (so I made it).

I still want people to use that single form to enter the customer #, last
name and first name (along with other data relevant to the charges). But I
also want the new customer form to grow as new customers occur, without
asking the users to use a separate form.

Can I get the single form to put the new customer # in both the "One" and
the "Many" tables, as well as associate that unique number with the last and
first names?

 

[KARL DEWEY]

Can I get the single form to put the new customer # in both the "One" and
the "Many" tables, as well as associate that unique number with the last and
first names?
Click on menu TOOLS - Relationships. If the two tables are not there then
click on the Show Table icon (it has a yellow plus sign in the upper left and
a table view in the lower right) and add the tables. Click on the 'customer
#' on the 'one' table and drag to the 'customer #' field of the many. In the
new window select Enforce Referential Integrity. Also select Cascade Update
Related Fields.

In the main form in design view and click on the extreme upper left corner
of the subform. Click on menu VIEW - Properties. Edit the Master and Child
links to contain the 'customer #' field.

 

[David K]

When I do this, and try to enter a new name into the form, a dialog box tells
me I can't. It offers several possible reasons, one of which is that data
between the two tables would become different if it lets me proceed. If I go
back and remove the "referential integrity" from the relationship, then it
lets me add the new customer, but of course I then have the new customer only
in the "many" table and not in the "one" table.
Perhaps I erred in the way I edited the 'master' and 'child' links? In the
rows that pertain to these links, I typed in the name of the columns. (In
both tables, the column title is "Medical Record Number"). There was no
ellipsis or drop-down-arrow to offer me choices for this change.

 

[KARL DEWEY]

If I go back and remove the "referential integrity" from the relationship,
then it
lets me add the new customer, but of course I then have the new customer only
in the "many" table and not in the "one" table.
Your data entry method is wrong. A new customer would be entered in the
'one' side.

 

[John W. Vinson]

Our "Main" form generates a bill for service, and requires name, customer #,
date, etc. It was built to populate a "many" Table.

Customers can be new (to us) or repeat. We are a small part of the company,
and don't create the customer numbers. After creating the Main table and the
form that populates it, I realized I need a "One" table with names and the
unique customer # (so I made it).

I still want people to use that single form to enter the customer #, last
name and first name (along with other data relevant to the charges). But I
also want the new customer form to grow as new customers occur, without
asking the users to use a separate form.

Can I get the single form to put the new customer # in both the "One" and
the "Many" tables, as well as associate that unique number with the last and
first names?

Normally this would be a one (customer) to many (orders) relationship. You
would have a Customers table (with CustomerNumber as its primary key); this
table would have the first and last name and other information about the
customer as a person or business. The orders table should *not* contain the
customer name, only the customer number! You can use a Combo Box on the form
to select which customer (by name, or by number, whichever fits your needs
better); you could have the name included in the combo's rowsource and display
it on the form with a textbox with control source like

=comboboxname.Column(n)

where n is the zero based index of the customer name field in the combo.

To add a new customer you will really need to pop up a new form, perhaps in
the NotInList event of the combo box. Aren't you worried that if you assign
new customer numbers and add people to your local customer table that you'll
get conflicts with the customer numbers and names from the corporate database?

John W. Vinson [MVP]

 

[David K]

The "customers" are actually patients, and they get a Medical Record Number
before they get admitted to the hospital. When we get consulted to see them,
they're not new to the hospital, but they're new to us. So we use the
hospital's Medical Record Number as the patient identifier (we don't assign a
number of our own).

My partners are already scared of computers. So I'm trying to make a single
user-friendly form, on which they can enter who they saw, and what they did.
The part about what they did has to go on the "many" form. If I have to make
them fill out a name & record number on the "one" form, I risk losing their
cooperation. I think I'm trying to make a form that seamlessly moves the
name and record number over to the "one" form, while adding a row to the
"many" table. The problem is that on the first day, the patient is new.
Each day thereafter, the patient isn't new, but the services provided are.

Can you expand on 'popping up a new form' and the 'NotInList' event of the
combo box?

 

[John W. Vinson]

The "customers" are actually patients, and they get a Medical Record Number
before they get admitted to the hospital. When we get consulted to see them,
they're not new to the hospital, but they're new to us. So we use the
hospital's Medical Record Number as the patient identifier (we don't assign a
number of our own).

My partners are already scared of computers. So I'm trying to make a single
user-friendly form, on which they can enter who they saw, and what they did.
The part about what they did has to go on the "many" form. If I have to make
them fill out a name & record number on the "one" form, I risk losing their
cooperation. I think I'm trying to make a form that seamlessly moves the
name and record number over to the "one" form, while adding a row to the
"many" table. The problem is that on the first day, the patient is new.
Each day thereafter, the patient isn't new, but the services provided are.

Can you expand on 'popping up a new form' and the 'NotInList' event of the
combo box?

Ok... it may be simpler than I thought.

You could base the One form on the patients table. You can have a combo box
(use the combo wizard) to find the patient's name or patient number, whichever
is more convenient, and just pull up all the patient's information. They don't
need to FILL OUT the patient information - just drop down the list and select
the patient's name (or number) and it will display the information. This does
assume that the patient data has already been entered, which it seems is the
case.

Your Form would then have a Subform based on the Many table. You don't need to
"move" anything - it's right there! You have the patient info on the one
table, and they can enter what they did onto the subform.

John W. Vinson [MVP]

 

[aiman via AccessMonster.com]

i've encounter this problem before...
i'm new in this field, so this what i've done & so far workin fine..

I have 3 tables. Patient, Case & Surgery
Each Patient can have many Case, & each Case can have many Surgery
Each table have their own ID(primary key) then I create relationship between
them.
Then create Form for each table, from there i create subformSurgery in Case
form, then i put the Case form as subform in Patient form (they called it
nesting) you can do this by using wizard. So you get 1 form with 3 form
inside.
since I try not to create multiple entry for same Patient, but just want to
add Case for them, i create a parameter query for Patient i called it
qrysearchpatient & make a form for it. i add command button to open form by
using wizard in the qrysearchpatient form to find specific data.
when i want to add Patient, i just open the the query form & enter the
patient ID, if the pt ID is inside the database, it will show in the form &
from there you can open the form for the specific Patient & just add the Case
& Surgery as needed..

i hope this helps...
pls tell me if i'm wrong guys..