?
Solved

looping code

Posted on 2009-04-13
20
Medium Priority
?
581 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 7
  • 3
20 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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
 

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 2000 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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
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…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

801 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