473,691 Members | 2,860 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Insert new data in subform

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 queryProductsBy Customer

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
3 4499
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
tblPurchaseDeta ils: DetailID, PurchID, ProductID, Qty, Price
tblProducts: ProductID, ProductName, RetailPrice

*Relationships*
tblCustomers 1 - M tblPurchases using CustID
tblPurchases 1 - M tblPurchaseDeta ils using PurchID
tblProducts 1 - M tblPurchaseDeta ils 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.adelaid e.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 queryProductsBy Customer

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
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 qryActiveProduc ts. Any idea how to change the Record source
of the combobox in an event? I tried to add an enter event

combobox.RowSou rce = qryActiveProduc ts
combobox.Requer y

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

Regards,
Nicolae

"Stewart Allen" <sa****@NOT.wav e.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
tblPurchaseDeta ils: DetailID, PurchID, ProductID, Qty, Price
tblProducts: ProductID, ProductName, RetailPrice

*Relationships*
tblCustomers 1 - M tblPurchases using CustID
tblPurchases 1 - M tblPurchaseDeta ils using PurchID
tblProducts 1 - M tblPurchaseDeta ils 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.adelaid e.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 queryProductsBy Customer

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
Meantime I discovered my problem.

I should have used:

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

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

In the LostFocus event I revert to the the other query

Regards,
Nicolae
Nov 13 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
4187
by: Tavish Muldoon | last post by:
What a pain trying to insert data into a table from a stored proc. My webform asks for 16 pieces of data - which then gets written to the database. I found this easier than the crap below (after the ***********). This was eaiser: get a list of all the parameters you want to pass - put them in a string:
2
2866
by: Santo Santis | last post by:
How can I make an .exe file that can insert data automatically in a SQLServer database, I can do it in C, but how can I connect to SQLServer and execute a query. All data that I have to insert are data that I can have from PC environment variables. Thanks
0
3984
by: Dinu | last post by:
Hi All I am trying to insert data within a transaction, into a Db2 database from asp.net. I have created a System DSN using ISeries Access Driver(32 bit) for Windows. I am then connecting to it using Microsoft .Net data provider from asp.net. I am able to insert data by using connection and comand object without
9
6583
by: Hi5 | last post by:
Hi, Any Idea how, I can make an Insert statement to insert data into 6 different tables, that are all holding all data of my database? Is there any example? I would be grateful if you could let me know of the ways to view data in a query and then add data to database
0
10481
by: Mamatha | last post by:
Hi When i clicked a button, i want to insert data from listview in VB.NET to Excel sheet. If you know the solution either above or below is ok for me. I know how to insert from a textfile,but all data of textfile inserted into only into a single cell.I want to insert single line of text file for each cell. I tried to insert data into Excel sheet in many ways but
1
1839
by: reagen | last post by:
dear all, please help me that my script cannot insert data to ms acces. <%@ Page Language="vb" ContentType="text/html"%> <%@ Import NameSpace = "System.Data" %> <%@ Import NameSpace = "System.Data.OleDb" %> <script runat="server"> Sub Insert_Btn (s As Object, e As EventArgs)
0
2197
by: danishce | last post by:
Hello, I want to insert data directly into my windows form data grid and load a combobox(userid) in the 1st column of data grid,a textbox(password) in 2nd column of datagrid. The code for insert data is: dim cmd as new oledbcommand("insert into table(userid,password) values('" & cmbcol1.text & "','" & txtcol2.text & "')",connection) dim da as new oledbdataadapter dim ds as new dataset da.selectcommand=cmd da.fill(ds) messagebox.show...
1
2180
by: Doc11 | last post by:
I'm trying to allow users insert data into a database using the form view. But when I click the insert button I get this error: Server Error in '/Customer Database' Application. -------------------------------------------------------------------------------- Incorrect syntax near 'nvarchar'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information...
0
4056
by: troydixon | last post by:
Hello, I am new at this, and have been trying to insert data into a table by using the footer of a gridview (which I dont like) or by using a detials view on the same page that is doing the following: 1) gets query string from url and filters records (works great) via a Details View 2) shows notes that have been added to the record shown in the details view using a grid view, and it filters the data by looking at the ID that is selected in...
1
2034
by: ghjk | last post by:
I want to insert data through my web application. I have dropdown box (have 2 values Yes and No)and when i select yes insert record will be 5 and when i select no insert record will be 3. So i cant insert data using same query in php. How can i insert data depend on user selected value?
0
8599
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
8531
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
1
8791
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
5813
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4322
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4550
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2965
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2227
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
1952
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.