Duplicate Values in Index, Primary Key or relationship

[access7]

Good afternoon

I would be extremely grateful if anyone gets the chance to look into the following error for me... I have just come to demo my system so far and it pops up with this... typical
Everything was working perfectly on the old version I had but in an attempt to 'get around' a problem I was having with enabling fields (boss didn't like the greyed out / fuzzy labels look) I have added additional sub forms to my main Frm_Company.
The error occurs when loosing focus on the top subform (SubFrm_Address) and entering the next sub form (SubFrm_Contacts). I have tried to add (what will be invisible) fields to check that the company ref links are working and these seem OK but I keep getting the following message;

'The changes you 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 entries and try again'

Is it something to do with the record sources for the forms? I have the main form running off a query (Qry_Company) and the others from either the same query or a different table.... is this the problem?

I am still fairly new to all this and I have to admit relationships are still something I am trying to get my head around .... could any point me in the right direction or explain where I am going wrong??

Many Thanks

 

[missinglinq]

Having Main Forms and Subforms running off of the same Query/Table is almost always a bad idea for the very reason you're listing here!

And in spite of the fact that I've read your post three or four times I cannot, for the life of me, figures out how you would 'work around' a problem with the appearance of Disabled Controls by using Subforms!

If your boss doesn't like the 'fuzzy gray' look (and I'm not fond of it either, I might add) simply use it in conjunction with the Lock Property! When you use the two Properties together the Controls are Disabled but appear normal.

To Disable Controls:

ControlName.Enabled = False
ControlName.Locked = True


To Enable Controls:

ControlName.Enabled = True
ControlName.Locked = False

Linq ;0)>

 

[access7]

Thanks for taking the time to reply to my post. Maybe I am going around things backwards, I'm afraid that comes with a huge lack of experience. I was under the impression that by using the 'lock' property rather than the enabled that it stopped my query from refreshing properly - I cant remember exactly now but I am sure I tried the lock property first and it gave me errors (which I think is why I used the enabled property instead). Perhaps using sub forms was not the best plan, I just noticed that it appeared to 'solve' my initial issue, I didn't realise it would cause another (always seems to be the case that as one thing is fixed another breaks! lol).

From a learning point of view is there anyway you could explain to me how having Main Forms and Subforms running off of the same Query/Table causes such problems?

Its looking as though I may have to revert back to the original version, I will look into the lock property again and see if I can get that working, although as I mentioned, I'm sure I had issues with it at the start.

Many Thanks for your reply, advice and experience are always very welcome, I have much to learn

Thank You for your time

 

[missinglinq]

First off, it simply makes no sense! Why would you want a Form/Subform with the same Record Source? If you have Controls that need to appear separately, for whatever reason, or too many Controls to appear on a single screen, you can add a Tabbed Control to your Form and place different Controls on different Pages. A Tabbed Control is also good for dividing Controls into logical groups for Data Entering/Presentation. An example of this would be an employee form.

• One page would hold name, DOB, phone number, etc.
• Another page could hold data about education.
• A third page could hold work history.

And so forth.

A Form/Subform from the same Record Source will mean that one of the Forms will not be Editable, since you have the same Record open twice, in different Forms.

And if you get into Editing the Records, you'll also start having Write Conflicts, always a hairball to sort out!

When you create a New Record in the Subform, the Field named in the Subform Control's LinkChildFields Property normally inherits its Value from the Field named in LinkMasterFields. Typically, Access will assign the Primary Key
field to these Properties. If the Record on the Main Form is not at a New Record, the Subform will try to use the same Primary Key Value as the Record in the Main Form, and since they are both using the Same Table, you'll get the

The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship


Error Message that necessitated this thread in the first place!

Having a Control Locked or Disabled, or Locked and Disabled, only effects the user's ability to Physically Enter/Edit Data in the Control. It doesn't prevent Access from any type of Data Manipulation, such as Requerying or Refreshing, done thru Code or thru Menu Items, and it doesn't prevent Access from Entering/Populating Data into the Control.

Trying to set Focus to a Control that is Disabled will pop an Error, but off hand I can't think of any Error that would be caused by a Control being Locked.

And like I said, if the driving force here is not having the Disabled Controls looking funky and gray, using

ControlName.Enabled = False
ControlName.Locked = True

will resolve that problem.

Good luck with your project!

Linq ;0)>

 

[access7]

Thank you so much for taking the time to explain that, I understand now why I was getting that message. I am going to change everything over today to lock property and get rid of the unnecessary sub forms - it will mean re-designing a couple of forms and changing some code but all good practice and a very good learning curve for how NOT to design them in the first place
Thanks again

 

[access7]

Hello, sorry, one more quick question... I have now got rid of the sub forms and have put the fields onto the tab control on the main form (as originally designed). I am now trying the locked property out to use (to combat the 'fuzzy look') - I have changed my code, but am not sure I've done it correctly as I am now getting an error when running code... I have highlighted the bits I think are relevant but have put the code for the whole form below (in case I have missed something out)... I have basically changed the word enabled to locked
Dim var
'traverse the array
For Each var In Me.MyControls
'var = objecttype, MyContols = collectionName
'set the enabled property of each control
var.Enabled = Enabled
Next

I have a variable called 'ControlEnabler' which is what drives the properties when the form opens...
I am getting the following error when going through the array... 'run time error '438', Object does not support this property or method'

Option Compare Database
Option Explicit

Property Get MyControls() As Variant
'exposes an array of controls whose enabled status we commonly set
MyControls = Array(Me.txtCompany, Me.txtFees, Me.Contact_Details, Me.Additional_Info, Me.Contact_History, Me.Document_Checklist, Me.Amendment_History)
End Property

Public Sub GoToContact(ContactID As Long)
'navigate to the correct company in this form
Me.GoToID DLookup("CompanyRef", "tbl_ContactDetails", "ContactRef = " & ContactID)
'run the Public GoToID method of the subform
Me.SubFrm_Contacts.Form.GoToID ContactID
End Sub

Public Sub GoToID(CompanyID As Long)
Me.Filter = ""
Me.FilterOn = False
With Me.RecordsetClone
.FindFirst "CompanyRef = " & CompanyID
If Not .NoMatch Then Me.Bookmark = .Bookmark
End With
End Sub

Private Sub CmdAction_Click()
DoCmd.OpenForm "Frm_Action", , , , , , Me.txtCompanyID & ";" & Me.SubFrm_Contacts!txtContactRef
End Sub

Private Sub CmdInd_Click()
If Me.SubFrm_Industry.Visible Then
Me.SubFrm_Industry.Visible = False
Else
Me.SubFrm_Industry.Visible = True
End If
End Sub

Private Sub CmdFees_Click()

Dim FeeDocPath As String
FeeDocPath = "C:\Users\Natalie\Documents\FeesTest.xlsx"
Dim XL As Excel.Application
Dim XLBook As Excel.Workbook
Dim XLSheet As Excel.Worksheet

Set XL = CreateObject("Excel.Application")
Set XLBook = GetObject(FeeDocPath)

XL.Visible = True
XLBook.Windows(1).Visible = True

Set XLSheet = XLBook.Worksheets(1)

End Sub

Private Sub Form_BeforeInsert(Cancel As Integer)
'sets the user name for the form
Me.CreatedBy = gsUserName
Me.CreatedWhen = Now()
End Sub

Private Sub CmdCancel_Click()
MsgBox "Are you sure you want to cancel any changes?", vbYesNo, "Cancel?"
If vbYes Then
Me.Undo
Else
DoCmd.CancelEvent
End If
ControlEnabler False
Me.CmdFinish.SetFocus
Me.CmdCancel.Visible = False
Me.CmdEdit.Visible = True
End Sub

Private Sub CmdClose_Click()
DoCmd.Close
End Sub

Private Sub CmdEdit_Click()
Me.CmdCancel.Visible = True
Me.Contact_Details.Enabled = False
Me.Contact_Details.Locked = True
Me.Additional_Info.Enabled = False
Me.Additional_Info.Locked = True
Me.txtCompany.Enabled = False
Me.txtCompany.Locked = True
Me.txtFees.Enabled = False
Me.txtFees.Locked = True
Me.CmdFinish.SetFocus
Me.CmdEdit.Visible = False

If IsNull(Me.OptCompany.Value) Then
MsgBox "You must choose Client or Prospect", vbCritical, "Choose an Option"
End If
End Sub

Private Sub CmdFinish_Click()

If IsNull(Me.OptCompany.Value) Then
MsgBox "You must choose Client or Prospect", vbCritical, "Choose an Option"
End If

ControlEnabler False
Me.CmdCancel.Visible = False
Me.CmdEdit.Visible = True

End Sub

Private Sub Form_Current()
If IsNull(Me.OptCompany.Value) Then
Me.ClientStatus.Visible = False
Me.ProspectStatus.Visible = False
Else
Me.ClientStatus.Visible = (Me.OptCompany.Value = 1)
Me.ProspectStatus.Visible = (Me.OptCompany.Value = 2)
End If
End Sub

Private Sub Form_Load()

ControlEnabler False

End Sub

Private Sub OptCompany_Click()
Me.ClientStatus.Visible = (Me.OptCompany.Value = 1)
Me.ProspectStatus.Visible = (Me.OptCompany.Value = 2)
End Sub


Private Sub ControlEnabler(Locked As Boolean)
' Traverses the array of controls MyControls, and sets the enabled property
Dim var
'traverse the array
For Each var In Me.MyControls
'var = objecttype, MyContols = collectionName
'set the locked property of each control
var.Locked = Locked
Next
End Sub

Private Sub txtHeardAbout_Change()
If Me.txtHeardAbout = "Recommendation" Then
Me.txtRec.Visible = True
Else
Me.txtRec.Visible = False
End If
End Sub

Private Sub txtFees_BeforeUpdate(Cancel As Integer)

End Sub

 

[missinglinq]

...I have changed my code, but am not sure I've done it correctly as I am now getting an error when running code...

'run time error '438', Object does not support this property or method'

The problem is that you're attempting to set the Enabled Property of all Controls on your Form, and some Controls do not have the Enabled Property!

Labels, for instance, do not have the Enabled Property, so using your code on a Form that has a Label on it will error out in this way.

The trick here is to only loop thru Controls that have the particular Property you're setting. To exclude Labels from the looping, you'd do something like this:

Private Sub Form_Load()

Dim ctrl As Control

For Each ctrl In Me.Controls
 If Not (TypeOf ctrl Is Label) Then
   ctrl.Locked = True
   ctrl.Enabled = False
  End If
 Next
End Sub

Now this code only sets the Locked/Enabled Properties if the Control is not a Label.

Linq ;0)>

 

[access7]

Many thanks again for your reply, I can see where I am getting the problem now, In my control enabler I am referencing the tab control pages - these do not have a locked property available - only an enabled property... therefore, I would have to name each individual control separately in my array... unless you know of a way to reference the control on the tab pages?? I have looked in VB and tried a few things but nothing working.
I then tried to go back to just using the enabled property (and not the locked one) with the following code (as suggested by yourself, hopefully i have done it right)... but it is still putting the labels as 'greyed out / fuzzy' as if they are disabled... not sure why??

My code is now as follows for that section....

Private Sub ControlEnabler(Enabled As Boolean)
' Traverses the array of controls MyControls, and sets the enabled property
Dim ctrl
'traverse the array
For Each ctrl In Me.MyControls
'ctrl = objecttype, MyContols = collectionName
'set the enabled property of each control
If Not (TypeOf ctrl Is Label) Then
If Not (TypeOf ctrl Is CommandButton) Then
ctrl.Enabled = Enabled
End If
End If
Next
End Sub

I don't suppose anyone has any suggestions to either a) how to still use the references to the tab control using the locked property OR b) how to get the above code working so that the labels are not included when I run the control enabler....

Thanks in anticipation