Selecting only 1 check box on a subform

[Kavy]

Good day,

According to some threads on Google, this may not be possible, but they were not crystal clear.

I have a form with a control source of a query. The query is made up of Transactions. Each Transaction has a corresponding separate budgeted transaction in a separate table called budget transactions. One "Budgeted Transaction" may have multiple corresponding real transactions i.e bad example, but my monthly rent may be a 'Budget Transaction', and if I pay 1 months rent in two payments (again, bad example I know), and therefore it would have 2 corresponding real transactions.

Each budget transaction has a corresponding budget account on a budget account table. So in the monthly rent example, the budget account table would show "Rent", while the budget transaction table would show multiple records which are my future monthly budgeted rent dates and payments (i.e May, June, July, etc).

So back to my form, it's fields are for the actual transactions, i.e The Date, The Amount, The Vendor (i.e My landlords name), and the 'Budgeted Account' (i.e "Rent" not May Rent).

There is a subform which has all of the budgeted transactions for the corresponding budgeted Account on the main form, i.e if the budgeted account on the main form is Rent, it has multiple rows showing May Rent and Amount, June Rent and Amount, etc. Despite this, only one of those records is for the actual transaction on the main form, but since I tied the subform to the budgeted account, all of the budgeted transactions for that budgeted account are shown.

Thats the tricky part, the relationships in the database follow this process: Actual Transactions - Budget Transaction - Budgeted Account,<b> not</b> Budget Accounts to Actual Transactions, meaning the main form which shows which Budget Account corresponds to a Actual Transaction, is actual grabbing the data via a corresponding budget transaction.

<b>So heres the question,</b>I managed to make the sub-form shows all of the budget transactions for the corresponding budget account on the main form, but I want to add a radio button for the user to change which budget transaction to actual transaction should apply to. I.e. The budget transaction is a rent payment, and that is not allowed to change here, but if they want to change which month the payment was for, I would like to have a radio button on the subfrom to select the correct month, or in other words, the budget transaction. I would like the radio button to be preselected based on which budgeted transaction ties to the actual transaction on the main form (I think I can do this once the problem below is figured out).

<b>And here is my first problem</b>
When I put a radio button on the subform, it is un-bounded, and if I select it, all the radio buttons on the other records on the subform get selected, and vice-versa.

Any comments?

Thank you for any suggestions at all!

Kavy

 

[Kavy]

Got it!

This is tricky, and it performs slightly slow, so if anyone knows a better way, please let me know.

On the option/radio button, use the control source property with the following statment:
<code>
=IIf([Forms]![PDF Files]![Transactions subform]![BAccount]=[TransNum],"True","False")
</code>
Where Transnum is the unquie idenitifcation of the budget transaction, and BAccount is the textbox on the Main Form (it says subform because I really have 2 subforms, something I omitted from my question to keep it straight forward). The BAccount textbox has the visible property set to false.

That will have the correct radio button loaded when the form loads, and updates when the form updates.

Here is the tricky part, you have to use the "Mousedown Event". The "afterupdate and *******" events refused to load for me when I had a control source for my option button.

Using the mouse down event, write some code to edit the table which has actual transaction in it, to reflect the correct budget transaction, throw in a "Me.refresh" and your done!

<b> Does anyone know why I had to use the Mousedown Event instead of *******? </b>

Thank you!

Kavy

 

[Norie]

Kavy

It's seems to me there is something wrong with your design.

You shouldn't have to jump through all these hoops.

I can't put my finger on what's wrong and I don't have Access available right now to test with.

When I do I'll have a proper look and post back.

PS I think it might be something to do with having 2 separate tables for transactions.

 

[xenou]

I'm not enthused about the radio buttons for choosing which budget transaction an actual transaction applies to. Perhaps that should simply be based on date alone (nothing more). In accounting systems this is usually done via a Posting Date.

So a "real transaction" (at the time of entry) is given two dates - transaction date and posting date.

Example:
Account: Rent
Amount: $750.00
TransactionDate: May 21, 2011
PostingDate: May 21, 2011

The above would be a May-2011 rent because it has a May-2011 Posting Date.

-----------------------------

Let's so you paid your rent late because you forgot about it (though of course now you keep a good budget so you'll never forget again):
Account: Rent
Amount: $750.00
TransactionDate: June 7, 2011
PostingDate: May 31, 2011

Now this rent was paid on June 7 and has that for its transaction date (which matters for say, cash flow or checkbook reconciliation). But it still has a May posting date, so when you do your budget to actual comparison it will show up in the right "bucket" - may expense matched to may budget.

Personally, for a home budget I would stick to a strictly cash basis accounting system (for the most part). If rent is paid late you just have a favorable variance in May, and in June it straightens itself out. This way, in fact, you will see that there was a problem because when you look at your budget variances you'll see that Rent isn't right - and since its a fixed amount each month it should be a bullseye with no variances favorable or unfavorable.

ξ

 

[Kavy]

I'm not enthused about the radio buttons for choosing which budget transaction an actual transaction applies to. Perhaps that should simply be based on date alone (nothing more). In accounting systems this is usually done via a Posting Date.

So a "real transaction" (at the time of entry) is given two dates - transaction date and posting date.

Example:
Account: Rent
Amount: $750.00
TransactionDate: May 21, 2011
PostingDate: May 21, 2011

The above would be a May-2011 rent because it has a May-2011 Posting Date.

-----------------------------

Let's so you paid your rent late because you forgot about it (though of course now you keep a good budget so you'll never forget again):
Account: Rent
Amount: $750.00
TransactionDate: June 7, 2011
PostingDate: May 31, 2011

Now this rent was paid on June 7 and has that for its transaction date (which matters for say, cash flow or checkbook reconciliation). But it still has a May posting date, so when you do your budget to actual comparison it will show up in the right "bucket" - may expense matched to may budget.

Personally, for a home budget I would stick to a strictly cash basis accounting system (for the most part). If rent is paid late you just have a favorable variance in May, and in June it straightens itself out. This way, in fact, you will see that there was a problem because when you look at your budget variances you'll see that Rent isn't right - and since its a fixed amount each month it should be a bullseye with no variances favorable or unfavorable.

ξ

Thanks for the reply's Norie and xenou and for bearing with my madness.

xenou, would your budget transactions be in the database prior to the posting date, i.e set as future transactions, or would they only be inserted on the posting date?

Its funny you choose this method, because when I first started this database, a while ago, I had all of the information on 1 table, the only thing was I called it "Real Date" And "Budget Date". In this case, I had forecasted my "Budget Dates" and had the transactions pre-inserted into my database. This would cause a discrepancy between posting date and "budget date", as posting date is when it was actually charged, budget date is an estimate of when it would be charged (an easy fix by adding a field). I had a field called "open" which told me if the transaction was closed or not, i.e a budget transaction which is still open wouldn't count against the budget. This doesn't allow me to have multiple real transactions to the same budget transaction, whereas having a separate table for budget transaction it does. So in the case where I make to 2 payments to my rent in 1 month, for the same months payment (yes, bad example), the budget doesn't track the rent difference until I close the budget transaction, and since the budget transaction is on a separate table from the real transactions, I can have multiple payments for the same bill.

xenou, if your not pre-inserting this records into the database, how would you forecast future transactions?

Thank you

Kavy

 

[xenou]

Hi,
I would pre-insert budgeted numbers - typically, this is really one amount per account per month - the budgeted amount for that month.

At home, I budget at the beginning of the year for next year - so last December I laid out my entire forecasted spending for 2011. I'll repeat the process sometime at the end of this year and figure out what my expectations are for 2012, again filling in everything for the entire year when I do. This is hardest the first year because you have to figure out where all your money is going. With my current year budget, I *will* revise my it if something significant happens. For example, gas prices went up this year - so I had to change my expectations there. More significant changes would be in order if I had a job change, got a raise, and so on.

My budgets are in a separate table. To use accounting lingo, I have my general ledger transactions in one table, which is all "actual" transactions and are entered as they occur - which could be zero, one, or many per month. Rent would normally be once a month, for instance, but purchase of groceries about once a week. In another table I have my Budget transactions, and these are all only one record for each account for each period. Some may be zero in any given month. For example, I pay my car insurance twice a year so most months are $0.00 but two months are $X.XX. Since its a cash basis budget at home, I budget it when I expect to pay it - in fact, just last week, in May. The expense is really for the next 6 months (the period of coverage) but for my budgeting and planning its enough to know that its $x.xx per year and I will pay it twice a year. My monthly spending in May is a little higher, but my overall planning for the year takes this into account.

To compare Actuals to Budget, then, I only need to run a report by account, summing Actuals from the first table and Budgeted from the second table, and compare them side by side (account by account). I do this as a monthly comparison, but also, as a year to date cumulative comparison. It's the latter that is of more interest to me, but if I'm unfavorable on the year to date, it can be comforting to see if I'm doing better in the current month - i.e., catching up.

If you wanted to be more sophisticated, add a Budget ID to the budget table, and you can then have multiple budgets in the table (your 2011 budget, for instance, and later your 2012 budget). For home use, I think you probably don't need that. You'll just copy your 2011 budget and update it for 2012. When your ready, you'll just switch in the new one (after backing up your 2011 history and/or reviewing how you ended up). Another way to be more sophisticated would be to design it from the start to not be so tied to yearly dates - just rolling forward month by month. This is helpful in business as things change a lot and some companies actually do budget on a rolling basis, always 12 months out. At home, I think each year doing your planning once a year is usually sufficient.

ξ

 

[Kavy]

Xenou,

Thank you for all the replies and discussion - one which has been very fruitful for me! I think your right, it is key to budget for your your yearly expenses all year, i.e allocated money monthly for a payment coming in 6 months. I still have to set that part up, but I think I got a path forward.

Kavy