Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

looping code

Posted on 2009-04-13
20
Medium Priority
?
599 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 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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

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!

Question has a verified solution.

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

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

564 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