How do I prevent records from being created in a related table?

[jbrumbau]

Hello,

I have been successfully using a database I've created for several months to populate an equipment list for a project we've been working on. However, the form has recently stopped working for some unknown reason. I've been having a problem recently where one table (Project Info) keeps auto-incrementing when I try to create a new record with another table (Equipment Info). Only the Equipment table is supposed to grow. It used to never grow the project table, but now it is and I'm stumped as to what is causing it.

Here are the details of my tables:

Table 1: Project Info
-Supposed to only hold one record (a static table)
-ProjKey=1 (integer, primary key)... Required=No, Indexed=Yes (No duplicates)
-Holds misc. project information such as project title, job #, etc.
-Holds project specific information for equipment
-Users can edit project information in a separate form (which works fine)

Table 2: Equipment List Data
-Holds many records and can increase in records (a dynamic table)
-Holds misc. information about the equipment
-ItemNo=* (integer, primary key... grows through a form macro when user enters a new record, which has worked fine in the past)... Required=No, Indexed=Yes (No duplicates)
-ProjLink=1 (integer)... Default Value=1, Required=No, Indexed=No

Here is the details of my query:
-Equipment table ProjLink has 1 to 1 relationship with Project table ProjKey
-Shows all equipment fields
-Shows specific project related info from the project table related to the equipment (this should be static)

Here are a handful of the details of my form:
-Allow Filters = Yes, Allow Edits = Yes, Allow Deletions = Yes, Allow Additions = Yes, Data Entry = No, Recordset Type = Dynaset, Record Locks = No locks, Fetch Defaults = Yes
-The macro that increments ItemNo for the equipment executes on "Got Focus". The code is below...

Private Sub ItemNoBox_GotFocus()
Dim strMsg As String
If Me!ItemNoBox.Value > 0 Then
Exit Sub 'we don't want to run this if something already exists
End If
Call incrNumber(Me, strMsg, "ItemNoBox")
If strMsg <> vbNullString Then
MsgBox strMsg, vbInformation
End If
End Sub

-incrNumber uses recordset clone to see what the itemNo was for the last record, then increments it. This was done to circumvent the problem[autonumber] had with incrementing whenever a user hits "Esc" to cancel input into a form, or when a record must be deleted for whatever reason. Since autonumbers cannot be edited, I had to code it this way.

In the form, upon creating a new equipment, I then get 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." I put a box that shows the ProjKey, and it has been changing when it shouldn't.

When I create records using the query, it does not have this problem. How can I fix my form? Or, how can I force my project table to never grow?

Thank you.

[FishVal]

Hello,

I have been successfully using a database I've created for several months to populate an equipment list for a project we've been working on. However, the form has recently stopped working for some unknown reason. I've been having a problem recently where one table (Project Info) keeps auto-incrementing when I try to create a new record with another table (Equipment Info). Only the Equipment table is supposed to grow. It used to never grow the project table, but now it is and I'm stumped as to what is causing it.

Here are the details of my tables:

Table 1: Project Info
-Supposed to only hold one record (a static table)
-ProjKey=1 (integer, primary key)... Required=No, Indexed=Yes (No duplicates)
-Holds misc. project information such as project title, job #, etc.
-Holds project specific information for equipment
-Users can edit project information in a separate form (which works fine)

Table 2: Equipment List Data
-Holds many records and can increase in records (a dynamic table)
-Holds misc. information about the equipment
-ItemNo=* (integer, primary key... grows through a form macro when user enters a new record, which has worked fine in the past)... Required=No, Indexed=Yes (No duplicates)
-ProjLink=1 (integer)... Default Value=1, Required=No, Indexed=No

Here is the details of my query:
-Equipment table ProjLink has 1 to 1 relationship with Project table ProjKey
-Shows all equipment fields
-Shows specific project related info from the project table related to the equipment (this should be static)

Could you be more specific here?
What is [ProjLink] table? Is it a bridge table to implement many-to-many relationship betweeen two tables mentioned above? Why relationship is 1 to 1?

Private Sub ItemNoBox_GotFocus()
Dim strMsg As String
If Me!ItemNoBox.Value > 0 Then
Exit Sub 'we don't want to run this if something already exists
End If
Call incrNumber(Me, strMsg, "ItemNoBox")
If strMsg <> vbNullString Then
MsgBox strMsg, vbInformation
End If
End Sub

Could you post incrNumber() routine code?

Regards.
Fish

[jbrumbau]

Hi Fish,

Thanks for your reply. [ProjLink] is simply an element/variable of the [Equipment List] table. There are only two tables involved, [Equipment List] and [Project Info]. The tables are actually not linked to each other until the [Equipment Form Query], where element [ProjLink] from [Equipment List] table is linked (1:1) to element [ProjKey] from [Project Info] table. The relationship is 1:1 because there is always 1 record in the project table, and that one record applies to each record in the equipment list table. I'm guessing in that case many [equipment]:1 [project] would also work, but 1:1 has always worked for me.

As for the incrNumber macro, even if I exclude the macro completely by deleting the event and then just type the number manually, I still have the problem. I am also positive that the problem originates from the form. If I enter data through the query, everything works fine.

Also, I have just figured out how to cease the bug from occuring! In the form, if I include a control box for [ProjLink] and make it locked, everything works perfectly and the [Project Info] table doesn't grow. I have no idea why this was the ticket, but apparently it was all that was needed. However, I still would like to know why this fixes the problem, because in the past, the form worked properly even without the control box I just added. It had only recently stopped adding records properly after I did some design changes to the form. In fact, the rest of my forms work properly without this control box (ones for instruments, specialty items, and other things). Also, it would be useful to know how to declare a table as static and to force it to never grow so that this problem doesn't arise in the future.

Thanks!

[FishVal]

Actually, to the best of my knowledge, there is only one reason for that - an editable control on your form linked to any field of [Project Info] table. When form is on new record and the control gets any data, new record will be added to [Project Info] table.

Best regards.
Fish

[jbrumbau]

Actually, to the best of my knowledge, there is only one reason for that - an editable control on your form linked to any field of [Project Info] table. When form is on new record and the control gets any data, new record will be added to [Project Info] table.

Best regards.
Fish

Hmm, that could explain something, I did throw in a control box that wasn't locked (since a user requested that it be unlocked) in one of my modifications to the form. It's still there though with the new control box that is locked which fixed the issue. Also, data wasn't being modified, but it was being accessed with a macro. Still seems strange.