Code to Send Values to Table from VBA (1 Viewer)

[dapfeifer]

Hey all...again...

Alright, so the code I've got now works, runs, and doesn't throw any errors, however it doesn't quite work right. What the following code is supposed to do is grab two values from one table, smash them together, then place them into another table :

Public Sub SetupUsableData()
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim pst As DAO.Recordset
    Dim tdf As DAO.TableDef
    Dim SQLCode As String
    Dim payorCode As String
    
    Set db = CurrentDb()
    Set tdf = db.TableDefs("tblImportData")
    
    SQLCode = "INSERT INTO tblUsableData ( SRCol," & _
    "Client_Code, Client_Name, Tran_ID, Inbnd_Bytes, Otbnd_Bytes, Bytes, Rate, Amount, RCCol, MTCol, MCCol, IOCol, ASCD_Client, " & _
    "Billable_Bytes, ECol ) SELECT tblImportData.PSR, " & _
    "IIf([PN1CC2] Is Null,[CC1],[CC1]+[PN1CC2]) AS Expr1, IIf([PC1CN2] Is Null,[PN2CN1],[PN2CN1]+[PC1CN2]) AS Expr2, " & _
    "tblImportData.PC2TRID, tblImportData.INBND_BYTES, tblImportData.OTBND_BYTES, tblImportData.BYTES, tblImportData.RATE, tblImportData.AMOUNT, " & _
    "tblImportData.RCCol, tblImportData.MTCol, tblImportData.MCCol, tblImportData.IOCol, tblImportData.ASCD_CLIENT, " & _
    "tblImportData.BILLABLE_BYTES, tblImportData.ECOL FROM tblImportData " & _
    "WHERE tblImportData.BILLABLE_BYTES Is Not Null;"
    DoCmd.RunSQL SQLCode
    
    Set tdf = db.TableDefs("tblUsableData")
    
    Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblImportData")
    Set pst = db.OpenRecordset("SELECT * FROM tblUsableData")
    Do While Not rst.EOF
    If (rst.Fields("PSR").Value = "PA") Then
        If (rst.Fields("PC2TRID").Value <> "") Then
            payorCode = (rst.Fields("PC1CN2").Value + rst.Fields("PC2TRID").Value)
            rst.MoveNext
        Else
            payorCode = rst.Fields("PC1CN2").Value
            rst.MoveNext
        End If
        Do Until rst.Fields("PN1CC2").Value = "   TOTAL"
        If ((rst.Fields("PSR").Value = "R" Or rst.Fields("PSR").Value = "S") And rst.Fields("BILLABLE_BYTES") <> "") Then
            pst.Edit
            pst.Fields("Payor_Code").Properties("Value") = payorCode
            pst.MoveNext
        End If
        rst.MoveNext
        Loop
    End If
        rst.MoveNext
    Loop
End Sub

If you do the step-by-step debug, it actually shows the pst.Fields("Payor_Code").Properties("Value") = payorCode line getting the right value. I have a feeling there is something more I need to do and that syntactically there is probably a better way to go about this, but my experience with VBA coding isn't exactly great. Any help on this would be appreciated!

 

[KenHigg]

pst.Payor_Code = payorCode

???

 

[dapfeifer]

Tried it, but it threw a "Method or data member not found" error. Thanks for posting though!

 

[DJkarl]

You have pst.Edit but do not have the corresponding pst.Update, if you do not call the Update the edits will not be saved.

 

[dapfeifer]

Yeah, I discovered that fatal flaw today, literally just before checking this board again. Thanks for the post though, appreciate the contribution.