Copy Query Not working

[Dinger_80]

I have a copy query I created for users when they are creating similar records. This is supposed to help reduce input time into the system. The problem I am facing is that the copy query isn't working. Part of why I think it isn't working is because I have the auto number shut off on the primary key field. I did this because the autonumber was malfunctioning and even if I created a new table with all the information required of the original, eventually the error occurs again. So I shut the auto number off because the problem was that the auto number sometimes went back and grabbed a number it had previously used, thus creating duplicate values in the primary key field (which doesn't work).

The problem that I face is that now when I run the query is that nothing happens. The code I am running is simple enough. The second query updates the record to values it needs to have.

DoCmd.SetWarnings False
    DoCmd.OpenQuery "CopyRecordPreviousTRQuery"
    DoCmd.OpenQuery "PreviouSTestRequestUpdateQuery"
    DoCmd.SetWarnings True

Here is the SQL of the CopyRecordPreviousTRQuery

INSERT INTO TestRequestTable ( TypeOfTest, Requestor, DateSubmitted, DateRequired, CatalogNumber, ComponentTested, IsSampleHere, SampleArrivalDate, WitnessTesting, ProjectNumber, TestCatagory1, TestCatagory2, SampleDisposal, PurposeOfTest, SpecialProcedures, IntervalsNumber, FolderHyperLink, IndividualComponent, ProjectManager, QualityAssuranceNumber, TestLocation, TestingElseWhere, TRNumber, IsSubmitted, Status, FolderMoved, Tech, Comment, TechnicianCompleted, TRNumberCounter, CompletedDate, Completed, TestRequestNumber )
SELECT TestRequestTable.TypeOfTest, TestRequestTable.Requestor, TestRequestTable.DateSubmitted, TestRequestTable.DateRequired, TestRequestTable.CatalogNumber, TestRequestTable.ComponentTested, TestRequestTable.IsSampleHere, TestRequestTable.SampleArrivalDate, TestRequestTable.WitnessTesting, TestRequestTable.ProjectNumber, TestRequestTable.TestCatagory1, TestRequestTable.TestCatagory2, TestRequestTable.SampleDisposal, TestRequestTable.PurposeOfTest, TestRequestTable.SpecialProcedures, TestRequestTable.IntervalsNumber, TestRequestTable.FolderHyperLink, TestRequestTable.IndividualComponent, TestRequestTable.ProjectManager, TestRequestTable.QualityAssuranceNumber, TestRequestTable.TestLocation, TestRequestTable.TestingElseWhere, TestRequestTable.TRNumber, TestRequestTable.IsSubmitted, TestRequestTable.Status, TestRequestTable.FolderMoved, TestRequestTable.Tech, TestRequestTable.Comment, TestRequestTable.TechnicianCompleted, TestRequestTable.TRNumberCounter, TestRequestTable.CompletedDate, TestRequestTable.Completed, TestRequestTable.TestRequestNumber
FROM ComponentTable INNER JOIN (CatalogNumberTable INNER JOIN TestRequestTable ON CatalogNumberTable.ID = TestRequestTable.CatalogNumber) ON ComponentTable.ID = TestRequestTable.ComponentTested
WHERE (((TestRequestTable.TestRequestNumber)=[Forms]![PreviousTestRequestForm]![TestRequestNumber]));

If anyone could help me figure out why the copy query wont work it would be appreciated. Thank you.

 

[llkhoutx]

What does the query when executed in the QBE frame?

 

[Cronk]

On copy of the insert query, change it to a select query to ensure that it is actually selecting data which is to be inserted.

 

[Dinger_80]

llkhoutx,
I am not sure what you are asking. I definitely have no idea what the QBE Frame is.

Cronk,
I changed it to a select and the information I am looking for is returned. I did some looking around and decided to try out doing things in VBA and not worry about the query designed in Access. I am still using a query, but somehow doing a StrSQL is working better. It isn't working perfectly and I have some bugs I am working out but here is what I have set up so far. Seems that at some point it doesn't recognize the separation I set up in the values. I will post when I get things fully functional.

StrSQL = "Insert INTO TestRequestTable(TypeOfTest, Requestor, DateSubmitted, DateRequired, CatalogNumber, ComponentTested, " & _
        "SampleArrivalDate, WitnessTesting, ProjectNumber, TestCatagory1, TestCatagory2, SampleDisposal, PurposeOfTest, SpecialProcedures," & _
        "IntervalsNumber, IndividualComponent, ProjectManager, QualityAssuranceNumber, TestLocation, " & _
        "TestingElseWhere, TestRequestNumber) " & _
        "Values(" & Me.TypeOfTest & ", " & Me.Requestor & ", " & "'" & Me.DateSubmitted & "'" & ", " & "'" & Me.DateRequired & "'" & _
        ", " & Me.CatalogNumber & ", " & Me.ComponentTested & ", " & "'" & Me.SampleArrivalDate & "'" & ", " & Me.WitnessTesting & _
        ", " & Me.ProjectNumber & ", " & "'" & Me.TestCatagory1 & "'" & ", " & "'" & Me.TestCatagory2 & "'" & ", " & "'" & Me.SampleDisposal & "'" & _
        ", " & "'" & Me.PurposeOfTest & "'" & ", " & "'" & Me.SpecialProcedures & "'" & ", " & "'" & Me.IntervalsNumber & "'" & ", " & "'" & Me.IndividualComponent & "'" & _
        ", " & Me.ProjectManager & ", " & Me.QualityAssuranceNumber & ", " & Me.TestLocation & ", " & Me.TestingElseWhere & _
        ", " & "'" & TestNum & "'" & ");"
CurrentDb.Execute StrSQL, dbFailOnError

 

[Dinger_80]

Ok so I wrote this up and it seems to work fine.

TestNum = DLookup("MaxOfTestRequestNumber", "PrimaryKeyCounterQuery") + 1

StrSQL = "Insert INTO TestRequestTable(TypeOfTest, TRNumber, Requestor, TestRequestNumber,  TestLocation, DateSubmitted, DateRequired, SampleArrivalDate, " & _
        "ProjectNumber, ComponentTested, IntervalsNumber, TestCatagory1, TestCatagory2, SampleDisposal, ProjectManager, SpecialProcedures, " & _
        " WitnessTesting, IndividualComponent, QualityAssuranceNumber, TestingElseWhere, CatalogNumber )" & _
        "Values(" & Me.TypeOfTest & ", " & "'" & Me.TRNumber & "'" & ", " & Me.Requestor & ", " & TestNum & ", " & Me.TestLocation & _
        ", " & "'" & Me.DateSubmitted & "'" & ", " & "'" & Me.DateRequired & "'" & ", " & "'" & Me.SampleArrivalDate & "'" & ", " & Me.ProjectNumber & _
        ", " & Me.ComponentTested & ", " & Me.IntervalsNumber & ", " & "'" & Me.TestCatagory1 & "'" & ", " & "'" & Me.TestCatagory2 & "'" & _
        ", " & "'" & Me.SampleDisposal & "'" & ", " & "'" & Me.ProjectManager & "'" & ", " & "'" & Me.SpecialProcedures & "'" & _
        ", " & Me.WitnessTesting & ", " & Me.IndividualComponent & ", " & "'" & Me.QualityAssuranceNumber & "'" & ", " & Me.TestingElseWhere & _
        ", " & CatalogNumber & ");"
CurrentDb.Execute StrSQL, dbFailOnError