Subform is creating extra data

[Trent]

I am in the design phase of a new database and am having a devil of a
time with a subform.

I have three tables that relate to problem - Suppliers tbl, Customers
tbl and a SuppliersCustomers tbl. The intent is to have the user enter
in spend information by customer with each supplier. Sounds simple
enough right?

The common fields in each table are as follows:

Supplier tbl - suppliername, suppliernumber
Customers tbl - customername, customernumber
SuppliersCustomers tbl - suppliername, customername, month, year,
Spend$, SpendUnits

I have 45 suppliers listed in the Suppliers tbl.

My main form has 45 records one for each supplier. It contains the
suppler name and additional information from the suppliers tbl.

Subform1 is based on a query of the SuppliersCustomers tbl. It already
has the SupplierName field populated from the main form(it is hidden to
avoid displaying redundant information). It also has Customername
which is a combobox lookup in the supplier tbl. The form user selects
a customername from the combobox dropdown.

Subform2 is also based on the same query as Subform1. It already has
the SupplierName and Customer Name fields auto populate based on the
supplier of record, and the customer that was selected in Subform1. It
also contains fields for month and year that appear as "Current"
default values. Spend$ and Spend Units are entered by the user.

So, the user is really only entering 3 pieces of information...

1. Selecting Customer from combobox lookup of Customers tbl
2. Entering Spend$
3. Entering SepndUnits

What is my problem you ask???

Well, for each line of data that is entered, I am getting two records
for each entry in my SuppliersCustomers tbl - one with the data user
entered, one with $0.00 spend and $0.00 units.

e.g. If I select customer Joe Smith from the customer tbl in subform1
and enter $1000 in spend and 100 units, I will get two records in
SuppliersCustomers tbl one for the actual data and one for Joe Smith
with $0.00 spend and $0.00 units.

Can anyone help me please? Why am I getting the double entries?

Is my form design bad?

What can I do to eliminate double entry?

I have spend about 10 hours trying to correct this already. Any help
anyone can provide would be GREAT!!!!!

[MGFoster]

Trent wrote:

I am in the design phase of a new database and am having a devil of a
time with a subform.

I have three tables that relate to problem - Suppliers tbl, Customers
tbl and a SuppliersCustomers tbl. The intent is to have the user enter
in spend information by customer with each supplier. Sounds simple
enough right?

The common fields in each table are as follows:

Supplier tbl - suppliername, suppliernumber
Customers tbl - customername, customernumber
SuppliersCustomers tbl - suppliername, customername, month, year,
Spend$, SpendUnits

I have 45 suppliers listed in the Suppliers tbl.

My main form has 45 records one for each supplier. It contains the
suppler name and additional information from the suppliers tbl.

Subform1 is based on a query of the SuppliersCustomers tbl. It already
has the SupplierName field populated from the main form(it is hidden to
avoid displaying redundant information). It also has Customername
which is a combobox lookup in the supplier tbl. The form user selects
a customername from the combobox dropdown.

Subform2 is also based on the same query as Subform1. It already has
the SupplierName and Customer Name fields auto populate based on the
supplier of record, and the customer that was selected in Subform1. It
also contains fields for month and year that appear as "Current"
default values. Spend$ and Spend Units are entered by the user.

So, the user is really only entering 3 pieces of information...

1. Selecting Customer from combobox lookup of Customers tbl
2. Entering Spend$
3. Entering SepndUnits

What is my problem you ask???

Well, for each line of data that is entered, I am getting two records
for each entry in my SuppliersCustomers tbl - one with the data user
entered, one with $0.00 spend and $0.00 units.

e.g. If I select customer Joe Smith from the customer tbl in subform1
and enter $1000 in spend and 100 units, I will get two records in
SuppliersCustomers tbl one for the actual data and one for Joe Smith
with $0.00 spend and $0.00 units.

Can anyone help me please? Why am I getting the double entries?

Is my form design bad?

What can I do to eliminate double entry?

I have spend about 10 hours trying to correct this already. Any help
anyone can provide would be GREAT!!!!!

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

First, it is a good idea to change the SuppliersCustomers table to use
the CustomerNumber & SupplierNumber instead of the "Names" columns.
Numbers are smaller in the DB & faster in searches.

I would have set up the form like this:

+------------------------------------------
| Suppliers
| +--------------------------------------
| | SuppliersCustomers subform
| |
| | Customer ComboBox
| | < other fields >

Set the RecordSource for the Suppliers to the Suppliers table. Set the
RecordSource for the SuppliersCustomers subform to the
SuppliersCustomers table.

Set the SuppliersCustomers subform Master/Child Link Fields to
SupplierNumber in the Suppliers form. Set the Customer ComboBox
RowSource property to a query that will pull the CustomerNumber and
CustomerName:

SELECT CustomerNumber, CustomerName FROM Customers ORDER BY 2

Bind the Customer ComboBox to the CustomerNumber column in the
SuppliersCustomers table. The Customer ComboBox is now a data-entry
control, not a record search control.

Train your users to search for the Customer by using the Find (Ctrl-F)
or the Filter (right click: select one of the Filter options) instead of
using a selected item from the Customer ComboBox drop-down list as the
search criteria.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQizatoechKqOuFEgEQKkewCgpEuOD7UDrB0G34/h666fjc0vmpMAnjM1
hBUi1Jaa7cjjh8+L72wpMHaE
=J4Ae
-----END PGP SIGNATURE-----