Calculation using value in unbound text box (1 Viewer)

[Gavx]

My database is an attempt at mirroring the transactions that occur in my bank account.
My challenge is to identify the transactions in the bank not yet in the db and somehow bring them in.

The bank only provides a text export of the transactions which I link to an access table (tblImportNotClean).
I query tblImportNotClean and output the results to an empty table (tblImport).

For each record in tblImport there is a checkbox field. Checking this field means the transaction is not in my transactions table (tblTransactions).
I identify missing transactions by applying the formula; Items Checked plus the total of tblTransactions equals bank account balance.
Note, transactions are continually occurring in the bank account which won't appear in the db until I put them there.
It is this part I am asking for guidance.

I was thinking I could paste into an unbound text box (txtBankTotal) the bank account total, assign a variable to it and then run a calculation using VBA – something like txtBankTotal less the total of items checked in tblImport. Using this I can reconcile back to the current total of tblTransactions.
In this way I identify the transactions in the bank that aren’t in the db.

I don’t need to store the bank account total in the db per se…

Is this do-able?
Thanks for any help

 

[Ranman256]

you can put any formula in an unbound checkbox:

txtBox.Controlsource = txtDist/txtTime
txtBox.Controlsource = 3.14*txtAmt

 

[1268]

My database is an attempt at mirroring the transactions that occur in my bank account.
My challenge is to identify the transactions in the bank not yet in the db and somehow bring them in.

The bank only provides a text export of the transactions which I link to an access table (tblImportNotClean).
I query tblImportNotClean and output the results to an empty table (tblImport).

For each record in tblImport there is a checkbox field. Checking this field means the transaction is not in my transactions table (tblTransactions).
I identify missing transactions by applying the formula; Items Checked plus the total of tblTransactions equals bank account balance.
Note, transactions are continually occurring in the bank account which won't appear in the db until I put them there.
It is this part I am asking for guidance.

I was thinking I could paste into an unbound text box (txtBankTotal) the bank account total, assign a variable to it and then run a calculation using VBA – something like txtBankTotal less the total of items checked in tblImport. Using this I can reconcile back to the current total of tblTransactions.
In this way I identify the transactions in the bank that aren’t in the db.

I don’t need to store the bank account total in the db per se…

Is this do-able?
Thanks for any help

Still not real sure what "this" is but it's access so most likely. Can you make a text box = A variable, sure. It's me.textbox1 = how ever you contrive a value. Where textbox1 is the name of your forms textbox. U could bind the text box to a query where the check filed = no. That would be the sum of the transaction not in your db. No need to do maths. Do you need the total or are your only looking for the value of what is not in the db? Why do you need either and is there not a unique value in each record? Why the check boxes?



Sent from my SM-T813 using Tapatalk

 

[Gavx]

Still not real sure what "this" is but it's access so most likely.

access

Do you need the total or are your only looking for the value of what is not in the db? Why do you need either and is there not a unique value in each record? Why the check boxes?

I need to identify the transactions not in the db. Comparing the totals and noting the variance means I search for the latest transactions that total to equal the variance.
The checkbox selects the transactions to be added to the db.

 

[1268]

access



I need to identify the transactions not in the db. Comparing the totals and noting the variance means I search for the latest transactions that total to equal the variance.
The checkbox selects the transactions to be added to the db.

Well then you can create the variance by creating two functions one sums the in db lines the other not in db lines. Then a sub where the is variance = variance1 - variance 2 where 1 and 2 are the functions you created. You can write each function many ways but Sql is likely easiest. Or you could create a record set and make variance 1 = to variance 1 + variance1.

Make variance the control source for you subform and to form a query based on the textbox and set it to update with a button click. Should leave with a form who's records consist of only those records not in the db.

If there is no unique record you are stuck with his method but even if you have a transaction number there are easier ways to do this.

Sent from my SM-G950U using Tapatalk

 

[The_Doc_Man]

When I did this sort of thing many years ago, I kept a record of every check I had written, and I did not allow automatic drafts. Things got more complicated to track when I started getting direct deposits and making manually entered on-line payments. This was MANY years ago and was based on Access v2. Which pre-dates Access 97. And of course Ac97 is from 20+ years ago.

I simply entered the check info into my transaction list manually (because I used the check record assiduously) and then when a statement came around, I compared it to my list of pending checks. Once I started getting direct deposits, I still got a paper stub that showed the transaction date and amount, which was all I really needed.

If the transaction was posted in the statement, I checked a box (one per record) that said "Has appeared in a monthly statement." That's the nice thing about Access control labels. They can be wordy even if the field / control name is just "[Posted]".

When I got the statement, I also would verify the amounts of any direct deposit along with the date on which it occurred. That way, I could write a query sorted by date to show me the daily running bank balance and I could write a summation query that told me what I thought was the actual balance vs. what was the balance not counting the stuff not yet found in any statement.

Using a GROUP BY query, it was also possible to write a report that should have paralleled the actual statement. The bank's statement cycle was based on posting all transactions up to midnight of the last day of the month, so a monthly reconciliation was quite easy.

Because direct deposits and manual deposits did not have a check number but needed to be in the transaction list anyway, I use an autonumber for the master transaction record and put the check number as just another data field that was allowed to be blank.

My database is an attempt at mirroring the transactions that occur in my bank account.
My challenge is to identify the transactions in the bank not yet in the db and somehow bring them in.

...

Note, transactions are continually occurring in the bank account which won't appear in the db until I put them there.

As long as you keep a good check record you can do this. You know this but I will emphasize it: There is nothing to reconcile UNLESS you enter the data from your check record and then later decide when it has become "official." If you don't enter the data manually first, your variance is impossible to compute due to lack of data to support it.