reference a subform form in public module (1 Viewer)

Cheez

New member
Local time
Today, 07:39
Joined
Mar 6, 2022
Messages
17
ok so I have a unbound main form w/a bound subform to a query. The main form has some combo boxes and a button which will change the where clause in the SQL statement of the subform. I have three forms that I want to use this in. In the module I'm trying to use this code refer back to subform. Something like this Me.Controls(StatementLedger).Form.RecordSource = SQL
 

moke123

AWF VIP
Local time
Today, 08:39
Joined
Jan 11, 2013
Messages
3,849
Assuming your passing some argument to the procedure, pass the form.

Code:
Sub SomeSub(frm as Form)
frm.YourSubform.YourField = X
 

Cheez

New member
Local time
Today, 07:39
Joined
Mar 6, 2022
Messages
17
ok let me show the entire code so you have a better sense of what i'm trying to do.

Code:
code in module

Public Sub SqlSearch(frm As Access.Form, intYear, intMonth, intLedgerID, intBank)

Dim Ayear, Amonth, Adays, LedgerID, BankID As Integer
Dim AfirstDay, AfirstDate, AendingDate, qrySccID, UD As String
  
On Error GoTo problem
    Ayear = frm.Controls(intYear)
    Amonth = frm.Controls(intMonth).Column(1)
    Adays = frm.Controls(intMonth).Column(2)
    AfirstDay = "/1/"
    AfirstDate = Amonth & AfirstDay & Ayear
    AendingDate = Amonth & "/" & Adays & "/" & Ayear
    BankID = Nz(frm.Controls(intBank), 0)
    LedgerID = frm.Controls(intLedgerID)
    qrySccID = ""
    UD = "SELECT tblTransactions.*, [Deposits]-[Withdrawals] AS Amount, [Charged]-[Paid] AS CCTotal, IIf([Cleared],[Amount],0) AS ClearedAmount, " _
        & "IIf([Cleared],0,[CCTotal]) AS PaidAmount, tblSavings.SBalance, tblChecking.CBalance, tblCreditCard.ccBalance, " _
        & "tblSavings.FinancialInstitutionID, tblChecking.FinancialInstitutionID " _
        & "FROM ((tblTransactions LEFT JOIN tblSavings ON tblTransactions.SavingsID = tblSavings.SavingsID) " _
        & "LEFT JOIN tblChecking ON tblTransactions.CheckingID = tblChecking.CheckingID) " _
        & "LEFT JOIN tblCreditCard ON tblTransactions.CreditCardID = tblCreditCard.CreditCardID " _
        & "WHERE " & qrySccID & "" & BankID & " AND tblTransactions.Archived=True " _
        & "AND tblTransactions.ChkDate BETWEEN #" & AfirstDate & "# AND #" & AendingDate & "# " _
        & "ORDER BY tblTransactions.ChkDate;"
  
    Select Case LedgerID
        Case Is = 1
            qrySccID = "tblChecking.FinancialInstitutionID="
        Case Is = 2
            qrySccID = "tblSavings.FinancialInstitutionID="
        Case Else
            MsgBox "Cannot Execute the Update. Make sure everthing is Correct!"
            Exit Sub
    End Select
  

Me.SavingsLedger.Form.RecordSource = UD
Me.CheckingLedger.Form.RecordSource = UD


problem:

    If Err.Number = 94 Then
        MsgBox "Please select a month and year to View!", vbInformation, "Statement Date!"     
    End If
End sub


main fourm with button the will trigger the event on subfourms

SqlSearch Me.StatementLedger.Form.RecordSource, "cboYear", "cboMonth", "txtLedgerID", "cboBank"
 
Last edited:

MarkK

bit cruncher
Local time
Today, 05:39
Joined
Mar 17, 2004
Messages
8,178
Don't reference the subforms in this routine. Change this routine to a function that only returns the correct SQL.
Code:
Function GetSqlSearch(frm As Access.Form, intYear, intMonth, intLedgerID, intBank) As String
    ....
    ....
    GetSqlSearch = UD
End Function
Then, do the assignment to subforms in the same code that called the above function.
Code:
Dim sql as String
sql = GetSqlSearch(Me, etc, etc, etc)
Me.SavingsLedger.Form.RecordSource = sql
Me.CheckingLedger.Form.RecordSource = sql
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:39
Joined
May 7, 2009
Messages
19,169
you can also change this portion:
Code:
...
    UD = "SELECT tblTransactions.*, [Deposits]-[Withdrawals] AS Amount, [Charged]-[Paid] AS CCTotal, IIf([Cleared],[Amount],0) AS ClearedAmount, " _
        & "IIf([Cleared],0,[CCTotal]) AS PaidAmount, tblSavings.SBalance, tblChecking.CBalance, tblCreditCard.ccBalance, " _
        & "tblSavings.FinancialInstitutionID, tblChecking.FinancialInstitutionID " _
        & "FROM ((tblTransactions LEFT JOIN tblSavings ON tblTransactions.SavingsID = tblSavings.SavingsID) " _
        & "LEFT JOIN tblChecking ON tblTransactions.CheckingID = tblChecking.CheckingID) " _
        & "LEFT JOIN tblCreditCard ON tblTransactions.CreditCardID = tblCreditCard.CreditCardID " _
        & "WHERE " & qrySccID & "" & BankID & " AND tblTransactions.Archived=True " _
        & "AND tblTransactions.ChkDate BETWEEN #" & AfirstDate & "# AND #" & AendingDate & "# " _
        & "ORDER BY tblTransactions.ChkDate;"
 
    Select Case LedgerID
        Case Is = 1
            qrySccID = "tblChecking.FinancialInstitutionID="
        Case Is = 2
            qrySccID = "tblSavings.FinancialInstitutionID="
        Case Else
            MsgBox "Cannot Execute the Update. Make sure everthing is Correct!"
            Exit Sub
    End Select
 

Me.SavingsLedger.Form.RecordSource = UD
Me.CheckingLedger.Form.RecordSource = UD
...
...

With:
Code:
...
...
  UD = "SELECT tblTransactions.*, [Deposits]-[Withdrawals] AS Amount, [Charged]-[Paid] AS CCTotal, IIf([Cleared],[Amount],0) AS ClearedAmount, " _
        & "IIf([Cleared],0,[CCTotal]) AS PaidAmount, tblSavings.SBalance, tblChecking.CBalance, tblCreditCard.ccBalance, " _
        & "tblSavings.FinancialInstitutionID, tblChecking.FinancialInstitutionID " _
        & "FROM ((tblTransactions LEFT JOIN tblSavings ON tblTransactions.SavingsID = tblSavings.SavingsID) " _
        & "LEFT JOIN tblChecking ON tblTransactions.CheckingID = tblChecking.CheckingID) " _
        & "LEFT JOIN tblCreditCard ON tblTransactions.CreditCardID = tblCreditCard.CreditCardID " _
        & "WHERE <ID> " & BankID & " AND tblTransactions.Archived=True " _
        & "AND tblTransactions.ChkDate BETWEEN #" & AfirstDate & "# AND #" & AendingDate & "# " _
        & "ORDER BY tblTransactions.ChkDate;"
 
    Select Case LedgerID
        Case Is = 1
            qrySccID = "tblChecking.FinancialInstitutionID="
        Case Is = 2
            qrySccID = "tblSavings.FinancialInstitutionID="
        Case Else
            MsgBox "Cannot Execute the Update. Make sure everthing is Correct!"
            Exit Sub
    End Select
 
    UD = Replace$(UD, "<ID>", qrySccID)
    
Me.SavingsLedger.Form.RecordSource = UD
Me.CheckingLedger.Form.RecordSource = UD

...
...
 

Cheez

New member
Local time
Today, 07:39
Joined
Mar 6, 2022
Messages
17
ok made the function and sub but it tells me invalid use of me.

Code:
Public Function GetSearchSql(frm As Access.Form, intYear, intMonth, intLedgerID, intBank)

Dim Ayear, Amonth, Adays, LedgerID, BankID As Integer
Dim AfirstDay, AfirstDate, AendingDate, qrySccID, UD As String

    Ayear = frm.Controls(intYear)
    Amonth = frm.Controls(intMonth).Column(1)
    Adays = frm.Controls(intMonth).Column(2)
    AfirstDay = "/1/"
    AfirstDate = Amonth & AfirstDay & Ayear
    AendingDate = Amonth & "/" & Adays & "/" & Ayear
    BankID = Nz(frm.Controls(intBank), 0)
    LedgerID = frm.Controls(intLedgerID)
    qrySccID = ""
    UD = "SELECT tblTransactions.*, [Deposits]-[Withdrawals] AS Amount, [Charged]-[Paid] AS CCTotal, IIf([Cleared],[Amount],0) AS ClearedAmount, " _
        & "IIf([Cleared],0,[CCTotal]) AS PaidAmount, tblSavings.SBalance, tblChecking.CBalance, tblCreditCard.ccBalance, " _
        & "tblSavings.FinancialInstitutionID, tblChecking.FinancialInstitutionID " _
        & "FROM ((tblTransactions LEFT JOIN tblSavings ON tblTransactions.SavingsID = tblSavings.SavingsID) " _
        & "LEFT JOIN tblChecking ON tblTransactions.CheckingID = tblChecking.CheckingID) " _
        & "LEFT JOIN tblCreditCard ON tblTransactions.CreditCardID = tblCreditCard.CreditCardID " _
        & "WHERE " & qrySccID & "" & BankID & " AND tblTransactions.Archived=True " _
        & "AND tblTransactions.ChkDate BETWEEN #" & AfirstDate & "# AND #" & AendingDate & "# " _
        & "ORDER BY tblTransactions.ChkDate;"
    
    Select Case LedgerID
        Case Is = 1
            qrySccID = "tblChecking.FinancialInstitutionID="
        Case Is = 2
            qrySccID = "tblSavings.FinancialInstitutionID"
        Case Else
            MsgBox "Cannot Execute the Search. Make sure everything is Correct!"
            Exit Function
    End Select
    
    GetSearchSql = UD
    

End Function

Public Sub SqlSearch()

    Dim sql As String
    
sql = GetSqlSearch(Me, "cboYear", "cboMonth", "intLedgerID", "intBank")
                  'Ivalid use of me!
Me.SavingsLedger.Form.RecordSource = sql
Me.CheckingLedger.Form.RecordSource = sql

End Sub
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:39
Joined
May 7, 2009
Messages
19,169
not familiar with the replace$ function (i guess that's what is.)
if you will closely look at the "new" UD string, i added <ID> on the Where clause.
you just need to Replace that with whatever the value in qrySccID.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:39
Joined
Feb 19, 2002
Messages
42,970
Also, your Dim statement are wrong. Only the last one of each Dim is getting the correct data type, all the others are being defined as variants. You have to do each one separately.
Dim a as string
Dim b as string
Dim c as Integer
 

Cheez

New member
Local time
Today, 07:39
Joined
Mar 6, 2022
Messages
17
Dim Ayear, Amonth, Adays, LedgerID, BankID As Integer Dim AfirstDay, AfirstDate, AendingDate, qrySccID, UD As String
Thanks Pat I have made those corrections

Code:
Dim Ayear As Integer
    Dim Amonth As Integer
    Dim Adays As Integer
    Dim LedgerID As Integer
    Dim BankID As Integer
    Dim AfirstDay As String
    Dim AfirstDate As String
    Dim AendingDate As String
    Dim qrySccID As String
    Dim UD As String

any other suggestions would greatly appreciated.
 

moke123

AWF VIP
Local time
Today, 08:39
Joined
Jan 11, 2013
Messages
3,849
Code:
AfirstDay = "/1/"
  AfirstDate = Amonth & AfirstDay & Ayear

You can use DateSerial() to get the first day of the month

Code:
DateSerial(Ayear,Amonth,1)

If you pass any date in the month you can use the one date to get the first day of month using Year() and Month() functions.

Code:
 DateSerial(Year(MyDate),Month(MyDate),1)

Code:
AendingDate = Amonth & "/" & Adays & "/" & Ayear

To get the last day of any month you can get the 0 day of the next month.

Code:
 DateSerial(Year(MyDate),Month(MyDate) + 1,0)
 
Last edited:

Cheez

New member
Local time
Today, 07:39
Joined
Mar 6, 2022
Messages
17
Code:
AfirstDay = "/1/"
  AfirstDate = Amonth & AfirstDay & Ayear

You can use DateSerial() to get the first day of the month

Code:
DateSerial(Ayear,Amonth,1)

If you pass any date in the month you can use the one date to get the first day of month using Year() and Month() functions.

Code:
 DateSerial(Year(MyDate),Month(MyDate),1)

Code:
AendingDate = Amonth & "/" & Adays & "/" & Ayear

To get the last day of any month you can get the 0 day of the next month.

Code:
 DateSerial(Year(MyDate),Month(MyDate) + 1,0)
thank you moke. I use this function on a different form in this DB, didn't think to use it here.



Code:
    Dim sql As String
    
sql = GetSqlSearch(Me, "cboYear", "cboMonth", "intLedgerID", "intBank")
                  'Ivalid use of me!
Me.SavingsLedger.Form.RecordSource = sql
Me.CheckingLedger.Form.RecordSource = sql

End Sub
Still having problems with this. Any suggestions anybody?
 

moke123

AWF VIP
Local time
Today, 08:39
Joined
Jan 11, 2013
Messages
3,849
Code:
Me.SavingsLedger.Form.RecordSource = sql
Me.CheckingLedger.Form.RecordSource = sql

if it's in a standard module shouldn't that be

Code:
frm.SavingsLedger.Form.RecordSource = sql
frm.CheckingLedger.Form.RecordSource = sql
 

Cheez

New member
Local time
Today, 07:39
Joined
Mar 6, 2022
Messages
17
I've uploaded a sample DB with what I'm trying to do. The code on the checking form is how I had it and is working fine, but I want this code to be universal. You'll see in the statement module the code I have previously posted. Any help would greatly appreciated.
 

Attachments

  • Registersample.accdb
    1 MB · Views: 238

MarkK

bit cruncher
Local time
Today, 05:39
Joined
Mar 17, 2004
Messages
8,178
Your tables aren't right, and it is making everything else in your system very difficult. You should have an Account table, a Transaction table, and a Post table. Account and Transaction are in a many-to-many relationship, with Post as the third or join table. One Account will have many posts. Also, one Transaction will have many posts. The Posts in one transaction will connect to many Accounts. The Posts in one Account will connect to many Transactions.

The Account table should hold every source or destination where money can flow including your expense categories. Each credit card and savings account, checking account, but also each expense or revenue account. Money flows to and from accounts. If you spend $50 on sporting goods from checking, then you reduce Checking, and you increase SportingGoods.
tAccount will have fields: AccountID, AccountName

The Transaction table should contain the date and the payee and not much else. Maybe an exchange rate if the transaction is in a foreign currency. Notes, tags, stuff like that, but functionally, it stores the date and the payee.
tTransaction will have fields: TransactionID, Date, Payee, Memo

Then the Post table, where all the nitty gritty work gets done. Each transaction should have a minimum of two posts, one where the money came from, and the other for where the money went, and these should balance to zero, but you will find that in reality many transactions have more posts. If you want to keep track of taxes in a transaction, maybe you post -55 to Mastercard, 5 to taxes, and 50 to office expenses. Each post should have the ID of the Account to which it was posted and the amount.
Post will have fields: PostID, TransactionID, AccountID, Amount, Memo (See how one post connects a transaction to an account?)
A typical trip to a supermarket for me, I post that single transaction from Chequing to Health, Hygiene, Office, Cleaning, Groceries, so typically four or more posts for a single payment at the supermarket. Your system as currently designed will simply not be able to represent that transaction accurately.

Sorry to be the bearer of bad news, but think about the simplicity of what I've described. If you are seeking code that is universal, first of all seek table structures that will allow that kind of universality. Right now you are forced to look in too many different places to find data.

One example, say you want to find the balance of an account on a particular day. Here is that function...
Code:
Function GetBalance(AccountID As Long, AtDate As Date) As Currency
    Const SQL As String = _
        "SELECT Sum(Post.Amount) AS Balance " & _
        "FROM Journal INNER JOIN Post ON Journal.JournalID = Post.JournalID " & _
        "WHERE Journal.Date <= p0 AND Post.AccountID = p1 "
    
    With CurrentDb.CreateQueryDef("", SQL)
        .Parameters("p0") = AtDate
        .Parameters("p1") = AccountID
        GetBalance = .OpenRecordset.Fields(0)
    End With
End Function
That one function, and with that you can find the balance of any account on any day. That is the kind of simplicity you'll be able to realize if you change your tables.

Hope this helps,
 

Cheez

New member
Local time
Today, 07:39
Joined
Mar 6, 2022
Messages
17
Your tables aren't right, and it is making everything else in your system very difficult. You should have an Account table, a Transaction table, and a Post table. Account and Transaction are in a many-to-many relationship, with Post as the third or join table. One Account will have many posts. Also, one Transaction will have many posts. The Posts in one transaction will connect to many Accounts. The Posts in one Account will connect to many Transactions.

The Account table should hold every source or destination where money can flow including your expense categories. Each credit card and savings account, checking account, but also each expense or revenue account. Money flows to and from accounts. If you spend $50 on sporting goods from checking, then you reduce Checking, and you increase SportingGoods.
tAccount will have fields: AccountID, AccountName

The Transaction table should contain the date and the payee and not much else. Maybe an exchange rate if the transaction is in a foreign currency. Notes, tags, stuff like that, but functionally, it stores the date and the payee.
tTransaction will have fields: TransactionID, Date, Payee, Memo

Then the Post table, where all the nitty gritty work gets done. Each transaction should have a minimum of two posts, one where the money came from, and the other for where the money went, and these should balance to zero, but you will find that in reality many transactions have more posts. If you want to keep track of taxes in a transaction, maybe you post -55 to Mastercard, 5 to taxes, and 50 to office expenses. Each post should have the ID of the Account to which it was posted and the amount.
Post will have fields: PostID, TransactionID, AccountID, Amount, Memo (See how one post connects a transaction to an account?)
A typical trip to a supermarket for me, I post that single transaction from Chequing to Health, Hygiene, Office, Cleaning, Groceries, so typically four or more posts for a single payment at the supermarket. Your system as currently designed will simply not be able to represent that transaction accurately.

Sorry to be the bearer of bad news, but think about the simplicity of what I've described. If you are seeking code that is universal, first of all seek table structures that will allow that kind of universality. Right now you are forced to look in too many different places to find data.

One example, say you want to find the balance of an account on a particular day. Here is that function...
Code:
[CODE]Function GetBalance(AccountID As Long, AtDate As Date) As Currency
    Const SQL As String = _
        "SELECT Sum(Post.Amount) AS Balance " & _
        "FROM Journal INNER JOIN Post ON Journal.JournalID = Post.JournalID " & _
        "WHERE Journal.Date <= p0 AND Post.AccountID = p1 "

    With CurrentDb.CreateQueryDef("", SQL)
        .Parameters("p0") = AtDate
        .Parameters("p1") = AccountID
        GetBalance = .OpenRecordset.Fields(0)
    End With
End Function
[/CODE]
That one function, and with that you can find the balance of any account on any day. That is the kind of simplicity you'll be able to realize if you change your tables.

Hope this helps,
Mark thank you for your input. I made this DB based off of what I see when logging onto my Banking or Creditcard accounts. Just barebones transaction ledger. No itemized details of the transaction, just a general description. Now maybe as learn more I will want to do so. I just thought that with 99% of this code being copy & paste from one form to another with a little change why not try and make it universal. Never made a Public Module so why try. Again thanks for you time and effort to help.

Quick qusetion with your sample Function. How would you call it to a subform RecordSource?
Code:
Function GetBalance(AccountID As Long, AtDate As Date) As Currency

    Const SQL As String = _

        "SELECT Sum(Post.Amount) AS Balance " & _

        "FROM Journal INNER JOIN Post ON Journal.JournalID = Post.JournalID " & _

        "WHERE Journal.Date <= p0 AND Post.AccountID = p1 "



    With CurrentDb.CreateQueryDef("", SQL)

        .Parameters("p0") = AtDate

        .Parameters("p1") = AccountID

        GetBalance = .OpenRecordset.Fields(0)

    End With

End Function
 
Last edited:

moke123

AWF VIP
Local time
Today, 08:39
Joined
Jan 11, 2013
Messages
3,849
You dont need tblCboLookup. I doubt you will ever look up transactions that occur in the future.
I use a simple function for this which provides a value list rowsource.
Code:
Function GetYears() As String
    Dim strOut As String, i As Integer, y As Integer

    y = Int(Year(Date))

    For i = 2000 To Int(Year(Date))
  
        If strOut <> "" Then
            strOut = strOut & ";" & y
        Else
            strOut = y
        End If
      
        y = y - 1
      
    Next i

    GetYears = strOut

End Function
In the load event of your form
Code:
Me.cboYears.rowsource = getyears
'Me.cboYears = year(date)  'un-comment if you want to default to current year

Your tblMonthLookup is also not needed and will produce invalid data every 4 years. You do not take leap year into account as your using hardcoded number of days in the month.
If you use the 0 day of the next month as noted in my above post it will handle leap years. You only need a 2 column value list like
Code:
1;"January";2;"February";3;"March";4;"April";5;"May";6;"June";7;"July";8;"August";9;"September";10;"October";11;"November";12;"December"
column widths would be 0;1
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:39
Joined
May 7, 2009
Messages
19,169
here try and modify it as required.
 

Attachments

  • Registersample.accdb
    2 MB · Views: 250

LarryE

Active member
Local time
Today, 05:39
Joined
Aug 18, 2021
Messages
562
This design will not work in a relational database. It appears you are attempting to track financial transactions but using ACCESS as a spreadsheet. In addition, I have not a clue why you are attempting to set form record sources on the fly when the form is loaded. Why not simply create a query and then use that query as a record source? Anyway, if I were to create a financial transaction tracking application (which I have), then I would start with a properly designed table and relationship structure. Something like this (using some of your defined fields):
FinancialTables.PNG
  1. Each client may have multiple Financial Institutions they deal with
  2. Each institution offers multiple account types (Credit Cards, Checking accounts, savings accounts, investment service accounts etc.)
  3. Each account type will have multiple transaction types (Deposits, Withdrawals, Charges, Payments, Investment product purchases) and income or expense categories. Dates, amounts, transaction types and categories are all input fields on forms using textboxes, dropdown combo boxes, list boxes or option group controls. Balances should be calculated on forms and reports.
 

Users who are viewing this thread

Top Bottom