Solved combo with 2 control sources (1 Viewer)

[ClaraBarton]

I have a combo box with 3 rowsource fields (ID, Category, Type) that I want 2 control sources.
If a selection is made for a category type, the control source is the fCatetoryID.
If the selection is an account type the control source is TAcct (transfer account).
I made an If, then function for the Control Source but the combo won't work with a function as a control source.

My search for help (it would seem a very simple and common issue) has been null.

 

[theDBguy]

Are you sure you don't want a cascading combobox?

 

[GPGeorge]

So, each record in the rowsource has an ID, a Category value, and an Account Type value. Can you provide some example data to illustrate this?

It looks to me on the surface, at least, that you are not talking about two different control sources at all. The ID must be the control source for the combo box or it makes no sense. The goal, rather, seems to be to DISPLAY either the second column -- Category -- or the third column -- Account Type. All that does, though, is show the user one or the other fields from the same record.

So, I suppose I could be misunderstanding what you want, so please elaborate, in plain language, what the reason for this requirement would be.

Thank you.

 

[The_Doc_Man]

It would be possible, I suppose, to write a UNION query that provides both sources together and then dynamically modify the .ControlSource query to filter out the one you don't want to see. Or you could programmatically alter the .ControlSource to include the right fields after deciding which thing you wanted to see. The question from theDBguy seems on point.

Without knowing a bit more, I have to agree with GPGeorge that your question seems unclear. What are you REALLY trying to show by making that selection? The thing that really seems tricky here is that from your description, your combo will be showing one of two dissimilar record types. If that was not your intent, then be aware that your question led us to believe that, so must have somehow been unclear.

 

[ClaraBarton]

Sometimes a checking transaction gets split into categories, food, shelter, etc. But sometimes you want to transfer money to a different account, make an atm withdrawal which goes into the cash account. Checks are taking money out. Withdrawals just move it around. Either way, you need to put it somewhere. If you look at Quicken, the transfers are in the category list. Just a different type. I have a join table with TransactionID, CategoryID, and AccountID (all foreign keys. I want to check the cbo for type and put the ID into either category or account.

Private Function Split()
    If Me.cboCategory.Column(2) = "Transfer" Then
        Me.cboCategory.ControlSource = "TAcct"
    Else: Me.cboCategory.ControlSource = "fCategoryID"
End Function

 

[GPGeorge]

First, thanks for clarifying the context. Maybe you can show us some sample data to help us understand this process. Show us all three tables:
Transactions, Categories and Accounts.

This is an attempt to emulate Quicken, then. Is it going to do something Quicken doesn't already do?

Do you really need to invest hours of your time in creating a Quicken clone? I dislike the fact that Quicken has gone to an annual subscription model, but knowing how complex it is, I can't imagine trying to replicate it myself with Access unless I could find something I could add that Quicken doesn't already have. And that seems rather unlikely. It even retrieves bank data and investment data from online sources in real time. I can't begin to think how I could replicate that.

 

[ClaraBarton]

point taken. many times here. suffer me anyway.

 

[gemma-the-husky]

You could have two combo boxes, and hide/display the one you want to use etc.

Or, once you select the display option you want, simply change the text in the cbo title, set the query to the one you want, and requery the cbo box.

 

[gemma-the-husky]

How much of quicken are you writing..which ledgers are you doing.

I wrote a great NL in a couple of days, and a SL in a little longer. The SL was actually harder, which I didn't expect. You can have the NL if you want it. Accounting is straight forward. I've used the NL for my own accounts for getting on for 20 years.

I wouldn't use that tblTranactions with a debit and credit column. I would just store a positive or negative value in a single column. I can't see why you need both the tblcheckcat, and tblcategory. I can't really see why that sort of information is linked to the transaction. I would have thought it ought to link to the tblaccount.

Is tblaccount a NL account, or an AR/AP.account? if it's a NL account, the account number ought to be a string, not a number.

 

[ClaraBarton]

Yeah, I've been using it for some time too for small business and personal finances and I really like it but the time killer is every time I use it I think of something that might make it better and then waste half a day to save a few seconds. I'd love to see your program. I use mine for checking and charge cards and categories, etc. Investments I track with Excel. I've been told repeatedly to get rid of debit and credit and I usually end up combining them in queries but the accountant in me needs them.

 

[LarryE]

point taken. many times here. suffer me anyway.
View attachment 113762

It seems more logical to have:

1. Each tblAccount may have multiple tblCategory (Account Categories)
2. Each tblCategory may have multiple tblTransactions
3. Each tblTransactions may have multiple tblCheckCat (Check Categories to split transactions)

So:

1. tblAccount linked to tblCategory
2. tblCategory linked to tblTransactions
3. tblTransactions linked to tblCheckCat

 

[ClaraBarton]

Accounts do not have categories.
Accounts are Checking, ChargeCards, Cash, Undeposited, etc and have many transactions.
Transactions are divided into categories.

 

[Pat Hartman]

Reusing controls is just plain poor design. It will cause nothing but confusion as a line of code that refers to AcctID might actually be working with a Category.

 

[ClaraBarton]

I am not reusing controls. Money can be spent and categorized or moved to another account. How would you assign a check?

 

[Pat Hartman]

You are reusing the combo for two different fields.

 

[GPGeorge]

I am not reusing controls. Money can be spent and categorized or moved to another account. How would you assign a check?

Categorizing transactions (Food, Housing, Utilities, Entertainment, etc.) is a different kind of operation from moving money into, between, or out of accounts. You need two controls, one for each action.

By definition, a check is drawn against a checking account. HOW the money is used is a different question and doesn't depend on the account type.

 

[gemma-the-husky]

I am not really familiar with quicken. I thought you were talking about a commercial type accounting system

For my own personal finances I just use spreadsheets. I suppose it might be occasionally useful to analyse my expenditure by the supplier, so I can see how much I've spent at different grocery stores, but it's not really that important to me.

 

[gemma-the-husky]

Categorizing transactions (Food, Housing, Utilities, Entertainment, etc.) is a different kind of operation from moving money into, between, or out of accounts. You need two controls, one for each action.

By definition, a check is drawn against a checking account. HOW the money is used is a different question and doesn't depend on the account type.

This is really describing a double entry system, I think.

For a payment you credit your bank account and you debit the expense account. By doing both, the double entry is maintained and the total debits correspond to the total credits.

It's not so much that the two parts of the transaction reflect different processes. Every account has it's own characteristics. it's more that the system needs to make sure that both sides (debit and credit) are processed concurrently to keep the total system balanced.

As an aside, when you receive a salary you debit you bank/chq account, and credit you income. When you pay rent or a mortgage you credit the bank account and debit "mortgage payments".

So money in hand in your bank account is when you have a debit balance in your own accounts, (not a credit balance). The reason we think of it as being "in credit" is because a bank statement shows the bank's position in their accounts. We are their creditors, and they owe us money, so we get a statement showing a credit balance.

 

[GPGeorge]

This is really describing a double entry system, I think.

For a payment you credit your bank account and you debit the expense account. By doing both, the double entry is maintained and the total debits correspond to the total credits.

It's not so much that the two parts of the transaction reflect different processes. Every account has it's own characteristics. it's more that the system needs to make sure that both sides (debit and credit) are processed concurrently to keep the total system balanced.

As an aside, when you receive a salary you debit you bank/chq account, and credit you income. When you pay rent or a mortgage you credit the bank account and debit "mortgage payments".

So money in hand in your bank account is when you have a debit balance in your own accounts, (not a credit balance). The reason we think of it as being "in credit" is because a bank statement shows the bank's position in their accounts. We are their creditors, and they owe us money, so we get a statement showing a credit balance.

We basically agree.

My point is that:

1. A transaction consists of moving money from one account to another account. My checking account to your checking account to reimburse you for lunch; My credit account to the phone company's account to pay for phone service.
2. The category from my perspective would be "Reimburse Lunch" or "Phone Service for March, 2024".
3. The account type is Checking to Checking, or Credit to whatever account the phone company payments go into.
4. I could reimburse you via credit card from my credit card account and pay the phone company by check from my checking account, or EFT for that matter.

In other words, as I understand it, "Category" and "Account Type" are two of a number of attributes of a transaction, but not of each other. One answers the question "Why was this transaction initiated?" and the other answers the question "Where did the money for this transaction come from?" Still another could be "How was the money moved?"

It boils down to this. Trying to use one control (combo box) to handle both attributes interchangeably is simply not going to work. It is, to strain an analogy, sort of like trying to use a single combo box to enter either Hair Color and Weight, depending on the intent of the user at that moment.

With enough heavy VBA, I guess it could be done, but there are better ways to invest development time that overloading a combo box, IMO.

Quicken is probably the most widely used personal and small business personal finance software in the US. QuickBooks is the grown up version for businesses, but even basic Quicken can be adapted for light business related transactions, i.e. the kind a sole proprietor might need.

 

[ClaraBarton]

yeah. Actually, I've been an accountant for years. I get all this. The only reason I mentioned Quicken is because they list the accounts with the categories and split the money so. Accounts are a TRANSFER type and enclosed in brackets and Categories are either EXPENSE or INCOME type. I'll move on. Thanks for all your input.