Need help with SQL Insert statement 1

[DougP]

I want to copy records from a subform that have a Check in the "MakeQAQC" field. But I need to make the new records have a "QA" added to the Number field. Is there a way to add extra text using this Insert or ???
hope that makes sense
I would much rather a single "Insert" than use a for next loop and have all the fields strung out

here is my code

    Dim Conn2 As ADODB.Connection
    Dim Rs1 As ADODB.Recordset
    Dim SQLcode, SQLcode2 As String
    Set Conn2 = CurrentProject.Connection
    Set Rs1 = New ADODB.Recordset
    
    SQLcode = "INSERT INTO [ChainOfCustody Details] ( MaterialDescription, HomogeneousAreaLocation, SampleLocation, Quantity ) " & _
            "SELECT * " & _
            "FROM [ChainOfCustody Details] " & _
            "WHERE ((([ChainOfCustody Details].MakeQAQC)=-1) AND " & _
            "(([ChainOfCustody Details].[Project Number])='" & Me![Project Number] & "') " & _
            "AND (([ChainOfCustody Details].Service)='" & Me![Service] & "') " & _
            "AND (([ChainOfCustody Details].Number)='" & Me![Number] & "') " & _
            "AND (([ChainOfCustody Details].[Building Name])='" & Me![Building Name] & "'));"
              
    Rs1.Open SQLcode, Conn2, adOpenStatic, adLockOptimistic

'  close it this way
    Set Rs1 = Nothing
    Set Conn2 = Nothing

DougP, MCP, A+
I Built my own R2D2
I love this site and all you folks that helped me over the years!

 

[dhookom]

Try something like:

    Dim Conn2 As ADODB.Connection
    Dim Rs1 As ADODB.Recordset
    Dim SQLcode, SQLcode2 As String
    Set Conn2 = CurrentProject.Connection
    Set Rs1 = New ADODB.Recordset
    
    SQLcode = "INSERT INTO [ChainOfCustody Details] ( MaterialDescription, HomogeneousAreaLocation, SampleLocation, Quantity ) " & _
            "SELECT * " & _
            "FROM [ChainOfCustody Details] " & _
            "WHERE ((([ChainOfCustody Details].MakeQAQC)=-1) AND " & _
            "(([ChainOfCustody Details].[Project Number])='" & Me![Project Number] & "') " & _
            "AND (([ChainOfCustody Details].Service)='" & Me![Service] & "') " & _
            "AND (([ChainOfCustody Details].Number)='QA" & Me![Number] & "') " & _
            "AND (([ChainOfCustody Details].[Building Name])='" & Me![Building Name] & "'));"
              
    Rs1.Open SQLcode, Conn2, adOpenStatic, adLockOptimistic

'  close it this way
    Set Rs1 = Nothing
    Set Conn2 = Nothing

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[DougP]

Well putting it where you have it is in the "Where" condition and does not add the "QA" to the begining of the field. It is trying to find something with QA in it. I probaby did not explain it too well. I need to add the "QA" to the data like so:
Say my "Number" field data is 456
I need the data inserted into the "Number" field to equal 456QA as the new records are inserted to the table. so this needs to happen somewhere before the "Where" condition and Access may not be sphisticated enough in the "Insert" part.

TIA

DougP, MCP, A+
I Built my own R2D2
I love this site and all you folks that helped me over the years!

 

[PHV]

Something like this ?
SQLcode = "INSERT INTO [ChainOfCustody Details] " _
& " (MaterialDescription,HomogeneousAreaLocation,SampleLocation,Quantity,[Project Number],Service,[Number],[Building Name]) " _
& "SELECT MaterialDescription,HomogeneousAreaLocation,SampleLocation,Quantity,[Project Number],Service,[Number] & 'QA',[Building Name] " _
& "FROM [ChainOfCustody Details] " _
& "WHERE MakeQAQC=-1 AND " _
& "[Project Number]='" & Me![Project Number] & "' " _
& "AND Service='" & Me![Service] & "' " _
& "AND [Number]='" & Me![Number] & "' " _
& "AND [Building Name]='" & Me![Building Name] & "'"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

Thanks PH. My bad, I guess I didn't see any other "Number" field referred to by your statement [blue]"QA" added to the Number field[/blue].

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[DougP]

Oh Yeah
PHV your a SQL guru thanks again.
Have a star


DougP, MCP, A+
I Built my own R2D2
I love this site and all you folks that helped me over the years!