copy records from another table 1

[davyre]

Hi, I got this code

Do While TmpPartsTableAll.EOF = False
    With TmpPartsTableAll
    If .TmpPartNumber = DLookup("PartNumber", "TempConcatenateParts", "PartNumber=" & .TmpPartNumber) Then
    StrConcSQL = "UPDATE TmpConcatenateParts SET Qty=Qty+" & .TmpQty & ",UnitCost=UnitCost+" & .TmpPartCost _
             & " WHERE PartNumber=" & .TmpPartNumber
    Else
    StrConcSQL = "INSERT INTO TmpConcatenateParts ([PartNumber], [PartDescription], [Material], [Qty], [UnitCost], [VendorPart], " & _
                 "[Vendor], [Comment]) " & _
                 "VALUES ( .TmpPartNumber, .TmpPartDescription, .TmpMaterial, .TmpQty, .TmpPartCost, .TmpVendorPart, .TmpVendorDesc, .TmpComments)"
    End If
TmpPartsTableAll.MoveNext
Loop

What I want to achieve is to select every record in TmpPartsTableAll, look if there is duplicate record in it, then move it to a new table called TmpConcatenateParts. I know that .EOF and .MoveNext is for RecordSet, but I dont know the same attribute for table in Access. Anyone can help with the DO WHILE clause? And is that correct for the query StrConcSQL?Thanks!

n.b it is similar to my previous thread

http://www.tek-tips.com/viewthread.cfm?qid=1695399

 

[PHV]

Use a recordset with the following SQL code:
"SELECT * FROM TmpPartsTableAll"

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

 

[davyre]

I follow your suggestion

copyRSSQL = "Select * From TmpPartsTableALL"
Set copyRS = db.OpenRecordset(copyRSSQL)
copyRS.MoveFirst
       
Do While copyRS.EOF = False
    If [highlight #FCE94F]copyRS.TmpPartNumber[/highlight] = DLookup("PartNumber", "TempConcatenateParts", "PartNumber=" & copyRS.TmpPartNumber) Then
    StrConcSQL = "UPDATE TmpConcatenateParts SET Qty=Qty+" & copyRS.TmpQty & ",UnitCost=UnitCost+" & copyRS.TmpPartCost _
             & " WHERE PartNumber=" & copyRS.TmpPartNumber
    Else
    StrConcSQL = "INSERT INTO TmpConcatenateParts ([PartNumber], [PartDescription], [Material], [Qty], [UnitCost], [VendorPart], " & _
                 "[Vendor], [Comment]) " & _
                 "VALUES ( copyRS.TmpPartNumber, copyRS.TmpPartDescription, copyRS.TmpMaterial, copyRS.TmpQty, copyRS.TmpPartCost, copyRS.TmpVendorPart, copyRS.TmpVendorDesc, copyRS.Comments)"
    End If

    DoCmd.SetWarnings False
    DoCmd.RunSQL StrConcSQL
    DoCmd.SetWarnings True
    copyRS.MoveNext
Loop

but it shows compile error : method or data member not found. Why is that?

 

[davyre]

ok I found the way to do it

 If copyRS.Fields("TmpPartNumber").Value = DLookup("PartNumber", "TempConcatenateParts", "PartNumber=" & copyRS.Fields("TmpPartNumber").Value) Then
    StrConcSQL = "UPDATE TmpConcatenateParts SET Qty=Qty+" & copyRS.Fields("TmpPartNumber").Value & ",UnitCost=UnitCost+" & copyRS.Fields("TmpPartNumber").Value _
             & " WHERE PartNumber=" & copyRS.Fields("TmpPartNumber").Value
    Else
   [COLOR=#EF2929] StrConcSQL = "INSERT INTO TmpConcatenateParts ([PartNumber], [PartDescription], [Material], [Qty], [UnitCost], [VendorPart], " & _
                 "[Vendor], [Comment]) " & _
                 "VALUES ('" copyRS.Fields("TmpPartNumber").value "', '" copyRS.Fields("TmpPartDescription").value "', '" copyRS.Fields("TmpMaterial").value "', " copyRS.Fields("TmpQty").value ", " copyRS.Fields("TmpPartCost").value ", '" copyRS.Fields("TmpVendorPart").value "', '" copyRS.Fields("TmpVendorDesc").value "', '" copyRS.Fields("Comments").value)"')"[/color]
                 
    End If

but it still showing some syntax error. Any idea why? Thanks a lot!

 

[davyre]

I got this problem

If "'" & copyRS.Fields("TmpPartNumber").Value & "'" = DLookup("PartNumber", "TmpConcatenateParts", "PartNumber= '" & copyRS.Fields("TmpPartNumber").Value & "'") Then

it seems that the IF clause was not working (i.e no record goes into the IF when it should be). Is there something wrong in the code?
The copyRS.Fields("TmpPartNumber").Value is a string datatype
the IF compares the copyRS.Fields("TmpPartNumber").Value with PartNumber records in table TmpConcatenateParts.
Anyone help?Thanks!

 

[PHV]

If copyRS!TmpPartNumber = DLookup("PartNumber", "TmpConcatenateParts", "PartNumber=" & copyRS!TmpPartNumber) Then


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

 

[davyre]

it still shows error when the PartNumber in my case is 'H0009'. It worked with any other partnumber but when it came to this particular record, it shows runtime error 2471 The expression you entered as a query parameter produced this error : 'H0009'
any idea why? Thanks

 

[PHV]

[tt]If copyRS!TmpPartNumber = DLookup("PartNumber", "TmpConcatenateParts", "PartNumber=[!]'[/!]" & copyRS!TmpPartNumber[!]& "'"[/!]) Then[/tt]

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

 

[davyre]

Thanks, it worked! But still I got another issue here,
if the condition fits, it goes in to IF clause, then it gave me a message box to enter value like this:

for copyRS!TmpQty, copyRS!TmpPartCost, and copyRS!TmpPartNumber.
here is my code

StrConcSQL = "UPDATE TmpConcatenateParts SET Qty=Qty+ copyRS!TmpQty, UnitCost=UnitCost+copyRS!TmpPartCost" _
                   & " WHERE PartNumber= copyRS!TmpPartNumber"

thanks!

 

[PHV]

StrConcSQL = "UPDATE TmpConcatenateParts SET Qty=Qty+" & copyRS!TmpQty & ",UnitCost=UnitCost+" & copyRS!TmpPartCost _
  & " WHERE PartNumber='" & copyRS!TmpPartNumber & "'"

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