Using an Unbound Text Box to Display Data

[JamesJoey]

I'm working on a budgeting db.
I want to create an unbound text box to display the current balance in my checking account.
I will need to change it periodically.
But, I have no idea what to set the Control Source to,

Any help will be appreciated,
James

 

[Uncle Gizmo]

What method does your code currently employ to calculate the balance of your checking account?

 

[JamesJoey]

No method.

I simply want to manually enter the balance.

 

[JHB]

No method.

I simply want to manually enter the balance.

Then place a unbound text control on form and type in what you want manually, it shouldn't be any problem with that.
The problem comes if you want the typed in value to show next time you open the form, then you need a table to store the value in it.

 

[sneuberg]

No method.

I simply want to manually enter the balance.

An unbounded text box has no place to store the data. I think you are going to need a table with a field for the balance and that would be the control source.

 

[JamesJoey]

I was trying to avoid creating another table bit I guess I'll have to.

So, for budgeting purposes, it would make sense to have two tables:
One for my expenses and the other for income??

 

[sneuberg]

So, for budgeting purposes, it would make sense to have two tables:
One for my expenses and the other for income??

I think that could go either way and it would depend on how you are going to use this. It could be one table of "Financial Transactions" with a field to indicate the type; "Income" or "Expense". That might make it easier if you want to have one form to enter both. On the other hand there's probably nothing wrong with two tables.

I suggest you think through what you want to do with the data and determine which one would make things easier.

 

[JHB]

I would use one table, not two.

 

[JamesJoey]

As apposed to a budgeting db, it would be better to call it a bill reminder db.
I want to track my bills with die dares and amounts.
Nothing as robust as a full fledged checking regisry.

I have a split format listing all my transactions for the month.

I would like to display my checking balance and the date of the balance in the form's header.

 

[arnelgp]

on your form's current event:

Private Sub Form_Current()
Me.yourTextBoxCheckBalance = DSum("[yourInField] - [yourOutField]", "yourCheckingTable")
End Sub

 

[gemma-the-husky]

do you mean you are trying to prepare a bank reconciliation

ie - enter the bank balance from your statement (1)

and then check that
the balance (1) agrees with your bank account balance (4) after taking into account uncleared payments (2) and uncredited deposits (3) (I hope these terms make senses - these are UK terms, and I think you are in the US)

----

FIRST WAY

if so, then you just need an unbound text box, with a format of currency (or fixed, 2DP's)

so you need to enter a value (1) and check that (1)-(2)+(3)=(4)

----
SECOND WAY

or you can just calculate (1) by using
(4) +(2) - (3)

ie the true balance (4), plus the payments (2) that have not yet reached the bank, less receipts (3) that have not yet reached the bank.

it's the same thing; I do it the second way.

 

[JamesJoey]

No, no.
Nothing so elaborate.

I simply have entered all my bills for the month into a continuous form with amounts and due dates for each.
As I pay each one online at my bank's web site I simply enter the date paid and the actual amount. (no sunning total)

I would like to display my balance indicated by the bank's web site at the top of the form. How to do this I have no idea.

 

[arnelgp]

try this tutorial:http://www.exceltrainingvideos.com/scraping-web-page-data-automatically-with-excel-vba/ and http://stackoverflow.com/questions/...ct-one-value-from-a-webpage-with-vba-in-excel

 

[JamesJoey]

I don't end to get the data from the Web page.

Simple go to the Web site to see my balance, then enter it into the balance field.

 

[gemma-the-husky]

I don't end to get the data from the Web page.

Simple go to the Web site to see my balance, then enter it into the balance field.

so just add an unbound control to your form, either formatted as currency or as number, fixed, 2dp's.

That's all you need to do.

 

[JamesJoey]

I've tried that.
The unbound text box loses the value I enter after I close and reopen the form.

 

[JamesJoey]

I'm using 2 tables now and am able to get the balance from the other table to display.

Problem is the former is now showing duplicates of all the data!?

I'm really confused now.

 

[sneuberg]

Could you upload you database so we could see how you have it set up. You could make a copy of it and delete the data before you upload.

 

[JamesJoey]

In the header I would like to display the balance and the date the balance was entered into the unbound text fields. (if that's possible.)

View attachment ExpensesCode.zip

 

[sneuberg]

You need a table to store the balance and the date but a single form can only have one record source. So the way to do this is to put the data from the tbPayees table in a subform. The attached database is a modification of what you had that demonstrates this. First I made a table (tbBalance) for the balance and date. Then I made a copied of frmPayeeList and named the copy subfrmPayList. I stripped this down until it only had the fields from the tbPayee. Then I made the frmPayeeList a single form, took out the tbPayee fields, made the record source the tbBalance table, made the fields from the tbBalance table the control sources for the applicable text boxes, and added a subform to the detail with the subfrmPayList in it. This subform is not linked to the main form as there is nothing to link them on. You might want to take the record navigation off the frmPayeeList as you are only interested in the first record.

For the attached demo I put the tables in the frontend. I'm curious. Why did you split this database?