Solved

looping code

Posted on 2009-04-13
20
532 Views
Last Modified: 2012-05-06
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
0
Comment
Question by:Susanstevenson
  • 10
  • 7
  • 3
20 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 24129366
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
 
LVL 61

Expert Comment

by:mbizup
ID: 24129384
<rs = OpenRecordset("qryBatchPayments", DB_OPEN_SNAPSHOT)>

You need to use SET when working with object variables:

SET rs = OpenRecordset("qryBatchPayments", DB_OPEN_SNAPSHOT)
0
 

Author Comment

by:Susanstevenson
ID: 24129497
Hey - still getting sub or function not defined... are there references that I should make sure I have checked?
0
 

Author Comment

by:Susanstevenson
ID: 24129525
I've added the references for active x data object library.. but still falling over on the above..
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 24129632
where is it failing?

do you have microsoft DAO x.x Object library in your references
0
 

Author Comment

by:Susanstevenson
ID: 24130116
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 24130503
ADD the MICROSOFT DAO x.x Object library to your references
0
 

Author Comment

by:Susanstevenson
ID: 24130520
SORRY - I JUST TRIED THAT I'M GETTING A MESSAGE SAYING IT CONFLICTS WITH AN EXISTING MODULE OR LIBRARY!!
0
 

Author Comment

by:Susanstevenson
ID: 24131302
DO YOU HAVE ANY FEEDBACK ON WHAT THAT MEANS? I'M ABIT CLUELESS...!
0
 
LVL 61

Expert Comment

by:mbizup
ID: 24131547
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:Susanstevenson
ID: 24131613
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
 
LVL 61

Expert Comment

by:mbizup
ID: 24131712
Use exclamation points for fieldnames:

rs!ID

Periods are for properties and methods such as:
rs.recordcount
or
rs.MoveFirst
0
 
LVL 61

Expert Comment

by:mbizup
ID: 24131720
An alternative syntax for fieldnames uses strings inside of parentheses:

rs("ID")
0
 

Author Comment

by:Susanstevenson
ID: 24131844
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
 
LVL 61

Expert Comment

by:mbizup
ID: 24132422
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
 

Author Comment

by:Susanstevenson
ID: 24136293
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
 

Author Comment

by:Susanstevenson
ID: 24136458
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
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 24136465
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
 
LVL 61

Expert Comment

by:mbizup
ID: 24136494
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
 

Author Closing Comment

by:Susanstevenson
ID: 31569482
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

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now