Subform-Query Problems

I have read thru many other threads to try and find a solution to my problem
but to no avail. My issue: I have two tables that I am trying to link so that
my 2d table data can be used as a subform on the main form for which all the
data on the 1st table is stored. What I am trying to do with the subform is
to have a datasheet that shows the date of visit..type of 8823 issued..etc
but to correspond to the PROJECT for which the visit is for in the Main Form.
I get the message below.. I have tried to change my query and I have no idea
what I am doing wrong. I have Project Name as my Primary Key in both tables.
I do not have any other unique field. Also, I cannot enter data into the
datasheet or the query, only the table. Please help if you can. I even
thought about just putting all the info in one table but I read where some
say that's not a good idea..

ERROR MESSAGE: "The changes you have requested to the table were not
successful because they would create duplicate values in the index, primary
key, or relationship. Change the data in the field or fields that contain
duplicate data, remove the index, or redefine the index to permit duplicate
entriesand try again."

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201003/1

0
Konchetta
3/4/2010 2:47:59 PM
access.queries 6343 articles. 1 followers. Follow

6 Replies
871 Views

Similar Articles

[PageSpeed] 32

Konchetta wrote:
>I have read thru many other threads to try and find a solution to my problem
>but to no avail. My issue: I have two tables that I am trying to link so that
>my 2d table data can be used as a subform on the main form for which all the
>data on the 1st table is stored. What I am trying to do with the subform is
>to have a datasheet that shows the date of visit..type of 8823 issued..etc
>but to correspond to the PROJECT for which the visit is for in the Main Form.
>I get the message below.. I have tried to change my query and I have no idea
>what I am doing wrong. I have Project Name as my Primary Key in both tables.
>I do not have any other unique field. Also, I cannot enter data into the
>datasheet or the query, only the table. Please help if you can. I even
>thought about just putting all the info in one table but I read where some
>say that's not a good idea..
>
>ERROR MESSAGE: "The changes you have requested to the table were not
>successful because they would create duplicate values in the index, primary
>key, or relationship. Change the data in the field or fields that contain
>duplicate data, remove the index, or redefine the index to permit duplicate
>entriesand try again."

I suggest you read a little on data normalization. UNderstand the "things"
you are working with.
Write down a description of each of the things and each of the
properties/attributes. You will understand the data much more than you think.

Get a handle on the business rules-- what happens to create a project,
conduct a visit-- what has to be recorded- what reports/questions will people
need- who are the people....

Also, don't be too concerned with the Form subForm aspect. You have a main
thing -seems to be Project that will ahve attributes of its own. Also you
seem to have a Visit "thing" that will be related to a Project; will occur on
specific Dates; probably have some sort of Purpose; and will likely involve
People.

Get your Tables organized and structured before you start dealing with Form.

There are free data models that may be useful to you available here
http://www.databaseanswers.org/data_models/project_planning/index.htm

Take a look at the Business rules

-- 
Message posted via http://www.accessmonster.com

0
orange
3/4/2010 3:53:40 PM
Thanks Orange!! Will do that.

orange wrote:
>>I have read thru many other threads to try and find a solution to my problem
>>but to no avail. My issue: I have two tables that I am trying to link so that
>[quoted text clipped - 14 lines]
>>duplicate data, remove the index, or redefine the index to permit duplicate
>>entriesand try again."
>
>I suggest you read a little on data normalization. UNderstand the "things"
>you are working with.
>Write down a description of each of the things and each of the
>properties/attributes. You will understand the data much more than you think.
>
>Get a handle on the business rules-- what happens to create a project,
>conduct a visit-- what has to be recorded- what reports/questions will people
>need- who are the people....
>
>Also, don't be too concerned with the Form subForm aspect. You have a main
>thing -seems to be Project that will ahve attributes of its own. Also you
>seem to have a Visit "thing" that will be related to a Project; will occur on
>specific Dates; probably have some sort of Purpose; and will likely involve
>People.
>
>Get your Tables organized and structured before you start dealing with Form.
>
>There are free data models that may be useful to you available here
>http://www.databaseanswers.org/data_models/project_planning/index.htm
>
>Take a look at the Business rules

-- 
Message posted via http://www.accessmonster.com

0
Konchetta
3/4/2010 4:07:57 PM
>> I have Project Name as my Primary Key in both tables.
You can not have Project as primary key in the project visit table as it 
would only allow ONE visit.  Use Project as a foreign key.

Join the two table in a one-to-many relationship and set the Master/Child 
links for the form/subform using the Project fields.

-- 
Build a little, test a little.


"Konchetta via AccessMonster.com" wrote:

> I have read thru many other threads to try and find a solution to my problem
> but to no avail. My issue: I have two tables that I am trying to link so that
> my 2d table data can be used as a subform on the main form for which all the
> data on the 1st table is stored. What I am trying to do with the subform is
> to have a datasheet that shows the date of visit..type of 8823 issued..etc
> but to correspond to the PROJECT for which the visit is for in the Main Form.
> I get the message below.. I have tried to change my query and I have no idea
> what I am doing wrong. I have Project Name as my Primary Key in both tables.
> I do not have any other unique field. Also, I cannot enter data into the
> datasheet or the query, only the table. Please help if you can. I even
> thought about just putting all the info in one table but I read where some
> say that's not a good idea..
> 
> ERROR MESSAGE: "The changes you have requested to the table were not
> successful because they would create duplicate values in the index, primary
> key, or relationship. Change the data in the field or fields that contain
> duplicate data, remove the index, or redefine the index to permit duplicate
> entriesand try again."
> 
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201003/1
> 
> .
> 
0
Utf
3/4/2010 4:20:01 PM
I would suggest two things. One, if you try to change your PK, it gives you 
that error. OR if you have duplicates in your primary key it gives you that 
error. Two, I think having project name as the PK may cause some problems 
because each project names will have to be an exact match. If someone tries 
to change Project for This to Project for That, you get an error. A better 
option may be to use an autonumber field for the PK.
-- 
Milton Purdy
ACCESS 
State of Arkansas


"Konchetta via AccessMonster.com" wrote:

> I have read thru many other threads to try and find a solution to my problem
> but to no avail. My issue: I have two tables that I am trying to link so that
> my 2d table data can be used as a subform on the main form for which all the
> data on the 1st table is stored. What I am trying to do with the subform is
> to have a datasheet that shows the date of visit..type of 8823 issued..etc
> but to correspond to the PROJECT for which the visit is for in the Main Form.
> I get the message below.. I have tried to change my query and I have no idea
> what I am doing wrong. I have Project Name as my Primary Key in both tables.
> I do not have any other unique field. Also, I cannot enter data into the
> datasheet or the query, only the table. Please help if you can. I even
> thought about just putting all the info in one table but I read where some
> say that's not a good idea..
> 
> ERROR MESSAGE: "The changes you have requested to the table were not
> successful because they would create duplicate values in the index, primary
> key, or relationship. Change the data in the field or fields that contain
> duplicate data, remove the index, or redefine the index to permit duplicate
> entriesand try again."
> 
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201003/1
> 
> .
> 
0
Utf
3/4/2010 4:22:09 PM
Thanks Karl, I will try that as well. I kept changing Project from my Primary
Key because I felt the problem lied somewhere in me making that my primary
key but nothingelse ever worked. So I will give this a try.

KARL DEWEY wrote:
>>> I have Project Name as my Primary Key in both tables.
>You can not have Project as primary key in the project visit table as it 
>would only allow ONE visit.  Use Project as a foreign key.
>
>Join the two table in a one-to-many relationship and set the Master/Child 
>links for the form/subform using the Project fields.
>
>> I have read thru many other threads to try and find a solution to my problem
>> but to no avail. My issue: I have two tables that I am trying to link so that
>[quoted text clipped - 14 lines]
>> duplicate data, remove the index, or redefine the index to permit duplicate
>> entriesand try again."

-- 
Message posted via http://www.accessmonster.com

0
Konchetta
3/4/2010 4:33:09 PM
Thank golfinray, I tried making my PK an autonumber too but that didn't work
but I did still have my Project Name in both tables. So I will give all this
some careful thinking over to apply to make sure I don't waste any more time.
Thanks a bunch!!


golfinray wrote:
>I would suggest two things. One, if you try to change your PK, it gives you 
>that error. OR if you have duplicates in your primary key it gives you that 
>error. Two, I think having project name as the PK may cause some problems 
>because each project names will have to be an exact match. If someone tries 
>to change Project for This to Project for That, you get an error. A better 
>option may be to use an autonumber field for the PK.
>> I have read thru many other threads to try and find a solution to my problem
>> but to no avail. My issue: I have two tables that I am trying to link so that
>[quoted text clipped - 14 lines]
>> duplicate data, remove the index, or redefine the index to permit duplicate
>> entriesand try again."

-- 
Message posted via http://www.accessmonster.com

0
Konchetta
3/4/2010 4:36:19 PM
Reply:

Similar Artilces:

LoadImage and Shell_NotifyIcon problem...
Hi all. I�m trying to load icon from file using LoadImage and add that icon to system tray. However, seems like the LoadImage doesn�t work properly because Shell_NotifyIcon doesn�t work with that icon. I tried with application icon (AfxGetApp()->LoadIcon(IDR_MAINFRAME) and everything works normally. I used the following code to load image: return (HICON)::LoadImage(NULL, lpszFileName, IMAGE_ICON, 16, 16, LR_LOADFROMFILE | LR_DEFAULTCOLOR); I tested that code in test application and icon is displayed properly. Icon on the disk has 32bit colors and size is 16x16, if it matters at all....

Duplicate the record in form and subform
Hi Allen Browne, I try to use your code to duplicate the record in form and subform but I got a error message "Error#3265". BTW, my primary key value is not autonumber, how to change the code to fit my database? Thanks for your expert comments in advance! Presumably we are talking about this article: http://allenbrowne.com/ser-57.html The "Item not found" error was caused by which line? Whichever line it is, you have some name Access can't identify, e.g. you have not changed the field names to match what's in your form. -- Allen Browne - Microsoft MVP...

Sophos 'Mailmonitor for Exchange'
I'm trying to find out if any other suppliers/users of Sophos anti-virus are having problems with Mailmonitor for Exchange. Before getting into the detail, let me just say that Sophos were first advised of this particular problem on 25th November 2003 (case id AVK-25-FGT for those that are interested). They have previously been made aware of everything in this posting. As a long time advocate of Sophos and MD of a 'Sophos Certified Partner', their help in resolving this particular issue has left me completely under-whelmed and we are now evaluating alternative products for the c...

Query Based Distribution Lists #3
I can create QBDL based on STATE, ZIP, LASTNAME and many other fields. I would like to make a list based on Group Membership. There is a field under USER called MEMBER OF but it does not give me any results when I do a preview. I have many employees who work in multiple locations and need to belong to several different groups even though they have only one login and work out of a single office with the same address etc. So I made them members of groups based on the different location in which they work and wanted to send mail based on this membership. McMurray <McMurray@disc...

problem with a conditional max problem
-- What formula could you enter in cell E13 so that if a value is entered in cell E12, cell E13 will display the value of f(x) that corresponds to the largest tabulated x which is less than or equal to the value entered. For example, if 3 is entered in cell E12, then E13 should display the value of f(0). Brian If your tabulated x's are in a range named "tabx", one way: =f(LOOKUP(E12,tabx)) where f() is your function. In article <8D7078DA-197C-4F2B-B7D0-129DEDC8EC8D@microsoft.com>, "Brian Cornejo" <BrianCornejo@discussions.microsoft.com> wrot...

running total #N/A problem
hi, i am trying to make a stock register. I have the following row fields: Product, transaction_date and following data fields: receipt, issue, balance (balance is a calculated field whose formula is receipt - issue and shown as running total with base of transaction_date) it is running perfectly allright till i add one more row field called transaction_no after transaction_date. the running total column gives me a #N/A. I am trying to make a stock register that shows the product, dates and transaction no's of each product and five a running total at the end of every transaction. i...

Macro Problems
Hello everyone. I am using Excel 2000, and I am having a macro problem. On sheet 1 I have a dropdown that I use data validation with and a macro. This list sorts alphabetically, and I can type new names in when I need to. It works fine, but I need to have two to three more dropdowns on the same sheet. I tried to modify the macro, but it will not work properly. I created a new list "VendorList" and modified the macro.(I think that is where I messed up) here is a copy of the modified macro: Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next Dim ws As Workshee...

My problem about rendering behavior
==== What I want to do: ==== I am hosting a "Microsoft Web Browser" control in my MFC program. I want to draw a red rectangle encompassing one or a group of html elements. Since the rectangle may encompass more than one elements, it seems to me that it is impossible to do it by changing element styles using IHTMLStyle interface. ==== The Method I am using now ==== Currently I am using "rendering behavior" method described in this topic: http://groups.google.com/group/microsoft.public.inetsdk.programming.d... which offers some sample codes as follows: &...

Problem with XP
HI ALL, Last time, i have to face a very strange runtime error. I have developed an application using VC6 and windows 2000 server. When I run this application on XP, and when drag a DIALOG and is moved off the right corner of application, a runtime error occur and application closes Regards, Ghazanfar Ali Ghazanfar Ali wrote: > HI ALL, > Last time, i have to face a very strange runtime error. I have developed > an application using VC6 and windows 2000 server. When I run this > application on XP, and when drag a DIALOG and is moved off the right corner > of applic...

CRITICAL role permissions problem
I have created a user role which gives 'none selected' for write access to Accounts and Contacts. The role also has 'none selected' for delete rights for accounts and contacts. The role has create, read, append, append to, assign, and share rights for Account and Contact. The client requires their sales reps to be able to create Accounts and Contacts and save them, but not be able to edit them after they are saved. The problem is when a Lead is converted, and the options for creating Account, Contact, and Opportunity are all selected, the user get a permissions error. ...

Problems with merged cells
I have merged a fair few cells so that I can enter paragraphs, once I type past a certain point it wont bring up everything I have entered in the cell, it cuts half of it off & it wont print it either, but when i click on the cell all the information is there......can anyone help me out with this??? Excel has limitations (see link below). Cells will show up to 1024 characters, but up to 32,767 will appear in the formula bar. Printing.. maybe it has a 1024 character limit too. (Just because you're using merged cells doesn't mean you get more characters - they're ...

Need help with the query.
How can I get desire results below. I cannot seem to figure this out. Any help is greatly appreciate. IF OBJECT_ID('Tempdb.dbo.#Temp', 'u') IS NOT NULL DROP TABLE #Temp GO CREATE TABLE #Temp ( UserHistoryId INT NULL, UserId VARCHAR(6) NULL, EventDate DATETIME NULL ) GO INSERT INTO dbo.#Temp([UserHistoryID],[UserId],[EventDate])VALUES (664, 'User1', CONVERT(DATETIME, 0x00008eac00000000)) INSERT INTO dbo.#Temp([UserHistoryID],[UserId],[EventDate])VALUES (1014, 'User1', CONVERT(DATETIME, 0x00009c2600ef152a)...

Problem with links
I'm having a problem with a link. I have a work book called 'Graveyard Shift' and a sheet within that workbook called 'Graveayd Charts'. I also have a 'YTD' workbook. I have the charts linked from 'Graveyard Charts' to 'YTD' I am getting an error that says Invalid Refernce. I checked it , it is right. When I have 'Graveyard Charts' open then everything updates perfectly. Any help? Peter - If the links use some kind of dynamic function (INDIRECT, OFFSET), the source workbook has to be open. Links to closed workbooks only work with no...

Charting Problems #2
I need to create a chart that has axes in "exact" inches. For example, the printed plot area is 6" x 3", or at least 1 axis is exactly inches. ...

CPropertySheet/Page problem on Win98 2e
Does anybody know of any problem using tabbed dlg implemented with CPropertySheet/Page on Win98 2e with both ie6 & mfc*.dlls uptodate? I'm building with VS6SP5. Thanks No. I have written substantial apps using Property Sheet / page and never had any issue with any DLL versions on any of the Win9x series of OS or any other windows version post Win95a for that matter. What is happening? - Tim "Vincent Yu" <VincentYu@discussions.microsoft.com> wrote in message news:CC63A0C8-8DB6-47F6-83A0-E674436441CD@microsoft.com... > Does anybody know of any problem using ...

Problem getting values in iframe
Hi, I have placed an iframe to the account form. This iframe calls an .aspx page that is also placed on the same server as the CRM. I am trying to get an value from the CRM form to the .aspx page in the iFrame. my function that I call upon "onLoad": <script language="javascript"> function GetSomeValues() { document.getElementById('TextBox1').value = parent.document.forms[0].all.name.DataValue; } </script> This fails! I get the message: "Access denied!" Is there any setting in the CRM I have forgotten to turn on or off?? Can a...

Problem with resources on W98
My application, which seems to work without problem on XP, has a problem on W98 with resource : when working on W98 french version after a while the application display a dialog box with the french message "Une ressource requise était non"(in english "A required resource was not") After that message the application seems to lack resources to work correctly. What should I check on my code ? -- Joel Windows 98 does not manage system resources as well as XP, so if you create a bitmap for example, but do not free it, Windows 98 will eventually break. 2000/XP handle syst...

Exchange contact sync problems.
I have a problem with contact synconizations under Ex2003. We bought a (3rd party) modul for sync'ing contact between Axapta and Exchange. Only problem is, the address data is synced wrong and the company that sold us the modul, claims that the problem lies in our Exchange installation sins the module runs fine on other installations they've done. Fx: converting from Ax to Ex Zipcode is converted into the "provins" field cityname is converted into both city and zipcode field The other way zip -> cityname cityname just disapear The Ex2003 installation is a migration off...

PostNcDestroy()..unknown crash problem
Hi, I have a modeless dialog that has a close button. In a localization version, it crashes when user clicks the "x" button on the top rightmost corner. But the close button works just fine. Both events lead to my PostNcDestroy() handler. I really cannot see any difference between these events, namely the "x" button and my own close button. Is there any significant difference between these two in the MFC framework? Any hint or help is much appreciated. (Since it doesn't crash in the English version, I cannot debug it.) Tony This is a typical "my pro...

Publisher 2000 print problem
What we see in the working document and what goes to print is different. Is there a way to enter the print preveiw document to fix it at that point. There seems to be no way to resolve this issue??? Please help if you can. ...

Colour Problem in Excel 2002
Hi, I'm having a really annoying problem. When I create autoshapes in Excel 2002 (XP), when I scroll down the page, and then back up, the colour is distorted and ruined to look at. It prints fine, but it is very annoying, and bad to look at. It's like the colour just doesn't stay, lines and bits of colour vanish every time this happens. Has anyone had this problem before? I've never seen this. But Excel and other windows programs use the printer driver to know how to display stuff. You may want to try a different printer (or printer driver) to see what happens. Maybe even...

problems installing Outlook Desktop client
Problem trying to install Microsoft CRM 3 Outlook desktop client on a PC running windows XP service Pack 2 and Outlook 2003 service Pack 2 connected to exchange. Originally downloaded ISO installation file from Microsoft downloads, and burnt CD. Installation failed, it seems the installation CD was corrupt. We then found our original installation CD, which when we installed it, it came up with two messages: - Microsoft Outlook was not initialized. Please run Outlook and configure your mail account. ---- Outlook is installed and running, there's no problem with this so I do not unde...

Money 2006 problem
I just installed the money 2006 trial version but when I try to run Money it just opens briefly and then immediately closes. Money 2003 was doing the same thing but I thought maybe 2006 would fix the problem. Can anyone help me please? Instead of opening Money, navigate to the folder where you have your .mny file using My Computer or Windows Explorer and double click on the sample.mny file. That will tell us if your My Money.mny file is faulty or the M2006 installation. -- Regards Bob Peel, Microsoft MVP - Money For UK tips & fixes see http://support.microsoft.com/default.aspx?sc...

Combo Box Problems
I would like to make a combo box in my worksheet do exactly what the Name Box does. When a item in the combo box is selected scroll right or left through the worksheet and find that heading that was selected in the combo box?.....Is there a way to do this? Thanks. On Aug 13, 10:00=A0am, daphoenix <daphoe...@discussions.microsoft.com> wrote: > I would like to make a combo box in my worksheet do exactly what the Name= Box > does. When a item in the combo box is selected scroll right or left throu= gh > the worksheet and find that heading that was selected in the combo > bo...

Problems switching to new hardware
after removing active directory from the machine, I tried to activate it as a 2nd machine for the new Dell machine. No luck, it still could not locate the machine. After tinkering around I finally gave up and reinstalled the OS on the new machine from scratch. I recreated the user list and also installed the email server. We're up and running again. I only have one problem left. Our old domain was server1.xxx.org. The new domain I created was just xxx.org. So now anytime we try to access the external web site from our provider called xxx.org I don;t get it. I've tried to use the DNS ma...