The changes you requested to the table were not successful

[Kasperczak]

I am working with Access 2016 and have already spent more time on this small project than in healthy!



I keep being plagued with the following error on every form that I create:

"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."

First off, there are no duplicate entries - in any of the tables. The table setup is as follows:

Table 1 - Tbl-Locations
Location_ID PK
Location_Name


Table 2 - Tbl_Maps
Map_ID Autonumber
Location_ID FK
Map_Number
Map_Ref Calculated (LocationID+"-"+Map_Number)
Last_updated

Table 1 has a one to many relationship with table 2 using the Location_ID field. In Tbl_Locations the Location_ID field is unique. In Tbl_Maps, duplicates are allowed.

The map number is in the form of a two digit number. There are NO duplicate records. There are a handful of sample locations present in Tbl_Locations and 3 sample map records for a single location in Tbl_Maps. That is all. I have tried compacting the database. Nothing seems to happen when I click the Compact and Repair Database button. This is a clean new installation of Office 2016.

First, I created a simple form with two fields and a list box populated from the table to add, update and display the location IDs. I got the above error immediately when I tried to add records. I populated the table directly with a few examples and found I still got the error just trying to view them on the form or even just when trying to close the form. After spending several hours trying to figure it out, I go around the error by using DoCmd.RunSQL statements triggered from Delete, Add, Modify buttons instead of using bound fields and disabled the standard records navigation controls which I didn't really want anyway.

For the second form, I have a combo box which is populated from Tbl_Locations. The selected Location_ID value from this combo is used to filter a sub-form (sFrm_MapData) containing the list of maps and related information from Tbl_Maps so that only records for maps relating to the selected location are shown. After several hours of head scratching and a lot of Googling I eventually got the filtering to work. It seems that a relationship between the tables must be defined despite one of the early posts I chanced upon saying that this was not necessary. However it seems that I can edit the fields only for the first location in the combo list, but an attempt to edit records for any other location causes the error above again.

To avoid ambiguity, I also want to be able to populate the Location_ID field in Tbl_Maps automatically when the user starts to edit a new record in the subform.

I haven't used Access for a very long time, so I guess I should consider myself a newbie. I am otherwise a quite capable programmer skilled in a number of programming languages, but this current, very simple Access project has already cost me an extraordinary amount of time and has almost brought be to the point of nervous breakdown! I get the impression from various comments and online videos I have seen this something as simple as this should just work using the wizards to create controls. I'm stepping back from this for now and will come back for any answers in a while. I don't ever recall having such issues with Access several years ago.

[CJ_London]

if locationID and mapnumber are numbers this

Map_Ref Calculated (LocationID+"-"+Map_Number)

looks like you are trying to subtract one from the other (not sure how a calculated field would interpret it)

try

Map_Ref Calculated (LocationID & "-" & Map_Number)

Not sure what the benefit of this field is - calculated fields cannot be indexed, so does not directly relate to your error, but sometimes access throws out a misleading error message. But clearly ID=12 and Map Ref=2 will generate the same value as ID=1 and map ref=22

To avoid ambiguity, I also want to be able to populate the Location_ID field in Tbl_Maps automatically when the user starts to edit a new record in the subform.

if you have set your relationships this should happen automatically when you create the subform - alternatively ensure the linkchild and linkmaster are populated with LocationID

[Kasperczak]

Thank you for your reply and interesting observations.

if locationID and mapnumber are numbers this

Map_Ref Calculated (LocationID+"-"+Map_Number)

looks like you are trying to subtract one from the other (not sure how a calculated field would interpret it)

try

Map_Ref Calculated (LocationID & "-" & Map_Number)

Not sure what the benefit of this field is - calculated fields cannot be indexed, so does not directly relate to your error, but sometimes access throws out a misleading error message. But clearly ID=12 and Map Ref=2 will generate the same value as ID=1 and map ref=22

I used plus (+) in my post to express how the field is composed but in the actual coding I am using the ampersand (&), nevertheless, my apologies for the ambiguity. To be sure however, I did check and the actual formula to calculate the field is:

[Location_ID] & "-" & Right("00" & [Map_Num],2)

The location code is two or three alphanumeric characters, e.g. LE. The map number would be two numeric characters, for example 01. The map reference would then be LE-01. Map two for that location would be LE-02 and so on. This code must be unique, but since I discovered as you point out, that I cannot index a calculated field and use it as a primary key, I added an autonumber field for this purpose. This number will never be seen by the user, but it does give each map a unique record ID that can serve as the primary key. The map reference field is a file reference that is currently being used by the current manual system and needs to be retained. Otherwise there is indeed little benefit in having that calculated field in terms of coding the application.

if you have set your relationships this should happen automatically when you create the subform - alternatively ensure the linkchild and linkmaster are populated with LocationID

I had a look at those properties and they were unset. I set them as you suggested and the Location_ID field is now indeed being populated automatically, however the filtering no longer works and I now only get one empty record for each selection. Also, the issue of the original error message still persists.

Unfortunately it seems that a number of things that 'should happen automatically' as per the numerous videos and examples available online, simply do not seem to happen that way. I initially thought that developing this application was going to be a doddle....

I don't know whether this is an issue with my installation, some corruption of the database or just Access being a frustratingly annoying, but I would like to figure it out. For example, I would like to understand exactly why that error comes up when there is no duplication in the records and why it pops up when switching back to design view, or when I am trying close the form, having taken the action only of selecting an item in the combo box to test the filter, and having made no attempt whatsoever to add any records. Trying to close the form results in:

You can't save a record at this time.
Microsoft Access may have encountered an error while trying to save a record.
If you close this object now, the data changes you made will be lost.
Do you want to close the database object anyway?

To the best of my knowledge, I have not changed anything nor am I trying to save anything. I am just trying to close the form. That being the case I usually select Yes to escape from the form and re-open it. It is the only way that I can get back to Design View.

This is the subform recordsource query:

SELECT Tbl_Maps.Location_ID, Tbl_Maps.Map_Num, Tbl_Maps.Used_By, Tbl_Maps.Last_Used, Tbl_Maps.Reserved_For
  FROM Tbl_Maps 
  ORDER BY Tbl_Maps.Location_ID, Tbl_Maps.Map_Num;

This is the combo row source:

SELECT [Tbl_Locations].[Location_ID], [Tbl_Locations].[Location_ID] & " - " & [Tbl_Locations].[Location_Name]
  FROM Tbl_Locations
  ORDER BY [Location_ID];

This is the filter code in the combo After Update event:

query = "[Location_ID]='" & Me.cmbLocations.Column(0) & "'"
Me.sFrm_MapData.Form.Filter = query
Me.sFrm_MapData.Form.FilterOn = True
Me.sFrm_MapData.Requery

This is also called from the form_load event to ensure that the table is appropriately filtered by the default combo box item when the form is loaded, otherwise it shows all records, which is not what is required. I added a separate checkbox for the 'All' option which turns the filter on and off.

[CJ_London]

suggest you upload a copy of your db - remove anything sensitive or irrelevant to the question in hand, compact it and the zip

[Kasperczak]

suggest you upload a copy of your db - remove anything sensitive or irrelevant to the question in hand, compact it and the zip

Ok, thanks. Here it is.

[Minty]

You have a lot of little things going wrong here.

You have used a bound form for your combo map look up form, it should be unbound, and use the master child properties as described.
Your add new / modify form wasn't concatenating the unbound controls into the sql strings.

Have a look at the attached for some resolution.testdb.zip

[Kasperczak]

You have used a bound form for your combo map look up form, it should be unbound, and use the master child properties as described.

Thanks for taking the time to have a look at this. Your amended version is just what I was looking for! I thought it must be something quite simple that I was missing.

It seems that I used the link master fields and link child fields property incorrectly, but in my defence I would argue that I was mislead somewhat by what Access itself was telling me. When I tried to set these properties I clicked on the dropdown and only the Location_ID field was available to me so I set both properties to this field. This was only the case when the control was bound. With the control unbound I simply got 'Can't build a link between unbound forms' which suggested that the form ought to be bound. However, I now see that simply typing in the unbound combo control name just works anyway!

It also seems that record filtering now works without the need for additional filtering code in the combo AfterUpdate event. I see that when the form loads, the combo is blank and all records are displayed. However the 'All' checkbox no longer works so I guess a different approach is required for this?

[QUOTE=Minty;405224]Your add new / modify form wasn't concatenating the unbound controls into the sql strings.
I'm not sure what is meant by 'concatenating unbound controls into sql strings'?

Looking at your amended version, I'm wondering whether using bound controls was the reason for getting that error on this form as well. I will need to experiment a bit further with this. Although I understand what a control source is, I think I need to better understand when it is needed and when it is not.

[Minty]

You normally use Bound controls to enter or edit a table, or an editable query.

Unbound controls are normally for displaying unrelated content or for controlling elements of a form. E.g. search boxes, search or filter combo's.

Your SQL code wasn't referencing the controls on the form but the underlying records, hence it wouldn't work.

strSql = "UPDATE YourTable SET StringFieldName = '" & Me.txtBoxUnbound & "' WHERE ID = " &  Me.YourIDControl & " ;"

So if you create a SQL query string as above, the SQL interpreter has no idea about your forms or controls.
So you have to get the value and place it in the string (concatenate). If it's a text value you have to encapsulate the string in quotes.
For numeric values just use the number. Dates need to be formatted ideally as #yyyy-mm-dd# to avoid issues with the interpreter always wanting to use American date format (mm/dd/yyyy)

More info here; http://www.baldyweb.com/buildsql.htm and here https://www.fontstuff.com/access/acctut15.htm

[Kasperczak]

You normally use Bound controls to enter or edit a table, or an editable query.

Unbound controls are normally for displaying unrelated content or for controlling elements of a form. E.g. search boxes,
search or filter combo's.

Hmm, I am finding it difficult to draw the distinction as taking the example of a subform where filtering is controlled by a combo, the user must still be able to edit the fields in the table so the above would seem to suggest bound controls. Yet, as your corrections have clearly demonstrated, unbound controls are needed in practice.

Your SQL code wasn't referencing the controls on the form but the underlying records, hence it wouldn't work.

strSql = "UPDATE YourTable SET StringFieldName = '" & Me.txtBoxUnbound & "' WHERE ID = " &  Me.YourIDControl & " ;"

So if you create a SQL query string as above, the SQL interpreter has no idea about your forms or controls.

Thanks for expanding on that. I had another look at your corrected version and I can see that you changed the first occurrence of SQL in my code to reflect what you have said here. I didn't spot that at first. The really strange thing is that my original SQL code for the Add/Modify/Delete form does actually work as it stands although it clearly shouldn't. Maybe Access 2016 can now parse the form control names and substitute the appropriate text? Nevertheless, since I can now appreciate now that the way it was written is bad form, I will correct this as recommended. Thanks for the explanation and the links.

My overriding question regarding this form in particular was why DoCmd.RunSQL statements were necessary at all in the first place? With bound fields I got that error I mentioned in the oopening post whenever I used the navigation controls, including when trying to add a new record or edit an existing one. With unbound fields no data appeared and the navigation didn't do anything. I then tried to use bound controls along with 'DoCmd.GoToRecord , , acNewRec' in the 'Add' button code but again got that same error about duplicate records.
Eventually I unbound the controls and used DoCmd.RunSQL instead. In hindsight, I was probably using bound controls when I shouldn't have, but clearly I was missing something else instead. Eventually, I unbound the controls and used the SQL statements instead. If this is an appropriate way to go about this, then I will leave it as it is. The impression I get from researching online is that "Access should do this automatically".

BTW, is there any way to make the record navigation controls bigger? They are tiny! That is one reason why I have disabled them and decided to use buttons instead.

One further question: I am aware that when you call a control from another form then the format to use is Form!ControlID.Property. However, when should one use the format Me.ContolID.Property versus just ControlID.Property? When on the same form, both seem to work the same, but in various online examples I see both forms used, sometimes interchangeably.

[Minty]

The reason that your original commands didn't work was that by using a bound control, you where effectively "Dirtying" a new record or an existing one.
But because it was the ID field that was being changed, when you tried to go to a new record and Access tried to save the one you had just changed , it realised it couldn't update the Primary key and errored out.

That's why even on a bound form your search controls have to be unbound. You aren't using them to update a field, just search for a record based on that text / id.

[Kasperczak]

That sort of makes sense I think as the 'dirty' record would probably have contained a blank ID key (in the event that nothing had been typed in yet), or an existing one (in the event that an existing record had been displayed) neither of which would be acceptable as a new primary key when it tried to autosave. But, in that case, I don't get the purpose of bound controls. What's the point of having bound controls that prevent you from doing anything useful? I would have thought Access to be "intelligent" enough to determine that a)key already exists so record needs to be updated or discarded if content the same; b)key does not already exist so add a record; c)key and other fields are blank so discard; d)prompt the user for all other ambiguous conditions.

Your comment has just made me think about the distinction between a bound form and bound controls. I was perhaps under the mistaken impression that both amounted to the same thing, i.e. a bound form was one that contained a bound control. So let me get this straight:

Bound form = RecordSource in Form properties defined
Bound control = ControlSource in object properties defined

Is this correct?
Is there anything else that makes a form or an object bound?

With regards to the Add/Modify/Delete form, I am using the listbox to display and search the records, and the text fields to add, amend or delete the appropriate record, so in effect I was intending to use the fields to automatically display the currently selected record and add/update/delete records. Hence the use (I supposed) of bound controls. With unbound controls I had to use a bit of code in the OnClick event for the listbox to populate the text fields using the data on the current line of the list box.

[Minty]

A bound form can contain both bound and unbound controls.

A more normal method of displaying records on a bound form is to use a unbound Search box , with options to search various fields.
When you hit the search button, or select a value in a combo box, you use that value to load specific record(s) (the preferred method if you have a large data set) , or filter the forms records to match.

This allows you to find and search or Add new records as required.
The important thing is to disable the Primary key field if it's an Autonumber, as it's not editable anyway, and sometimes it's better not displayed at all.

A trick with a bound form is to set its underlying record source to an "Empty" record.

SELECT Blah, blah1 , blah2, other required fields FROM YourTable Where PrimaryKeyID = 0

This will load an empty recordset, so you don't get #Name in your controls, but don't have the overhead of loading the form underlying recordset with every record.

[Kasperczak]

Thank you very much once again for this useful information which I have carefully noted for future reference.

I have to say this somewhat surprised me though:

A bound form can contain both bound and unbound controls.
The important thing is to disable the Primary key field if it's an Autonumber, as it's not editable anyway, and sometimes it's better not displayed at all.

The reason I say that is, that, although I agree that an autonumber field need not be shown, the Access wizard alerted me to add a primary field when I created the table and the way the information was presented seemed to strongly suggest that this is an absolute must! I can see your reasoning though, since the number is automatically generated, is unique by definition, and can't be manually overridden, so, by default it uniquely identifies a record without the need to be designated as a primary key.

I guess that just because Access puts a heavy emphasis on making it so, doesn't necessarily mean that it must be so.

Anyway, thanks again.

[Minty]

When I said disable the Primary Key, I meant in terms of "seeing it" not the actual functionality of it, apologies for the poor choice of terms.

[CJ_London]

primary keys are essential to the effective design of a database - which is why you are prompted to add one. Their only function is to uniquely identify a record and an autonumber is ideal for this purpose. You don't have to use an autonumber - for example you could use a vehicle registration, social security number etc. However it is not generally recommended to use these as a primary key because they may not be available at the time the record is created or they change or need to be corrected - which means you have to update all child records which might be associated with that record. Numbers are also more efficient than text in that the take up less space so indexing will be much faster.

The fact Access creates a control on a form for a primary key if using a wizard is an inconvenience (in my opinion) - just delete the control. Forms do not need to display all the fields in a table, just the ones required for the form to meet its objectives.

Access is primarily a design platform for forms and reports. The fact it comes with ACE (the database engine) is immaterial (previously it was JET which is a Windows object) - you can use Access to link to any ODBC database - all of which require primary keys, it is not something unique to Access.