insert into table from recordset

[megatronixs]

Hi all,

I'm running first a query on linked table that will create a recordset.
I need to get this into a local table, but the recordset that is returned, has 2 values (from 2 different fields) but I got stuck with this.
Where did I go wrong on the below code? the "values(rst1, rst2)" is wrong, just don't know how to do it.

Private Sub btn_check_pro_Click()
    Dim db As DAO.Database
    Set db = CurrentDb
    Dim from_date As String
    Dim to_date As String
    Dim branch As String
    Dim account As String
    Dim rs As DAO.Recordset
    Dim rst As DAO.Recordset
    Dim StrSQL As String
Set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT pro_nr FROM tbl_batch_process_pro", dbOpenDynaset)
        rs.MoveFirst
Do Until rs.EOF
    pro_nr_txt = rs![pro_nr]
If rs.EOF = True Then
    Set rs = Nothing
        Set db = Nothing
    Exit Sub
End If

    Set rst = db.OpenRecordset("SELECT BRANCH_NO, ACCOUNT_NO " & _
    "FROM tbl_products_1 " & _
    "WHERE CUSTOMER_ID = " & pro_nr_txt & " AND ACCOUNT_TYPE_CODE = ""83""; ", dbOpenDynaset)
If Not rst.EOF Then
    StrSQL = "INSERT INTO tbl_batch_process_idr (branch, account) VALUES (rst1, rst2);"
        DoCmd.SetWarnings False
            DoCmd.RunSQL StrSQL
        DoCmd.SetWarnings True
End If
                
Set rst = Nothing
    Set rst = db.OpenRecordset("SELECT BRANCH_NO, ACCOUNT_NO " & _
    "FROM tbl_products_2 " & _
    "WHERE CUSTOMER_ID = " & pro_nr_txt & " AND ACCOUNT_TYPE_CODE = ""83""; ", dbOpenDynaset)
If Not rst.EOF Then
    StrSQL = "INSERT INTO tbl_batch_process_idr (branch, account) VALUES (rst1, rst2);"
        DoCmd.SetWarnings False
            DoCmd.RunSQL StrSQL
        DoCmd.SetWarnings True
End If
rs.MoveNext
    
Loop
rs.Close
Set rs = Nothing
Set rst = Nothing
Set db = Nothing
Form.Requery
Form.Refresh
MsgBox "Your fries are done!", vbInformation
Form.Requery
End Sub

Greetings.

 

[moke123]

StrSQL = "INSERT INTO tbl_batch_process_idr (branch, account) VALUES (rst1, rst2);"

should be

StrSQL = "INSERT INTO tbl_batch_process_idr (branch, account) VALUES (" & rst!BRANCH_NO & "," & rst!ACCOUNT_NO & ")"

instead of runSQL, you should also use

db.execute StrSQL,dbfailonerror

same thing applies to the other lines of your code

 

[megatronixs]

Hi Moke123,

It works really great
I have only 2 more problems with this now. It will add it on a new line (record) was wondering how it could get to the same line in the table as there is the criteria for the query. Second problem is that if I have only on record "pro_nr" (the criteria) it will just go on and as the new data that was inserted is a new record and the EOF is not reached this way.

Greetings.

 

[moke123]

I was trying to figure out what you were doing and cleaned it up so i could understand it.

Private Sub btn_check_pro_Click()

    Dim db As DAO.Database
    Set db = CurrentDb

    Dim branch As String
    Dim account As String
    Dim StrSQL As String

    Dim rs As DAO.Recordset
    Dim rsP1 As DAO.Recordset
    Dim rsP2 As DAO.Recordset

    Set db = CurrentDb

    Set rs = db.OpenRecordset("SELECT pro_nr FROM tbl_batch_process_pro")

    if not rs.BOF and rs.EOF then

    Do Until rs.EOF


        Set rsP1 = db.OpenRecordset("SELECT BRANCH_NO, ACCOUNT_NO FROM tbl_products_1 WHERE CUSTOMER_ID = " & rs![pro_nr] & " AND ACCOUNT_TYPE_CODE = ""83""; ")

        If Not rsP1.BOF And rsP1.EOF Then

            StrSQL = "INSERT INTO tbl_batch_process_idr (branch, account) VALUES (" & rsP1!BRANCH_NO & "," & rsP1!ACCOUNT_NO & ")"
            db.Execute StrSQL, dbFailOnError
        End If


        Set rsP2 = db.OpenRecordset("SELECT BRANCH_NO, ACCOUNT_NO FROM tbl_products_2 WHERE CUSTOMER_ID = " & rs![pro_nr] & " AND ACCOUNT_TYPE_CODE = ""83""; ")

        If Not rsP2.BOF And rsP2.EOF Then

            StrSQL = "INSERT INTO tbl_batch_process_idr (branch, account) VALUES (" & rsP2!BRANCH_NO & "," & rsP2!ACCOUNT_NO & ")"

            db.Execute StrSQL, dbFailOnError

        End If

        rs.MoveNext

    Loop

    End If

    rs.Close
    Set rs = Nothing
    Set rsP1 = Nothing
    Set rsP2 = Nothing
    Set db = Nothing

    Form.Requery
    Form.Refresh
    MsgBox "Your fries are done!", vbInformation
    Form.Requery

End Sub

I have only 2 more problems with this now. It will add it on a new line (record) was wondering how it could get to the same line in the table as there is the criteria for the query.

Are you saying you want one line with the pro_nr, Branch and account from product1 and branch and account from product2 for each pro_nr?

Second problem is that if I have only on record "pro_nr" (the criteria) it will just go on and as the new data that was inserted is a new record and the EOF is not reached this way.

not sure i understand. it should only loop the number of records in your first recordset. the other recordsets will have either no records or 1. Note the change of the rs test - "if not rs.BOF and rs.EOF then" The new records are being inserted into another table which isnt in the loop and shouldnt have any effect on .EOF.

 

[megatronixs]

Hi,
the pr_nr could be in one of the 2 tables, that is why I need to check if with that criteria there is in one or more in the first table, if not, it goes to the second table, and if there still not, then it stops. I also will add there some more code to add a "yes" or "no" if there are no records at all.
Maybe it will be smart to just add the records returned on other table where the criteria is stored, this way it will be on the same "row"
I will check with your code how things will work and then see what next step will be.

Thanks a lot

Greetings.