Solved

looping code

Posted on 2009-04-13
20
517 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
Comment Utility
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
Comment Utility
<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
Comment Utility
Hey - still getting sub or function not defined... are there references that I should make sure I have checked?
0
 

Author Comment

by:Susanstevenson
Comment Utility
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
Comment Utility
where is it failing?

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

Author Comment

by:Susanstevenson
Comment Utility
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
Comment Utility
ADD the MICROSOFT DAO x.x Object library to your references
0
 

Author Comment

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

Author Comment

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

Expert Comment

by:mbizup
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:Susanstevenson
Comment Utility
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
Comment Utility
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
Comment Utility
An alternative syntax for fieldnames uses strings inside of parentheses:

rs("ID")
0
 

Author Comment

by:Susanstevenson
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

743 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

9 Experts available now in Live!

Get 1:1 Help Now