Data validation for multi record data entry

[Izzy]

Hi there,

I'm working on a project that requires a fairly flexible data entry form. To help illustrate why I’m going to start by describing the table that will eventually store the data.

The table is essentially a journal that records account transactions. Every time an account balance changes an associated journal entry is created.

For instance if I were to add $10,000 to Account#1 the entry would look like:
Account#1 +$10,000

If I were to later decrease the balance of this account by $5,000 the entry would look like:
Account#1 -$5,000

At any given time I can see the actual account balances by running a query that sums the amount and groups by the Account#.

Increasing or decreasing a single account balance is fairly straight forward. The issue arises when complex transfers take place.

For instance, if I wanted to transfer $2k from Account#1 to Account#2 and $2k from Account#1 to Account#3 the entry would look something like:

Account#1 -$4,000
Account#2 +$2,000
Account#3 +$2,000

As you can see this will result in 3 new records in the Journal table.

So first off, the data entry form must give users enough flexibility to add as many records as they require to complete a single transaction.

Next – prior to these records being appended to the actual journal table a number of logic checks must take place.

For instance:
Data Validation Rule #1 - The account (in this case Account#1) cannot be decreased into a negative balance.
Data Validation Rule #2 – The total Debits & Credits balance. In this case Account#2 & Account#3 are increased by a total of $4k. Account#1 is decreased by $4k. i.e. Total increases = total decreases.

Now that I’ve bored you all half to death I’m going to get into my actual question. How do I go about creating this data entry form? I don’t want any of these 3 records to be appended to the Journal table until the users specifies all of the appropriate lines & then clicks a command button. The command button click event will then trigger the data validation logic and thereafter launch a procedure to append the records to the Journal table.

My first thought was to create a disconnected/dynamic/custom recordset that users can manipulate. When they’re content they fire the click event. If all is well the records are then appended to the Journal and the dynamic recordset is dumped. My concern is I may be heading in the wrong direction. What are your thoughts on using the recordset approach? Is there are more effective way?

Thanks -Izzy

 

[grzzlo]

Hi Izzy,

I'm working on a similar sort of database myself. I used a table instead of a dynamic recordset because it was easier, though I'm not sure it's a better solution. What I did was create a table called tblTempSplits (maybe yours would be tblTempJournalEntries?) that stores the splits before the user actually commits them to the database. Then I create a subform that's bound to that table. The user then adds as many records as they want to tblTempSplits using the subform and then hits a command button to apply their changes to the database.

The second validation rule is really easy to check. Just do a sum of the "Amount" field in tblTempSplits and make sure it equals zero before applying changes.

The first one is more complicated (and not one I have to worry about -- it's OK for an acct to go into the negative in my system). The first thing that pops to mind is that you loop through the recordset in tblTempSplits and check each amount against your account totals, using something like

rsSubForm.MoveFirst
While Not rsSubForm.EOF
    If rsSubForm!Amount + Nz(DSum("Amount","tblJournalEntries","AcctID=" & rsSubForm!AcctID)) < 0 Then
        ' warn user
    End If
    rsSubForm.MoveNext
Wend

but, if you use a table instead of a dynamic recordset, I think you could probably just create a query that checks the amounts in tblTempSplits against another query of account sums and calculates projected totals. Then you just do a single DLookup (or DCount might be faster?) for any records in that query where the projected total is less than zero.

 

[Izzy]

grzzlo, thanks for the advice. I decided to go with the temporary (over dynamic) table approach. It was definitely a lot easier to program.

I like the concept of dynamic recordsets a lot. For some reason scratch pad stored queries & tables seem to bug me. In my opinion it makes more sense to handle such situations with runtime recordsets. That being said, it's a lot less hassle working with stored queries/tables & manipulating them via VBA/SQL.

What is the general opinion of the community? Is there merit to using dynamic recordsets to handle these type of situations?

Cheers -Izzy

 

[grzzlo]

I agree that temp tables are ugly. I skipped the dynamic recordset approach at first because it seemed like a lot of work and I wasn't sure it would work for me. Now I'm really glad I did because it has saved me a lot of trouble in the long run. The temp table is serving multiple purposes (different kinds of transactions get entered differently using different forms) and makes data validation really simple. I have stored queries based on the temp table that I check before adding transactions to the database. It may not be the most elegant solution, but it's really easy to implement and maintain.

Actually, now I'm sorry I suggested you use a temp table -- I'm curious to know how much work it would have been for you to use a dynamic recordset.