By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,679 Members | 2,036 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,679 IT Pros & Developers. It's quick & easy.

Insert new data in subform

P: n/a
Hi All,

I have a problem and I can't figure out how to solve it.
My database has three tables:

tblCustomers, with CustomerId and CustomerName
tblProducts, with ProductId and ProductCode
tblPurchases, with PurchaseId, CustomerId and ProductId

I created a form with a subform which shows all the customers, one at a time
and in the subform all the products they purchased.
(the subform contains only a combobox with the ProductCode). In the dropdown
of the combobox I have a list with all the products from tblProducts.

The form data property is set to tblCustomers. The subform data property is
set to a queryProductsByCustomer

Now, is it posssible to add a new purchase in the subform? When I add a new
product in the subform, I get this error: The changes you requested to the
table were not successful because they would create duplicate values in the
index, primary key or relationship

From what I figured it out so far, because the combobox shows the value
ProductCode from the tblProducts, the subform tries to insert my purchase in
the tblProducts instead of tblPurchases. How can I make the form and subform
functional? I suppose I have to change the query, or to add a new query, etc

Any help greatly appreciated

PS

I tried to simplify even further and instead of the combobox in the subform,
I use only a textbox with the productId, where I try to add a new product.
If I link the subform to the tblPurchases, I can add a new ProductId to the
tblPurchases for the selected Customer...
It would be nice to be able to use the ComboBox, to view the ProductName and
to select from dropdown by ProductName as well

Regards,
Nicolae

Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Hi Nicolae,

The subform's data source should be set to tblPurchases but I can see a
major design flaw here.
Here's how you should be thinking about when designing the table structures.
One customer can make many purchases.
Each purchase can contain many products.
Each product can be purchased many times from different customers.
This means that one customer can purchase many products and each product can
be purchased by many customers so a many to many relationship exists between
customers and products.

*Tables*
tblCustomers: CustId, CustName, etc
tblPurchases: PurchID, CustID, PurchDate
tblPurchaseDetails: DetailID, PurchID, ProductID, Qty, Price
tblProducts: ProductID, ProductName, RetailPrice

*Relationships*
tblCustomers 1 - M tblPurchases using CustID
tblPurchases 1 - M tblPurchaseDetails using PurchID
tblProducts 1 - M tblPurchaseDetails using ProductID

Have a look at the table structure of the Northwind database that came with
access. The OrderDetails table there uses the OrderID and the ProductID
combined as a primary key where my example uses a DetailID for the primary
key.

With your example, how do you know when your customer purchased those
products? How do you know what they purchased for their first order, second
order etc.

Hope this helps
Stewart
"Nicolae Fieraru" <no****@please.cxm> wrote in message
news:40********@duster.adelaide.on.net...
Hi All,

I have a problem and I can't figure out how to solve it.
My database has three tables:

tblCustomers, with CustomerId and CustomerName
tblProducts, with ProductId and ProductCode
tblPurchases, with PurchaseId, CustomerId and ProductId

I created a form with a subform which shows all the customers, one at a time and in the subform all the products they purchased.
(the subform contains only a combobox with the ProductCode). In the dropdown of the combobox I have a list with all the products from tblProducts.

The form data property is set to tblCustomers. The subform data property is set to a queryProductsByCustomer

Now, is it posssible to add a new purchase in the subform? When I add a new product in the subform, I get this error: The changes you requested to the
table were not successful because they would create duplicate values in the index, primary key or relationship

From what I figured it out so far, because the combobox shows the value
ProductCode from the tblProducts, the subform tries to insert my purchase in the tblProducts instead of tblPurchases. How can I make the form and subform functional? I suppose I have to change the query, or to add a new query, etc
Any help greatly appreciated

PS

I tried to simplify even further and instead of the combobox in the subform, I use only a textbox with the productId, where I try to add a new product.
If I link the subform to the tblPurchases, I can add a new ProductId to the tblPurchases for the selected Customer...
It would be nice to be able to use the ComboBox, to view the ProductName and to select from dropdown by ProductName as well

Regards,
Nicolae

Nov 13 '05 #2

P: n/a
Hi Stewart,

Thank you very much for your information. I realised I have to set the
subform datasource to tblPurchases.
I will look very carefully to your explanation when I will be able to, as
the design information is essential. I have to check what I already know
from it, and what is new for me.
The example I provided is not a real database, I tried to simplify as much
as I could the real problem which I have. I got the real application
working, but now I run into another problem. The way I work is not ideal in
many ways. Between our customer and me there is another guy with more
experiece than me, and I am not allowed to talk to the customer, to find out
what they really need. So I actually have to consider my coleague as beeing
my customer and I have to accomplish what he wants.
Based from my previous example, now let's consider that some of the
products are inactive (not beeing sold anymore).
The subform works now the way it should. For every customer I can see in the
subform the list with all their purchases. I have a combo box in the detail
section which ProductName of the products. I added a checkbox which is able
to filter the products, based on their active status. If I check the box
"hide inactive products" comboboxes with inactive products become blank and
in the dropdown I have left only active products.
But actually I have to do this: when I click on checkbox to hide the
inactive products, both types of products still have to be shown in the
comboboxes. But in the dropdown list now I have to have just the active
products listed... I have no idea how to do this. I think I have to have as
a Record Source for the combobox the tblProducts, so the combobox value
remains unmodified by the combobox. And I suppose I have to add an event to
the combobox, that when I click on it, to change the Record Source from
tblProducts to qryActiveProducts. Any idea how to change the Record source
of the combobox in an event? I tried to add an enter event

combobox.RowSource = qryActiveProducts
combobox.Requery

But I can't view the records from the query in the dropdown list...

Regards,
Nicolae

"Stewart Allen" <sa****@NOT.wave.THIS.co.nz> wrote in message
news:cc**********@news.wave.co.nz...
Hi Nicolae,

The subform's data source should be set to tblPurchases but I can see a
major design flaw here.
Here's how you should be thinking about when designing the table structures. One customer can make many purchases.
Each purchase can contain many products.
Each product can be purchased many times from different customers.
This means that one customer can purchase many products and each product can be purchased by many customers so a many to many relationship exists between customers and products.

*Tables*
tblCustomers: CustId, CustName, etc
tblPurchases: PurchID, CustID, PurchDate
tblPurchaseDetails: DetailID, PurchID, ProductID, Qty, Price
tblProducts: ProductID, ProductName, RetailPrice

*Relationships*
tblCustomers 1 - M tblPurchases using CustID
tblPurchases 1 - M tblPurchaseDetails using PurchID
tblProducts 1 - M tblPurchaseDetails using ProductID

Have a look at the table structure of the Northwind database that came with access. The OrderDetails table there uses the OrderID and the ProductID
combined as a primary key where my example uses a DetailID for the primary
key.

With your example, how do you know when your customer purchased those
products? How do you know what they purchased for their first order, second order etc.

Hope this helps
Stewart
"Nicolae Fieraru" <no****@please.cxm> wrote in message
news:40********@duster.adelaide.on.net...
Hi All,

I have a problem and I can't figure out how to solve it.
My database has three tables:

tblCustomers, with CustomerId and CustomerName
tblProducts, with ProductId and ProductCode
tblPurchases, with PurchaseId, CustomerId and ProductId

I created a form with a subform which shows all the customers, one at a time
and in the subform all the products they purchased.
(the subform contains only a combobox with the ProductCode). In the

dropdown
of the combobox I have a list with all the products from tblProducts.

The form data property is set to tblCustomers. The subform data property

is
set to a queryProductsByCustomer

Now, is it posssible to add a new purchase in the subform? When I add a

new
product in the subform, I get this error: The changes you requested to the table were not successful because they would create duplicate values in

the
index, primary key or relationship

From what I figured it out so far, because the combobox shows the value
ProductCode from the tblProducts, the subform tries to insert my purchase in
the tblProducts instead of tblPurchases. How can I make the form and

subform
functional? I suppose I have to change the query, or to add a new query,

etc

Any help greatly appreciated

PS

I tried to simplify even further and instead of the combobox in the

subform,
I use only a textbox with the productId, where I try to add a new

product. If I link the subform to the tblPurchases, I can add a new ProductId to

the
tblPurchases for the selected Customer...
It would be nice to be able to use the ComboBox, to view the ProductName

and
to select from dropdown by ProductName as well

Regards,
Nicolae


Nov 13 '05 #3

P: n/a
Meantime I discovered my problem.

I should have used:

Me.ComboBox.RowSource = "QueryName" (with double quotes) in the GotFocus
event

I also find useful to change the mouse pointer with Screen.MousePointer = 1

In the LostFocus event I revert to the the other query

Regards,
Nicolae
Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.