Advanced Search

Requesting Account History

/ Published in: Visual Basic
Save to your folder(s)
Code that is used to call history for an account (trigger account). The user will pass the specified account to this database, along with the username requesting. Once the account is passed via this code, a VB program that runs automatically every minute will find any new accounts in this database based on created date being populated, but Process_Started and Process End being null in the tblAcctHistoryTrigger table. When the program finds a new account, it will populate the Process_Started field and being inserting history records in the tblAcctHistory table. Once history insert is complete, the Process_End field will be populated. Each day, history in tblAcctHistory from the prior day will be deleted to preven old history from being used.

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. Private Sub btnRequestPmtHistory_Click()
  2.  
  3. Dim db As DAO.Database
  4. Dim qry As DAO.QueryDef
  5. Dim dbpath As String
  6. Dim rstTest As Recordset
  7.  
  8. dbpath = "G:\RT_BE\GT_Acct_History_Master.accdb" 'location of the database
  9.  
  10. 'Set password in the connection parameter of the OpenDatabase method
  11. Set db = OpenDatabase(dbpath, False, False, "MS Access;PWD=moose")
  12.  
  13. '***NEED TO CHECK TO SEE IF HISTORY IS ALREADY OUT THERE SO WE DON'T DUPLICATE THE HISTORY***
  14.  
  15. Set rstTest = db.OpenRecordset("select * from tblAcctHistory where Account_Number = " & acctno & "")
  16.  
  17. If rstTest.EOF = False Then
  18.     MsgBox "There is already history on this account."
  19.     'Insert code for what to do if history is already there
  20.     Exit Sub
  21. End If
  22.  
  23. TriggerAccount = 12345678 'Set this variable as the account for which history is requested
  24. created = USERNAME 'Set this variable as the username of the requester (i.e. [Environ("UserName")] to get the windows login ID)
  25.  
  26. Set qry = db.QueryDefs("TriggerAcctInsert")
  27.  
  28. qry.Parameters(0) = TriggerAccount 'First parameter
  29. qry.Parameters(1) = created 'Second parameter
  30.  
  31. qry.Execute
  32.  
  33. MsgBox "Account submitted for payment history request."
  34.  
  35. 'Insert update account status to "Pending History Request"
  36.  
  37. End Sub

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.