looping code

Hi All,

I am trying to process batch payments through an access database.  I have the actual code to take the payment working fine off a button and via a form (obviously this means you do each transaction one at a time) but now need to get the code to loop through a recordset and process each payment bringing back the results for each transaction..  I am having problems writing the looping code and referencing the recordset as my vb skills are limited... I don't know if I am better using a while not end of recordset loop and how would I write this?
When I call the attached function it goes to the openrecordset line and complains that my sub or function is not defined.  Can anyone help me to write the loop code and also advise me on how I should be referncing my fields?  Many Thanks,Susan
Public-Sub-makePayment.doc
SusanstevensonAsked:
Who is Participating?
 
mbizupConnect With a Mentor Commented:
Yes - that's one of Access's more meaningful error messages.  See the comments in the sample code:
'To edit an existing record
rs.edit
   rs!yourFieldName = ...
   rs!AnotherField
   ' etc
rs.update
 
'To add a new record:
rs.addNew
    rs!yourFieldName = ...
    rs!AnotherField
   ' etc
rs.update

Open in new window

0
 
Rey Obrero (Capricorn1)Commented:
try these codes


Public Sub makePayment()
Dim rs As DAO.Recordset, rsCnt as long,  i as long
Set rs = OpenRecordset("qryBatchPayments", DB_OPEN_SNAPSHOT)
rs.MoveLast
rsCnt=rs.recordCount
rs.MoveFirst
For i = 1 to rsCnt             
        MyNewReference = String(16 - Len(rs.ID), "0") & rs.ID        
        Set cfg = CreateObject("DataCash.Config")
        cfg.setConfigFile ("C:\Program Files\DataCash\datacashconf.xml")
        Set doc = CreateObject("DataCash.Document")
        doc.setConfig (cfg)        
        doc.Set "Request.Authentication.client", "..........."
        doc.Set "Request.Authentication.password", ".........."        
        doc.setParams "Amount", "currency", "GBP"
        doc.setWithParameterList "Request.Transaction.TxnDetails.amount", rs.PaymentAmount, "Amount"              
        doc.Set "Request.Transaction.CardTxn.Card.pan", rs.CCNumber
        doc.Set "Request.Transaction.CardTxn.Card.expirydate", rs.CCExp
        doc.Set "Request.Transaction.CardTxn.Card.issuenumber", rs.CcIssue
        doc.Set "Request.Transaction.CardTxn.Card.startdate", rs.CcStart
        doc.Set "Request.Transaction.CardTxn.Card.Cv2Avs", rs.CcSec      
        doc.Set "Request.Transaction.TxnDetails.merchantreference", MyNewReference
        doc.Set "Request.Transaction.CardTxn.method", "auth"        
        Set agt = CreateObject("DataCash.Agent")
        agt.setConfig (cfg)
        agt.send (doc)
        Set response = CreateObject("DataCash.Document")
        response.getResponseDocument (agt)
        s = response.getxml
        rs.PaymentStatus = DLookup("Status", "DCReturnCodes", "code = " & response.Get("Response.status"))
        rs.DCReturnCode = response.Get("Response.status")
        rs.DCReference = response.Get("Response.datacash_reference")
        rs.reason = response.Get("Response.reason")
        Debug.Print         
        Next i
     rs.close
        
End Sub

Open in new window

0
 
mbizupCommented:
<rs = OpenRecordset("qryBatchPayments", DB_OPEN_SNAPSHOT)>

You need to use SET when working with object variables:

SET rs = OpenRecordset("qryBatchPayments", DB_OPEN_SNAPSHOT)
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
SusanstevensonAuthor Commented:
Hey - still getting sub or function not defined... are there references that I should make sure I have checked?
0
 
SusanstevensonAuthor Commented:
I've added the references for active x data object library.. but still falling over on the above..
0
 
Rey Obrero (Capricorn1)Commented:
where is it failing?

do you have microsoft DAO x.x Object library in your references
0
 
SusanstevensonAuthor Commented:
IT TRIPS THE DEBUG ERROR AT THE SET RS=OPENRECORDSET STATEMENT.  I HAVE THE REFERENCES FOR ADO RECORDSET 2.8 AND ADO 2.8 LIBRARY TICKED...
0
 
Rey Obrero (Capricorn1)Commented:
ADD the MICROSOFT DAO x.x Object library to your references
0
 
SusanstevensonAuthor Commented:
SORRY - I JUST TRIED THAT I'M GETTING A MESSAGE SAYING IT CONFLICTS WITH AN EXISTING MODULE OR LIBRARY!!
0
 
SusanstevensonAuthor Commented:
DO YOU HAVE ANY FEEDBACK ON WHAT THAT MEANS? I'M ABIT CLUELESS...!
0
 
mbizupCommented:
You need to prefix OpenRecordset with a database object such as CurrentDB:

Set rs = CurrentDB.OpenRecordset("qryBatchPayments", DB_OPEN_SNAPSHOT)


Also, please don't use Caps Lock.  It comes across as yelling in netiquette, and tends to scare Experts off :-)
0
 
SusanstevensonAuthor Commented:
Apologies Capricorn1- no offence intended... will make sure not to use caps in future.  Thanks mbizup...
By prefixing it and also removing another reference, it is now working.  However, it doesn't like the way i have named my fields  rs.id, rs.PaymentAmount etc - should I be putting these in brackets or quotes or anything?
0
 
mbizupCommented:
Use exclamation points for fieldnames:

rs!ID

Periods are for properties and methods such as:
rs.recordcount
or
rs.MoveFirst
0
 
mbizupCommented:
An alternative syntax for fieldnames uses strings inside of parentheses:

rs("ID")
0
 
SusanstevensonAuthor Commented:
Thanks... getting closer!!! Can I update the recordset directly with the results received back from the payment processor? it is telling me that database or object is read only?
0
 
mbizupCommented:
Try changing the recordset type from Snapshot to Dynaset:

Set rs = CurrentDB.OpenRecordset("qryBatchPayments", DB_OPEN_DYNASET)

If that doesn't work out, then your query is probably not updateable.

0
 
SusanstevensonAuthor Commented:
Hey, I've changed it to dynaset and am getting the message "update or cancelupdate without add new or edit" - are you familiar with this?
0
 
SusanstevensonAuthor Commented:
Hi, I've added the statements rs.Edit and rs.Update.. to the code it is now running.  I do have one more question though ... I am using access 2007 to provide a front end to mysql tables.  I keep getting locked out of certain records - by chance one of these is the first record in my recordset.  I have restarted mysql and my pc (I know that the record was added previously by me) but the lock still remains - do you know of any code that I can run to drop the locked records if they appear in the recordset?
0
 
mbizupCommented:
I'm not familiar with mySQL.

Also - this really should be posted as a seperate question because:
- It's a seperate topic (each question should have a single-topic or issue as its focus)
- You will attract more Experts with a new thread

When you do post a new thread, include the Access and mySQL zones, but leave out the vbScript zone (vbScript and Access Visual Basic for Applications - VBA-  are different languages).
0
 
SusanstevensonAuthor Commented:
No problem - I knew that but wondered if you were familiar with mysql.  No problem - will post new question relating to mysql.
Many Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.