Private Sub btnRequestPmtHistory_Click()

Dim db As DAO.Database
Dim qry As DAO.QueryDef
Dim dbpath As String
Dim rstTest As Recordset

dbpath = "G:\RT_BE\GT_Acct_History_Master.accdb" 'location of the database

'Set password in the connection parameter of the OpenDatabase method
Set db = OpenDatabase(dbpath, False, False, "MS Access;PWD=moose")

'***NEED TO CHECK TO SEE IF HISTORY IS ALREADY OUT THERE SO WE DON'T DUPLICATE THE HISTORY***

Set rstTest = db.OpenRecordset("select * from tblAcctHistory where Account_Number = " & acctno & "")

If rstTest.EOF = False Then
    MsgBox "There is already history on this account."
    'Insert code for what to do if history is already there
    Exit Sub
End If

TriggerAccount = 12345678 'Set this variable as the account for which history is requested
created = USERNAME 'Set this variable as the username of the requester (i.e. [Environ("UserName")] to get the windows login ID)

Set qry = db.QueryDefs("TriggerAcctInsert")

qry.Parameters(0) = TriggerAccount 'First parameter
qry.Parameters(1) = created 'Second parameter

qry.Execute

MsgBox "Account submitted for payment history request."

'Insert update account status to "Pending History Request"

End Sub

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.

Requesting Account History

Visual Basic