How do I insert data of one table to another?

[wind20mph]

Inserting data from History Table LIKE "P" Type into Preventive Maintenance Table and LIKE "R" Type into Repair Table.

I don't quiet get it because it says "PM Data Update" but nothing happened.
Below is the code:

[FONT=Tahoma][SIZE=2][COLOR=Red]Private Sub cmdUpdate_Click()
Dim strSQL
Dim dbMNT As Database
Dim rstHist As Recordset
Dim rstPM As Recordset
Dim rstRep As Recordset

Set dbMNT = CurrentDb()

Set rstHist = dbMNT.OpenRecordset("SELECT * FROM history ")
Set rstPM = dbMNT.OpenRecordset("SELECT * FROM pm ")
Set rstRep = dbMNT.OpenRecordset("SELECT * FROM repair ")

If rstHist.RecordCount >= 1 Then

Do Until rstHist.EOF = True
    
    If rstPM.RecordCount > 0 Then
    strSQL = "INSERT INTO pm(pmno, id, [desc], otherinfo ) SELECT history.refno, history.id, history.act, history.sparts FROM history WHERE (((history.reftype) LIKE 'P'));"

    DoCmd.RunSQL (strSQL)

    rstHist.MoveNext
    Else
        Exit Do
    End If
        
    If rstRep.RecordCount > 0 Then
    strSQL = "INSERT INTO repair(bdate, id, refno, [desc], otherinfo) SELECT history.hdate, history.id, history.refno, history.act, history.sparts FROM history WHERE (((history.reftype) Like 'R'));"

    DoCmd.RunSQL (strSQL)

    rstHist.MoveNext
    Else
        Exit Do
    End If

Loop
MsgBox "PM Data Update", vbInformation + vbOKOnly, "Updated"
Else
    MsgBox "Nothing to Update", vbInformation + vbOKOnly, "Nothing"
End If
rstHist.Close
End Sub[/COLOR][/SIZE][/FONT]

Any assistance, enlightenment, help would be greatly appreciated.
Thanks in Advance.

 

[JHB]

Could it be because you don't have the stars in the Like?

LIKE '*P*'

LIKE '*R*'

 

[wind20mph]

Could it be because you don't have the stars in the Like?

It does not use wildcards because its values are "P", "R", "C" and "O" from combo box value list.

 

[JHB]

It does not use wildcards because its values are "P", "R", "C" and "O" from combo box value list.

Then don't use like but =.

 

[wind20mph]

I did use this and the same result... no update.

[FONT=Tahoma][SIZE=2][COLOR=Blue]Private Sub cmdUpdate_Click()
Dim strSQL
Dim dbMNT As Database
Dim rstHist As Recordset
Dim rstPM As Recordset
Dim rstRep As Recordset

Set dbMNT = CurrentDb()

Set rstHist = dbMNT.OpenRecordset("SELECT * FROM history ")
Set rstPM = dbMNT.OpenRecordset("SELECT * FROM pm ")
Set rstRep = dbMNT.OpenRecordset("SELECT * FROM repair ")

If rstHist.RecordCount >= 1 Then

Do Until rstHist.EOF = True
    
    If rstPM.RecordCount > 0 Then
    strSQL = "INSERT INTO pm(pmno, id, [desc], otherinfo ) SELECT history.refno, history.id, history.act, history.sparts FROM history WHERE (((history.reftype) = 'P'));"

    DoCmd.RunSQL (strSQL)

    rstHist.MoveNext
    Else
        Exit Do
    End If
        
    If rstRep.RecordCount > 0 Then
    strSQL = "INSERT INTO repair(bdate, id, refno, [desc], otherinfo) SELECT history.hdate, history.id, history.refno, history.act, history.sparts FROM history WHERE (((history.reftype) = 'R'));"

    DoCmd.RunSQL (strSQL)

    rstHist.MoveNext
    Else
        Exit Do
    End If

Loop
MsgBox "PM Data Update", vbInformation + vbOKOnly, "Updated"
Else
    MsgBox "Nothing to Update", vbInformation + vbOKOnly, "Nothing"
End If
rstHist.Close
rstPM.Close
rstRep.Close
End Sub[/COLOR][/SIZE][/FONT]

 

[JHB]

Then post a stripped version of your database with some sample data + info in which form you have the code.

 

[wind20mph]

i cannot strip post the file, its too large now. about 1.2GB mnt.accdb (Access 365) file.

however, this is what i did that works:
i made an add query named qryaddrep and qryaddpm.

here is the code containing repair:

[FONT=Tahoma][SIZE=2][COLOR=Blue]INSERT INTO repair(bdate, id, refno, [desc], otherinfo) SELECT history.hdate, history.id, history.refno, history.act, history.sparts FROM history WHERE (((history.reftype) = "R"));[/COLOR][/SIZE][/FONT]

and for pm:

[FONT=Tahoma][SIZE=2][COLOR=Blue]INSERT INTO pm(pmno, id, [desc], otherinfo ) SELECT history.refno, history.id, history.act, history.sparts FROM history WHERE (((history.reftype) = "P"));[/COLOR][/SIZE][/FONT]

of which the only difference from VBA is the single quote and double quote.