Error 3022 is being generated even when the record is not a duplicate

[Paul1]

I am having a problem with Run-Time Error '3022': The Changes you Requested to the Table were not Successful. Only my queries are preventing some non duplicates from being inserted into the table.



I have a table with three fields in addition to an auto number field. The fields are Report ID, Workspace ID and Version. These are all number fields. I have created a unique composite index using the design tab in the ribbon. Please the attachment.

Now when I add the values for each field manually for a row into the table, then only duplicates are prevented but non duplicates are entered into the table as I would expect. However when I Perform these steps in a procedure (please see attached code) then I get error 3022 at the 3rd update query when two of the three columns are duplicates of each other.

Probably there is a better way to do this? I only know how to build this joining table the way that I've done it.

Anyway, I would appreciate if you someone could suggest how to resolve this problem.
Many thanks.

[Gicu]

Hi Paul,

Why not append all three fields in one query? Can you try to create an actual append query having all three fields and simply run if in your sub?

Cheers,
Vlad

[Gicu]

Here is a sample db with an append query.

INSERT INTO tblReportsWorkspaces ( [Workspace ID], [Report ID], Version )
SELECT [Me]![lstWorkspace] AS Workspace_ID, [Forms]![frmViewReportVersions]![lstReports] AS Report_ID, [Forms]![frmEditReportVersion]![txtVersion] AS Version;

Cheers,
Vlad

[Gicu]

You will need to replace [Me] with the name of your form you're calling this from.

[Paul1]

Hi Gicu,

Thanks for your suggestion. I have just tried this. Please see the code below.
Only now when I run the application I am getting Access error 3061 - Too few parameters expected three.

I don't really understand this because I am running the query in code and supplying the inputs. I also tried adding parameters in the code but that did not work and so I have commented these out.

I would appreciate if you know what is going on here?

Many thanks.

Private Sub cmdOK_Click()
On Error GoTo Err_cmdOK
    Dim db As DAO.Database, qdf As DAO.QueryDef, strSQL As String, intReport As Integer
    
    Set db = CurrentDb

    'Append values to table tblReportsWorkspaces
    strSQL = "INSERT INTO tblReportsWorkspaces ( [Report ID], [Workspace ID], Version ) " _
           & "SELECT [Forms]![frmViewReportVersions]!lstReports AS Report_ID, " _
           & "Me.lstWorkspace AS Workspace_ID, [Forms]![frmEditReportVersion]!txtVersion AS Version"
        
'    Debug.Print strSQL
    
    Set qdf = db.CreateQueryDef("", strSQL)
'    qdf.Parameters(0) = [Forms]![frmViewReportVersions]!lstReports
'    qdf.Parameters(1) = Me.lstWorkspace
'    qdf.Parameters(2) = [Forms]![frmEditReportVersion]!txtVersion
        
    With qdf
        .SQL = strSQL
        .Execute (dbFailOnError)
    End With
        
    If IsOpen("frmEditReportVersion") Then
        Forms!frmEditReportVersion!lstWorkspaces.Requery
    End If
    
    If IsOpen("frmEditReportVersion") Then
        Forms!frmEditReportVersion!lstWorkspaces.Requery
    End If
    
    gfAddWS = True
    
    DoCmd.Close acForm, "frmSelectWorkspaceToAdd"
    
Exit_cmdOK:
    If Not qdf Is Nothing Then  'Only close object if it is open
        qdf.Close
        Set qdf = Nothing
    End If
    Set db = Nothing
Exit Sub


Err_cmdOK:
    Select Case Err.Number
    Case 3075
        Call MsgBox("You must select a workspace to add", vbCritical, "Reports Log Database")
    Case Else
        MsgBox Err.Number
        Dim strMsg As String
        strMsg = "Access Error " & Err.Number & vbCrLf _
               & Err.Description & vbCrLf & vbCrLf & "New workspace record was not added to the database"
        Call MsgBox(strMsg, vbCritical, "Reports Log Database")
    End Select
End Sub

[CJ_London]

& "Me.lstWorkspace AS Workspace_ID, [Forms]![frmEditReportVersion]!txtVersion AS Version"

you cannot use parameters in this way when executing a query from code. Try


& "Me.lstWorkspace AS Workspace_ID, " & [Forms]![frmEditReportVersion]!txtVersion & " AS Version"

and if this code is being run in your frmEditReportVersion form then you don't need the form collection


& "Me.lstWorkspace AS Workspace_ID, " & me.txtVersion & " AS Version"

[Gicu]

Hi Paul,

Why would you want to create a temporary query in VBA and run it like that instead of having an actual query that you run from code (by using Docmd.openquery or qdf.Execute). Have you tried to run the query I sent you yesterday (modified to replace Me with the calling form - probably frmSelectWorkspaceToAdd). Do you get any errors?

To run it you would simply use

Set qdf=currentdb.QueryDefs("YourQueryName")
qdf.execute (dbFailOnError)

Cheers,
Vlad

[Paul1]

Thanks to Ajax. The queries now run.

Gicu. I did try to run the query based on your example yesterday. Please see my earlier reply to you. I could not get it to run because my syntax was wrong and Ajax helped me to resolve that issue now.

With regard to creating a temporary query in VBA. I did try that but was having some problem passing the required parameters which as far as I understand would still need to be passed in vba code. I would then run it as a QueryDefs collection object as in your example above. (My understanding is that using Docmd.openquery to run a query is a bit amateurish and so I wouldn't use that method. Would you agree with me?)

Notwithstanding that do you think it is better to run the query created in the QBE grid and run it from VBA code as a QueryDefs collection and qdf.Execute in preference to creating a temporary query in VBA code like I have done? If so why?

Many thanks.

[Gicu]

When you save a query Access saves the optimized query plan and uses that every time as opposed to running a temporary query from VBA which will involve some overhead in processing. With your particular query it will probably not make a big difference. Also, I find that having an actual query makes it easier to maintain and/or modify.

Cheers,
Vlad

[Paul1]

I'm trying to convert this temporary query into a saved query as per Vlad's suggestion. Please the attached query and code where the query is called. I have tried placing the parameters in the parameters box in the QBE grid but I can't now get this to run. I would appreciate if you could have a look at it. Many thanks.

Private Sub cmdOK_Click()
On Error GoTo Err_cmdOK
    Dim db As DAO.Database
    
    Set db = CurrentDb

    db.Execute "qappWorkspaceIDto_tblReportsWorkspaces", dbFailOnError
        
    If IsOpen("frmEditReportVersion") Then
        Forms!frmEditReportVersion!lstWorkspaces.Requery
    End If
    
    If IsOpen("frmEditReportVersion") Then
        Forms!frmEditReportVersion!lstWorkspaces.Requery
    End If
    
    gfAddWS = True
    
    DoCmd.Close acForm, "frmSelectWorkspaceToAdd"
    
Exit_cmdOK:
    Set db = Nothing
Exit Sub

Err_cmdOK:
    Select Case Err.Number
    Case 3075
        Call MsgBox("You must select a workspace to add", vbCritical, "Reports Log Database")
    Case Else
        Dim strMsg As String
        strMsg = "Access Error " & Err.Number & vbCrLf _
               & Err.Description & vbCrLf & vbCrLf & "New workspace record was not added to the database"
        Call MsgBox(strMsg, vbCritical, "Reports Log Database")
    End Select
    Resume Exit_cmdOK
End Sub

[Gicu]

Hi Paul,

I don't thing you need to add those parameters. What happens if you removed them, save the query and then try to run it?

Cheers,
Vlad

[Paul1]

Hi Vlad,


I just tried it and the query is still asking for parameters. As I understand it, this is an action query so I can't use the querydefs object. That's why I used the statement db.Execute "qappWorkspaceIDto_tblReportsWorkspaces", dbFailOnError".
Only this does statement does not allow me to run parameters via VBA code. If I want to run parameters via code then I am back to creating a query in code using db.CreateQueryDef().
I would think there must be a way to apply the parameters in the parameters table in the QBE grid?


Thanks.


Paul

[Gicu]

Paul,
Are the forms open and the right selections made when you try to run the query?
Vlad

[Gicu]

Paul,

Have a look t the attached file and screen shot. I have opened all three queries, make my selections and run the query without any parameter prompts.

Cheers,
Vlad

[Paul1]

Hi Vlad,

Yes I think that is the problem. I am getting the selections but the forms are closing for 2 of those selections. I didn't consider that.
I can now see that this method will work. While it would normally be preferable to use a stored query, on this occasion I think it is preferable to create a temporary query via code as I need access to all the field selections when the query is run.
Many thanks for your help.