Query is not referencing a passed value from form consistently

[Pianopizza5]

Hello,

I am passing a record’s data (the primary key) [I ran into a problem with this also, but got it to work] from the form to an append query, which adds a record to the composite table.

(I am using the form value in the query itself, not as a filter criteria.)

Here is my query:

INSERT INTO tblDiscoveryTracking ( DefendantID, DiscoveryID )
SELECT tblDefendant.DefendantID, [Forms]![frmDiscoveryTracking]![Test] AS DiscID
FROM tblDefendant;

[tblDiscoveryTracking] is the composite table, connecting the Discovery and Defendant table.


[Test] is the item on the form (which is the DiscoveryID)

The problem is that the passed value does not initially appear in the query when I run the VBA code from my form (see attached).

• I tried using DoCmd.Save

VBA:
DoCmd.OpenQuery "qryInitialAddDefendant"

As a result, I get the system error:

Microsoft Access can’t append all the records in the append query… 5 record(s) to the table to due key violations.

• I verified that none of the fields are indexed
• The fields are not keys in the [tblDiscoveryTracking]
• I believe that it is because the DiscoveryID field is blank (it did not pass from [Forms]![frmDiscoveryTracking]![Test])

Though I found that sometimes, when I open the append query to the Datasheet View, the query populates correctly (see attached). Then, when I go to Design View and click Run, the results are added to the tblDiscoveryTracking.

• The referenced form is always open when I do this.

Any thoughts? I need for this to work more consistently and the I want to automate this process using the code in VBA.

Any help, thoughts is greatly appreciated. I have been struggling for this for a while.

[Micron]

I think the message means you are trying to put duplicate values in a field that does not permit dupes, but I can't see the entire message. That doesn't mean you had to manually index the field in table design. It may only be that you're trying to insert a dupe in a primary key field (which is automatically indexed). Or you have a composite key or a composite index that you're trying to insert duplicate values into.
EDIT - or as you stated, you're trying to insert values into a field where the value isn't permitted. This could be a zls (empty string) or null. Check if the fields you're appending into don't allow either of those.

[davegri]

I would give this a try:

NSERT INTO tblDiscoveryTracking ( DefendantID, DiscoveryID )
SELECT tblDefendant.DefendantID, [Forms]![frmDiscoveryTracking]![Test] AS DiscoveryID
FROM tblDefendant;

[Pianopizza5]

Thank you so much for your response. I very much appreciate it. I investigated this and neither the DefendantID or DiscoveryID in the target table (tblDiscoveryTracking) are primary keys and they about both not "Required" (see attached). So technically, the fields should allow duplicates. Also, please find the entire message attached.

Hmm, they are "Number" fields, so the property "Allow Zero Length" isn't available (and they need to be a "Number" field because they are in a relationship with an AutoNumber Primary key).

(I changed the SQL per the davegri's post. Now it seems that the values consistently appear!. However, the error is appearing every time. So, it would make if the DiscoveryID was indexed as a primary key.)

I'm going to respond to the other post concerning the next step (as it pertains to that too).

Thank you!

[Pianopizza5]

Thank you so much for your response! I tried recording it and it appears that the query more consistently populates with the DiscoveryID from the form.

However... every time I ran the query the same error popped up (when before, it only came up when I initially ran the query). Going into survival mode and using what I know/we discussed above, I wanted to try out our theory. ...It worked and I'm not in love with it for integrity reasons, so I would love to get both of your thoughts. The error was saying that it cannot add the items due to there being duplicates. Everything that I have read said that it only mattered if the destination table [tblDiscoveryTracking] had an indexed/primary key field. However, I figured let's see if it at least works if I remove the Primary Key in the original table [DiscoveryID]. This worked! However, the thing is that I have to sacrifice the Enforce Integrity in the Relationships, which is not good for a relational database.

The funky thing too is that I added multiple records, so there were duplicate DefendantIDs, and I did not get this error.

Would love to get both of your thoughts and if you think I'll run into any integrity problems later.

I am going to do some more testing to make sure is reliable, but I think it might be it!

Thank you all so much.

[Micron]

Thanks for posting the entire message. I'm still going with that you're trying to duplicate records where it's not allowed. Maybe there is something amiss with a relationship you set between those tables, but I agree - looks like dupes should be allowed. As for

the passed value does not initially appear in the query when I run the VBA code from my form

If you have the query open then edit its recordset by some other means (e.g. vba) you will have to refresh the view as it won't do that automagically. Try the refresh all option on the ribbon. Maybe the reason for the message is that you've inserted the record one way or another without realizing/seeing it and then are trying to insert the same form value another time. Or the form value hasn't actually changed although you think it has. To test that, try presenting a message box that shows the control value within the sql statement your code constructs. What you have as sql should work, but if that is exactly how your code looks, it won't, but you'd get a runtime error instead.

[davegri]

Did a quick mockup and found a method that works consistently for me. Added a button to the form.


Code for the button

Option Compare Database
Option Explicit


Private Sub cmdInsert_Click()
    Dim sSQL As String
    
    sSQL = "INSERT INTO tblDiscoveryTracking ( DefendantID, DiscoveryID ) " _
    & "SELECT tblDefendant.DefendantID, " & Me.Test & " AS DiscoveryID " _
    & "FROM tblDefendant;"
    Debug.Print sSQL
    CurrentDb.Execute sSQL, dbFailOnError
    Me.Requery
    MsgBox "Done!"
End Sub

[CJ_London]

Unless someone else has suggested it and I've missed it, the message could relate to a multi field index set to no duplicates

[Micron]

Mentioned in post 2.

[Pianopizza5]

Wow, thank you all so much! There are so many helpful comments!

I applied the code that davegri wrote and it worked. However, and I ran into some additional issues as my project has some additional levels.

Applying what was said in the comments above, I was able to get my database to function correctly, but I had to remove the "Enforce Referential Integrity" in the join between the tbDiscoveryTracking and tblDiscoveryProduction (key: DiscoveryID). Since this iteration worked and I really need to complete this project, I am just going to roll with it. Plus, the DiscoveryID is an autonumber, so I don't think I'll have a "duplicate" issue (furthermore, the database is going to be completely locked down for the users, so there shouldn't be any user errors).

Note: when I used davegri's code, I was able to keep the "Enforce Referential Integrity"


In the hopes of helping others if anyone has a similar issue, I placed the initial append code in an AfterUpdate event when an item is entered. Then, I created an additional append query, which runs instead of the initial append code if more defendants are added. This prevents duplicate defendants.

This is accomplished by filtering the DefendantID in the tblDiscoveryTracking for null values (IS NULL). I had this query run using a button labeled as "Refresh"

The SQL code is:

INSERT INTO tblDiscoveryTracking ( DefendantID, DiscoveryID, AppendDefPlaceholder )SELECT tblDefendant.DefendantID, [Forms]![frmDiscoveryTracking]![Test] AS DiscoveryID, tblDiscoveryTracking.DefendantID
FROM tblDefendant LEFT JOIN tblDiscoveryTracking ON tblDefendant.DefendantID = tblDiscoveryTracking.DefendantID
WHERE (((tblDiscoveryTracking.DefendantID) Is Null));

Thank you all again for your help.