Access VBA - writing to a table from a recordset 2

[JoanieB]

I am trying to insert a new record into an existing table using data from a recordset. The data is modified from the original table and new values are being sent. Using an INSERT INTO statement, I cannot pull the fields from the original table, but the values being inserted are variables, not VALUES. I am prompted for the values at run time instead of them being pulled from my code.
How can I get around this?

 

[Dodgey]

Do you mind if I see an extract of the code you are using?

 

[JoanieB]

Do While Not RS.EOF
'Initialize variables
Bank = RS!Name
L60 = RS!L60
CurrType = RS!CurrType
USDTotal = 0
J05Date = Format(Now, "mmddyy&quot
J05Month = Left(J05Date, 2)
J05Day = Mid(J05Date, 3, 2)
J05Year = Right(J05Date, 2)
TicketNo = Null

Do While RS!L60 = L60
'add up banks here: check posneg
'4608 = debit
'4352 = credit
If RS!PosNeg = 4352 Then
USDTotal = USDTotal + RS!USDAmt
Else
USDTotal = USDTotal - RS!USDAmt
End If
RS.MoveNext

etc
etc

DoCmd.RunSQL "INSERT INTO TblOver50 (Nostro, Ledger, CurrencyType, Day, CredDeb, Total) VALUES (Bank, L60, CurrType, J05Date, DebCred, USDTotal);"

I appreciate your time.

 

[Dodgey]

Ok, looks good...

First with some basics...

Ensure all your variables are explicitlly declared/dimensioned. Then compile it. It should give you a good idea if the compiler is unsure about the variables.

Now for the reason I think it fails. Try this.
....
Dim msg As string

msg = "INSERT INTO TblOver50 (Nostro, Ledger, CurrencyType, Day, CredDeb, Total) VALUES (Bank, L60, CurrType, J05Date, DebCred, USDTotal);"

debug.Print msg
...
The output will be this...

"INSERT INTO TblOver50 (Nostro, Ledger, CurrencyType, Day, CredDeb, Total) VALUES (Bank, L60, CurrType, J05Date, DebCred, USDTotal);"

Why you ask? You have actually incorporated the variable into the SQL string. Instead try...

DoCmd.RunSQL "INSERT INTO TblOver50 (Nostro, Ledger, CurrencyType, Day, CredDeb, Total) VALUES (" & Bank & "," & L60 & "," & CurrType & "," & J05Date & "," & DebCred & "," & USDTotal & ""

Building SQL strings is the pits sometimes aint it.

Best of luck Joanie.