tyronedjackson
New member
- Local time
- Today, 05:54
- Joined
- Sep 7, 2010
- Messages
- 5
Having trouble with a transaction that adds payments to a payment table using the sum of the group of payment details, and then adds the details to a related detail payment table. The information comes from records in one subform that may include multiple detail payment records for mulitple customers within the same subform recordset. My code seems to only sporadically duplicate payments, but not all of the time. Below, I've tried to comment as much as possible to give an idea of what I am trying to do but please ask for clarification where needed. Thank you in advance for any help given.
Code:
'<-----------------START LINGUASYS HERE------------------>
Dim ws As dao.Workspace
Set ws = DBEngine.Workspaces(0)
ws.BeginTrans
Dim rs As dao.Recordset
Dim rst As dao.Recordset
'Define the recordset for adding student payments to EduSys
Set rs = Forms!Pagos![subformulario transacciones de pagos].Form.RecordsetClone
'Reiterate the recordset to control which line items get added to which orderId
Set rst = Forms!Pagos![subformulario transacciones de pagos].Form.RecordsetClone
Dim db As Database
Set db = CurrentDb
Dim Prs As dao.Recordset
Dim PPrs As dao.Recordset
Dim varPPaymentId
Dim varPaymentId
Dim varOrderId
Dim varOrderProgramId
Set Prs = db.OpenRecordset("linpayments", dbOpenDynaset)
Set PPrs = db.OpenRecordset("linppayments", dbOpenDynaset)
'Go through the recordset of the subform to the first one with the LinguaSysOrderId
'not equal to nothing
' Populate Recordset.
rst.MoveLast
rst.MoveFirst
rst.FindFirst "Len([LinguaSysOrderId] & '') > 0"
Do Until rst.NoMatch
bkmk = rst.Bookmark
'While there, with the second iteration of the recorset, find all of the line items
'that have OrderIds that match that one
rs.MoveLast
rs.MoveFirst
rs.FindFirst "[LinguaSysOrderId]=" & rst![LinguaSysOrderId]
'Add together all of the amounts where the LinguaSysOrderId is the same as the one
'we are on. Can't add related payment details yet because there would not be a
'related parent payment id yet
Do Until rs.NoMatch
Pay = Pay + rs!MontoEntrada
rs.FindNext "[LinguaSysOrderId]=" & rst![LinguaSysOrderId]
Loop
'For purposes of knowing what amount to record in the LinguaSys system, check the type
'of money that the client is paying with
If DLookup("[usetc]", "aspMoneda", "[IdMoneda]=" & Forms!Pagos!IdMoneda) = 0 Then ' if the curency is colones
tyDolls = Pay
Else
tyDolls = Pay / Forms!Pagos!TipodeCambio 'dollars
End If
'Add the SUM PAYMENT payment for all rows that have the same LinSysOrderId
'to the LinguaSys Parent Payment table (PPayment) using the variable 'Pay' from above
PPrs.AddNew
PPrs!OrderId = rst![LinguaSysOrderId]
PPrs!PaymentAmount = Pay
PPrs!PaymentDate = Forms!Pagos!Fecha
PPrs!CreditCardNumber = Forms!Pagos!NumDeTarjeta
PPrs!CardholdersName = Forms!Pagos!NombreDeTarjeta
PPrs!CreditCardExpDate = Forms!Pagos!VenceTarjeta
PPrs!CreditCardAuthorizationNumber = Forms!Pagos!Autorización
PPrs!PaymentMethodID = DLookup("[linPaymentMethodID]", "PaymentMethodConversion", "aspIdMetodo = " & Forms!Pagos!IdMetodo)
PPrs![Exchange Rate] = Forms!Pagos!TipodeCambio
PPrs![recibo#] = Forms!Pagos!IdPagos
PPrs!Amount = tyDolls
PPrs!PaymentDollars = Pay / Forms!Pagos!TipodeCambio
PPrs!CheckNumber = Forms!Pagos!NumDocumento
PPrs!Comments = Forms!Pagos!Notas
'get id while inside this routine to use when we add the related record to the Payment table
varPPaymentId = PPrs!PPaymentId
varOrderId = rst![LinguaSysOrderId]
PPrs.Update
'since the above id is sometimes not recorded we will do it again
'outside the addnew record routine and then compare the two numbers
'later. If they don't match, we know there was a problem in running
'the routine.
pbkmk = PPrs.LastModified
Dim varMyvar
PPrs.Bookmark = pbkmk
varMyvar = PPrs!PPaymentId
varOrderId = rst![LinguaSysOrderId]
If varMyvar <> varPPaymentId Then
MsgBox "Hubo un error añadiendo el pago a LinguaSys. Contacte Tyrone o Edgar."
GoTo Err_BotonDeVenta_Click
End If
'log the payment to the payment_log.txt file
PaymentLog ("LinguaSys Parent Payment COL" & Pay & " - REC:[" & Forms!Pagos!IdPagos & "] ORDID:[" & varOrderId & "] PPID:[" & varPPaymentId & "]")
'Have to reset the payment amount to zero so that the next Order is not overpaid
Pay = 0
'May need to move to the first record (I think) of the recordset before finding first since
'this will leave us at the last one found
rs.MoveLast
rs.MoveFirst
'Now we can Add the INDIVIDUAL payments to the LinguaSys Payment table where
'PPaymentId is recorded from above. Couldn't do this until the related PPaymentId
'was created
rs.FindFirst "[LinguaSysOrderId]=" & rst![LinguaSysOrderId]
Do Until rs.NoMatch
Prs.AddNew
Prs!PPaymentId = varPPaymentId
Prs!OrderProgramID = rs![LinguaSysOrderProgramId]
Prs!PaymentAmount = rs!MontoEntrada
Prs!PaymentDate = Forms!Pagos!Fecha
varPaymentId = Prs!PaymentId
varOrderProgramId = rs![LinguaSysOrderProgramId]
Prs.Update
'log the payment to the payment_log.txt file
PaymentLog ("LinguaSys Child Payment COL" & rs!MontoEntrada & " - REC:[" & Forms!Pagos!IdPagos & "] ORDProgId:[" & varOrderProgramId & "] PPID:[" & varPPaymentId & "] PID:[" & varPaymentId & "]")
rs.FindNext "[LinguaSysOrderId]=" & rst![LinguaSysOrderId]
Loop
rst.Bookmark = bkmk
rst.FindNext "[LinguaSysOrderId]<>" & rst![LinguaSysOrderId] & " AND Len([LinguaSysOrderId] & '') > 0"
Loop
ws.CommitTrans
'<-----------------END LINGUASYS HERE------------------>
