Linked Fields Creating a Duplicate Index Error

[Navyguy]

Hello All

Just getting back into Access after about 7 or so years away... seems I have forgot everything!!!

Back to a basic question(s)...

I have a Details_Form form based on a query of a single field (Equipment_ID_Number) which is an Indexed (No Duplicates) value (but not the primary key). The query comes from Tbl_Equipment.

The Details_Form Form_Load() SetFocus is on the Equipment_ID_Number that is established previously. This is to confirm that the user is editing the correct record; there can only be one Equipment_ID_Number.

The Equipment_Information form is a subform inside the Details_Form. The subform is based on a Tbl_Equipment and is supposed to be used to update the remaining fields after the Equipment_ID_Number is entered and saved to the table.

The Equipment_ID_Number is the linked field between the Details_Form and the Equipment_Information subform. The TextBox for the Equipment_ID_Number is hidden on the subform (not that matters). I have unhidden it to confirm that the Equipment_ID_Numbers match for troubleshooting purposes.

I can enter the data in the remaining boxes on the subform (which are the remainder of the fields in Tbl_Equipment); when I go to move to the next record I get an error that I am creating a duplicate entry in an indexed field (Equipment_ID_Number).

So what I think is happening is that when I move to the next record the subform (or maybe the mainform) tries to save the information in the linked master/child fields even though it was created by the .setfocus on the Details_Form.

I do not have any problems with my other subforms as they allow duplicates with the Equipment_ID_Number.

I am sure it is something really simple I am missing, but have spent hours searching for something simple and have found nothing. Perhaps the easy solution would be to allow the entry of the all that information on the same page instead of using subforms, but I am sure I have done this before in older versions of access with success.

Cheers

John

 

[mjdemaris]

I like your quote!
You could simply select the table you wish to make a form from, click Create Form. Then add a combo box to the header, and use the wizard to select how you want to get the data. The third option is the one you want on the first screen. Then format it to your preferences.

https://www.youtube.com/watch?v=wKtdiO_BfoI

Mike

 

[jdraw]

In typical form/subform in Access. The Form is based on the 1 side of a 1 to Many relationship and you seem to have Equipment (1) to EquipmentDetails(Many).
The EquipmentDetails table would have Equipment_ID and something to uniquely identify each Detail (if I'm guessing your set up correctly). Your Form and subform would be linked on fields Equipment_ID.

I'm not sure where your Setfocus fits? I think this is a situation where a graphic/jpg or two may help. I'm not following the verbiage, and am trying to guess how your form/subform (are or should be) set up.

Here's a youtube video by 599cd that might help.

Good luck

 

[Navyguy]

Thanks again for taking the time to respond.

I will try to be a bit clearer in my explanation...

Table = Tbl_Equipment
Contents of table = Equipment_ID_Number (indexed no duplicates)
Contents of table = ID (Autonumber and Primary Key)
Contents of table = several other text fields (no index)

Main Form = Details_Form
Control = Txt_Equipment_ID_Number
Control Source Query = Qry_Equipment_Info (only returns the Equipment_ID_Number)
OnLoad Event = me.Txt_Equipment_ID_Number.SetFocus
Command Button = Save Record

Main Form is "opened" as a result of a previous form where the user is asked to create a unique Equipment ID Number; when the "Create" button OnClick Event it saves the Equipment_ID_Number to the Tbl_Equipment and opens the Main Form.

SubForm = Equipment_Information
Control = the remaining fields of the Tbl_Equipment
Control Source = Tbl_Equipment
Linked Master Field = Txt_Equipment_ID_Number
Linked Child Field = Equipment_ID_Number

There are other subforms (for other information) in the tabbed control and they work OK as it creates a the Equipment_ID_Number Foreign Key (not indexed) in a different table.

Essentially think of the UI like this:

The user is asked to select an option and they select "Create" (which just takes them to a different form.
The create option takes them to a form where they are asked to create a unique Equipment ID Number

Assuming they create a unique number, they select "Create" and that is when the Main_Form opens with the above information. At this point they have the option of selecting any number of subforms in the tab control. All but one have a control source for a different table. The one that throws the error is the subform where it is basically asking for the remainder of the data to be inputted into the Tbl_Equipment. The error is that it trys to create a new Equipment_ID_Number because (I believe) it is reading the linked fields in either the main form of Subform and tries to resave that data.

My other subforms seem to work OK. I have been looking at an old 03 database that I did which uses the same process (totally different topic and fields) and it seems to be working fine, so I am not sure what I am missing. Is it the version of access I am using (using 2007)? Missing Update perhaps?

I did take a minute to watch the video links provided, but they don't seem to have quite the same set-up I have in this part of the form.

Cheers

John

 

[jdraw]

Can you show us your relationships window? This might tell us more about your tables.
You really need to get the tables and relationships set up to support your business. It isn't clear (to me anyway) what your business and rules are.

I may be misunderstanding but if you have a main form and subform dealing with different aspects of the same table, that will definitely be an issue.

 

[Navyguy]

Thanks for the reply again...

Here is a screen shot of the relationship between the tables.
Two of the table have a 1 to 1 as the records should never change so no need for multiple records. They are in separate tables because not all equipment will have "wiring" or will have "buttons".

Tbl_Maintenance has a 1 to many because each piece of equipment will have multiple entries of "maintenance".

Cheers

John

 

[jdraw]

Navyguy,

I think you could do better with design. We still don't know exactly what the "business" is, so can not comment on the structure with certainty. What I get from your posts is: (my best guess at the moment)

You /your company is in the business of maintaining Equipment. The Equipment is located in various Locations and specifically in identified Rows of Racks. The maintenance involves several "services" including vacuuming, cleaning, use of forced air, torquing... The Equipment has part numbers and contains a number of "Contact Blocks". While servicing Equipment the technician records a confirmation of the service(s) done to a piece of Equipment and can record dated Observations relating to the Equipment, condition, concerns etc.

You need a clear description of your business in plain English as a base for designing your database to support this business. Adjust my effort or give us the business overview in simple terms.

You need the business description in order to comment on the suitability of the database structure/data model to support that business.

 

[Navyguy]

I appreciate your patience JDraw...

I will provide the following, perhaps it will assist. You are essentially right in your concept...

There are specific pieces of equipment (a global term used to describe a grouping of individual items). Think of it like a "Car". A car has tons of individual items to make it a car and although there may be several similar cars available they are uniquely identified by their serial number. In this application the "Car Serial Number" is the "Equipment_ID_Number".

So while all cars have a "steering wheel", they all don't have the same steering wheel or the same parts to make the steering wheel. Same theory applies to the Tbl_Buttons and Tbl_Wiring.

Contact_Block_1 et all hold information about the wires and cables that are attached to that specific block. Perhaps all blocks on the equipment will not be used; maybe only 1 block, maybe all 3. I may actually add more fields to say 10 blocks... all equipment has blocks, but not all are used.

The items in Tbl_Buttons are simply "Part_Numbers" or identifiers if that part is there (yes / no). To better create a Normal Form design, I could separate out the HA and SAS information, but for the sake of two fields I was not worried about it much as some equipment will not have both HA and / or SAS. Some equipment will not have any HA or SAS, but that does not matter because the record will not be created for that equipment.

While all cars require maintenance, some maintenance is regular like "change oil", other maintenance may not be. Tbl_Maintenance, as you suggested is the date the "service" was preformed with some other related data. The regular things like "Change Oil" are in there; other unique items may be captured in the observations field or may create a separate "Other_Maintenance" field...

Again what I sent you was a representation of what the whole thing looks like in an attempt to sort out the original error in the linked fields. There are a number of other tables that are used along the same lines as well as a number of "lookup" tables.

Once again I appreciate your assistance.

Cheers

 

[jdraw]

Good stuff.

I still think there is some Normalization required, but you know your business better than any reader.

If you have different technicians that can service any equipment at any time, I think this may be helpful:

 Technician--> ProvidedServiceToEquipment <--Service
                                     ^
                                     +Equipment

where

Service is a table of the Services that you could perform
ServiceID PK
ServiceName


Technician is a table of info about each Technician
TechID PK
TechFirstName
TechLastName
...other info for this Technician

Equipment is your table related to a piece of Equipment

ProvidedServiceToEquipment is a junction table relating
Technician, Service and Equipment

I would have a separate autonumber PK on this table (eg TSEID)

TSEID PK
TechID FK
ServiceID FK
EquipID FK
DateOfService

This set up allows for new Equip, new Techs and new Services.

My feeling is that Observations/Notes are recorded during performance of the service. So in the Observation/Notes table I would recommend you have the TSEID as FK relating back to the junction table. But, as I said, you know your business better than any reader.

Good luck.