Automatically adding multiple records to a table.

[Roadbeer]

Hey guys,

I'm working on a budgeting DB and have been pulling my hair out for a few hours on this and I'm wondering if there is a way to do it.

I have 3 tables: Tbl 1 (Transaction), Tbl 2 (Account), tbl 3 (SubAccount).

What I'd like to do when the user opens the form it queries the number of their sub accounts and the budget amount attributed to them. Once they enter a figure in an unbound box, the form splits that figure based off of the number of and percentage of the sub accounts then automatically stamps a new entry into the Transaction table with a new record for each of the subaccounts.

[June7]

I am not sure how you want to calculate the split. This could get tricky because of rounding. Seems I used OTS accounting software (Quickbooks, Great Plains) and it handled this nicely.

Depends on data structure. What are the fields in tables?

Is form opened with table Account as RecordSource? Or is form unbound? Does user enter the Account number on this form? Is the percentage factor a value in table or must it be calculated?

Maybe in a button Click event something like:

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM SubAccount WHERE Account=" & Me.Account, dbOpenSnapshot)
rs.MoveFirst
varRemainder = Me!AcctBudget
While Not rs.EOF
varTranAmt = Round(rs!SubBudAmount / Me!AcctBudget * Me.textboxAmount, 2)
CurrentDb.Execute "INSERT INTO Transaction(SubAccount, Amount) VALUES(" & rs!SubAccount & ", " & IIf(varTranAmt < varRemainder, varTranAmt, varRemainder)
varRemainder = varRemainder - varTranAmt
rs.MoveNext
Wend

Might need some more code to deal with the rounding to prevent cumulative error.

Another issue is preventing duplicate insert of records by accidental multiple clicks of button.

[Roadbeer]

Thanks June, I realize I could have provided you with more information, but I didn't want to write a book to find out it couldn't be done.

The form will actually be a sub-form bound to the transaction table. The main form will have all the account/sub-account information and that will be passed in the parent/child relationship.

The fields in the tables, and feel free to make any suggestions on this are:
Tbl1: Pkey(auto), Fkey ( Pkey from Tbl3), amount, transaction type.
Tbl2: Pkey(auto), Username.
Tbl3: Pkey(auto),Fkey (Pkey from Tbl2), Budget Name, Budget amount (Field Size Double, format Percent, Decimal Places=0).

I created Tbl3 to keep Tbl2 from bloating as a user may have as few as 2 or a few dozen depending on what they want to budget to.

I have the main form querying tbl2 as a record source, but it is basically the intermediary between various sub-forms that are adding records to other tables not mentioned, all are updated with either a save/new, save/close button on the main form.

So, I have a couple follow up questions as I'm ham-fistedly learning my way through VBA.

1. In the second line I see you reference a query. Is this generated by the code, or one I create?
2. Would you be so kind as to thumbnail what the various lines of code are doing, I see this as having more applications for me in the future.

Thanks again

[June7]

1. This opens a recordset with the SQL SELECT statement (no Access query object involved unless you want a query to be the data source, I assume a table can be the data source).

2. The code opens a recordset and in WHILE WEND loop reads each record and uses values from record as well as form textboxes to write record to Transactions table. The varRemainder is a check to make sure apportionments saved don't exceed the amount entered on form. Might end up saving records with 1 cent amount or pennies might be dropped. I haven't done any testing.

Hard for me to be specific because don't know your db structure but the general logic might be relevant.

[alcapps]

The fields in the tables, and feel free to make any suggestions on this are:
Tbl1: Pkey(auto), Fkey ( Pkey from Tbl3), amount, transaction type.
Tbl2: Pkey(auto), Username.
Tbl3: Pkey(auto),Fkey (Pkey from Tbl2), Budget Name, Budget amount (Field Size Double, format Percent, Decimal Places=0).

question..
if Tbl1: pkey = 1, amount was 100

tb2
10 Al

tbl3:
had 2 records(is this right?)
1000,10, socks,50.00,50%
1001,10, shoes,50.00,50%

is table layout 3 correct.. based on the 50% you would know what to put in the budget amount field.
I guess I am trying to figure out the tbl3 a little more.

you budget 100.00 in tbl1
and your tbl3 is used to do the split always?

that code in vba is very quick.. but I am not sure of the (Field Size Double, format Percent, Decimal Places=0).

[Roadbeer]

I've actually added another table to the equation now, It seemed like a better way to handle things in case the user wanted to transfer between sub accounts or if they wanted to have multiple "Primary" accounts. But if I'm over-complicating things, or if things seem redundant/unnecessary please let me know. These are naked tables so data wont be lost.

All Pkeys are autonumber

Tbl1: Account: (Pkey, Account Name)
Tbl2: AccountSub: (Pkey, Fkey (Pkey from Tbl1), subAcctName)
Tbl3: Budgeting: (Pkey, BudgetName, Fkey (Tbl1 Pkey), BudgetPct (% to be dived into each deposit), Fkey(Pkey from Tbl2)
Tbl4: Transaction: (Pkey), Transaction Type (Default Value from form), amount, Credit (default value 0 from this form), Fkey (Pkey Tbl2)

So from this form, the script I'm looking for is to fill the fields for Tbl4.

To answer your question about the layout

During account creation Form for Tbl1, a new record is created on Tbl2 (Fkey Tbl1,Account Name from Tbl1) and on Tbl3 (AcctName from Tbl1, Fkey(Tbl1), 100% (default),Fkey (Pkey from tbl2). I added this to handle the remainders that didn't occur to me until June7 brought it up. I set the amount to 100 so the user can create/edit budgets later from a different form.
The reason I set % field to double/percent/0 was because I was getting weird results in previous attempts to calculate where I'd get 3500% ect. But if the code would run better with a long integer number, I can figure out the reporting later.

[alcapps]

Tbl1: Account: (Pkey, Account Name)
Tbl2: AccountSub: (Pkey, Fkey (Pkey from Tbl1), subAcctName)
Tbl3: Budgeting: (Pkey, BudgetName, Fkey (Tbl1 Pkey), BudgetPct (% to be dived into each deposit), Fkey(Pkey from Tbl2)
Tbl4: Transaction: (Pkey), Transaction Type (Default Value from form), amount, Credit (default value 0 from this form), Fkey (Pkey Tbl2)

tbl1 10, al
tbl2 100, 10, Natural Gas
tbl2 101, 10, Car Gas
tbl3 1, monthly, 10, 100%, 100

I am not seeing what your attempting to do for tbl4.
can you give me what tbl4 should look like from here. I can then maybe help you.

[Roadbeer]

It's getting the figure from an amount is typed in an unbound box, then the budgets from tbl3 split that amount and deposit it into table 4 so each budget has a balance. other forms and tables handle deductions, credits and transfers on table 4 as well.

so your example would read more like this.

tbl1: 10, Household
tbl1: 11, Al
tbl1: 12, Mary

tbl2: 100, 10, Natural Gas
tbl2: 101, 10, Electricity
tbl2: 102, 11, Al's Account
tbl2: 103, 11, Al's Car
tbl2: 104, 11, Al's Entertainment
tbl2: 105, 12, Mary's Account
tbl2: 106, 12, Mary's Entertainment

tbl3: 1, 11, Natural gas, 15, 100
tbl3: 2, 11, Al's Car, 5, 103
tbl3: 3, 11, Al's Entertainment, 10, 104
tbl3: 4, 11, Mary's Entertainment, 10, 106
tbl3: 5, 12, Natural Gas, 10, 100
tbl3: 6, 12, Electricity, 20, 101
tbl3: 7, 12, Mary's Entertainment, 10, 106
tbl3: 8, 12, Al's Entertainment, 5, 104

Table 3 is there so Either Al, or Mary can create and adjust their budgets accordingly (from a different form), but also be able to budget into the Household or each others discretionary funds. When Mary brings up the deposit form, it will query only those budgets she has selected, regardless if they're her's, the households or Al's. The remainder of the budget being dumped into Al's or Mary's accounts.

So, say during a cycle Al entered 800 and Mary 1000 into "txtTotalDeposit"
Tbl 4 would look like this.

Al's deposit:
tbl4: 500, deposit, 120, 0, 100
tbl4: 501, deposit, 40, 0, 103
tbl4: 502, deposit, 80, 0, 104
tbl4: 503, deposit, 80, 0, 106
tbl4: 504, deposit, 480, 0, 102
Mary's deposit
tbl4: 505, deposit, 100, 0, 100
tbl4: 506, deposit, 200, 0, 101
tbl4: 507, deposit, 100, 0, 106
tbl4: 508, deposit, 50, 0 104
tbl4: 509, deposit, 550, 0, 105

The 2nd column will always be deposit from this form and the 4th column will always be 0 on this form as it deals with credit transactions handled by a different form.

[alcapps]

question on tbl4.. why do you do 100 on the first row should that be 102?
and in a normalized world we would loose the Natural gas on tbl3 column. but it is ok for this demo.


tbl4: 500, deposit, 120, 0, 100
tbl4: 501, deposit, 40, 0, 103
tbl4: 502, deposit, 80, 0, 104
tbl4: 503, deposit, 80, 0, 106
tbl4: 504, deposit, 480, 0, 102
tbl4: 505, deposit, 100, 0, 100
tbl4: 506, deposit, 200, 0, 101
tbl4: 507, deposit, 100, 0, 106
tbl4: 508, deposit, 50, 0 104
tbl4: 509, deposit, 550, 0, 105

tbl1: 10, Household
tbl1: 11, Al
tbl1: 12, Mary

tbl2: 100, 10, Natural Gas
tbl2: 101, 10, Electricity
tbl2: 102, 11, Al's Account
tbl2: 103, 11, Al's Car
tbl2: 104, 11, Al's Entertainment
tbl2: 105, 12, Mary's Account
tbl2: 106, 12, Mary's Entertainment

tbl3: 1, 11, Natural gas, 15, 100
tbl3: 2, 11, Al's Car, 5, 103
tbl3: 3, 11, Al's Entertainment, 10, 104
tbl3: 4, 11, Mary's Entertainment, 10, 106
tbl3: 5, 12, Natural Gas, 10, 100
tbl3: 6, 12, Electricity, 20, 101
tbl3: 7, 12, Mary's Entertainment, 10, 106
tbl3: 8, 12, Al's Entertainment, 5, 104

Table 3 is there so Either Al, or Mary can create and adjust their budgets accordingly (from a different form), but also be able to budget into the Household or each others discretionary funds. When Mary brings up the deposit form, it will query only those budgets she has selected, regardless if they're her's, the households or Al's. The remainder of the budget being dumped into Al's or Mary's accounts.

So, say during a cycle Al entered 800 and Mary 1000 into "txtTotalDeposit"
Tbl 4 would look like this.

tbl4: 500, deposit, 120, 0, 100
tbl4: 501, deposit, 40, 0, 103
tbl4: 502, deposit, 80, 0, 104
tbl4: 503, deposit, 80, 0, 106
tbl4: 504, deposit, 480, 0, 102
tbl4: 505, deposit, 100, 0, 100
tbl4: 506, deposit, 200, 0, 101
tbl4: 507, deposit, 100, 0, 106
tbl4: 508, deposit, 50, 0 104
tbl4: 509, deposit, 550, 0, 105

[Roadbeer]

No, because if you follow what tbl3 is doing, that is Al, contributing to the Natural Gas account, just like on row 6 you have mary contirbuting to the natural gas account.
As far as normalization, table 3, that is what Al, and Mary are calling their budgets to the account in table 2. Al could have called his "house farts" as long as it get's to the same account

[alcapps]

what what does the 100 represent on the tbl4: 500, deposit, 120, 0, 100

if that represents tbl2 id 100 that refers back to tbl1 Household not Al. I guess I am missing that small fact..

tbl4: 500, deposit, 120, 0, 100

tbl3: 1, 11, Natural gas, 15, 100
tbl2: 100, 10, Natural Gas
tbl1: 10, Household

so what does the 100 represent?
secondly your right you can name tbl2 Natural gas .. to pass gas
but why do you need to repeat it in tbl3 just wondering..

I see where you put 11 which is Al but I guess I thought you were mapping the 100 to tbl2. That is the confusion. I don't know if tbl3 1,11 should be 1,10 or if 100 at the end is pointing to tbl2 from tbl3.. Once we get this cleared up I can give you some code to accomplish this task.

[Roadbeer]

I think the explanation of what I'm doing has gotten over complicated, so lets take two of the tables out of the equation.

Tbl1 Budgeting.
In this table, a user may have as few as 2 or a few dozen entries, depending on how they want to budget their cash out.
Pkey:UserNumber:BudgetPercent:ToAccount#
1 : 2 : 35% : 18
2 : 2 : 25% : 15
3 : 2 : 40% : 8

Tbl2 Transactions:
The user, for example, enters 1000 into an unbound box(txtTotal) then presses a button and the following new records are entered into Tbl2.
Pkey:TransactionType:Cash:Credit:Account:User
1 : Deposit : 350 : 0 : 18 : 2 <----- 35% of 1000 deposited into account 18 by user 2
2 : Deposit : 250 : 0 : 15 : 2 <------25% of 1000 deposited into account 15 by user 2
3 : Deposit : 400 : 0 : 8 : 2 <------40% of 1000 deposited into account 8 by user 2

The best way I can think to explain it would be that the On Click event runs a code that queries the budgeting table for all records that match txtUserID then divides txtTotal by the various numbers in the BudgetPercent column and adds a new record for each into the Transactions table. Because TransactionType and Credit columns are constants from this form, and it's getting the user from the txtUserID unbound box, the only variables are Cash and Account. So the code, in addition to working out the percentages would need to add the word Deposit, the number 0 and the users ID in the appropriate columns.

[alcapps]

AddRecords.zip

ok I hope this helps.. I did it two ways... one a sql way.. then a form calling a sub procedure on a button click.. either way you get the same results...

take a look let me know if this works for you.. I didn't add any validation. this is code examples only..

[Roadbeer]

OUTSTANDING!

Super simple and elegant. I'm a huge fan.

Couple quick questions, as you have 2 things in here I've never worked with (modules and append queries).

1. Would I be better off importing the whole thing and restructuring my DB around it (a lot of work), or is renaming somewhat safe as long as I keep your original to reference from?
2. I can pretty much see what the module is doing, my only question is what's calling it and the append query into use? I can't see them being referenced in the form or the tables. Mostly I ask this in case I ever have cause to use it again.

Thanks again. This last thing has been what's been holding my project up.

[June7]

Very nice, al.

1. adapt by whatever means you are comfortable with

2. As al said, this demonstrates two methods. The button on form calls the procedure in the Module. The code could actually be right in the button click event procedure. Nothing calls the query. Just open it manually.