Creating a temporary set of records

[BobBridges]

I've created some complex Access applications, but I'm mostly self-taught. I'm pretty sure that means I have surprising and disturbing holes in my education; I'm probably doing some things the hard way just because I never realized there's a better. This time around I want to ask first, so I can cure some of my ignorance.

I'm creating a bookkeeping app. To start a new journal entry I envision a form where I enter a date and description in the header, and in the detail section as many debits and credits as necessary. I figure the easiest way to do the detail section is to feed it an empty table; that way I can easily start new records (new debits and credits) just by typing into the new-record fields. Then, once the journal entry is built, I can push the button and have it poured into the journal and ledger tables.

I can, during the design phase, create EmptyTable in the database and have the form use that. But I want to be able to instantiate the form, so I can have more than one open at a time, and I don't want multiple instances of the form to be all using the same temporary table. So what's the best way to do this? I can certainly have VBA create a new table with a random name ("EmptyTable1037") at run time, and delete it afterward. But maybe it's better to use a recordset? Or is there another way?



Feel free to criticize from some other angle; if you're really itching to ask "but why do you want to do it that way in the first place, when you can...?" then by all means charge in.

[ItsMe]

The first thing I ever read about building data bases was the importance to plan your data tables out first and do the best job possible. If you do this, there should not be much need for temporary tables. I am not sure why you feel the need to store the data in a separate table to move it to its proper place at a later time.

If it is necessary to store your user's input temporarily, you could store the data in an unbound form. On that form you could employ a control to verify, compare, update, append, etc. The form can hold the user inputs in memory you declare when you build the form. For example, a textbox control on a form should have a predetermined data type whether or not it is bound to a table or query. When the user types data in the textbox control the form will have it available for use at a later time until you close the form or edit the data more than once.

[June7]

An unbound form could require lengthy code to save the entered data.

Why reinvent the wheel? There are so many easy to use and inexpensive bookkeeping off-the-shelf apps available. Like QuickBooks.

[BobBridges]

Why reinvent the wheel? There are so many easy to use and inexpensive bookkeeping off-the-shelf apps available.

Well, two or three reasons, but they're all the obvious ones:

1) For fun. I'm a geek, and I enjoy this sort of thing.

2) Because I don't want to spend money for an app and then discover all the ways I can think to improve it. If I write it from scratch, then when I think of an improvement I can implement it just exactly the way I want it to be.

3) Because if this looks like a challenge to me (and it does), then it means I still don't know enough about Access development yet. There are, as I said, disturbing holes in my education.

I cite #1 as the worst reason and #3 as the best, but for all I know #1 is actually the most compelling .

It occurs to me now, though, that if I buy one of those products you mention—and how inexpensive are they, anyway?—then whatever I use would probably give me additional ideas about writing a better one for myself. Something to consider seriously.

[ItsMe]

A few years back I used to pinhole into Peachtree. Now, you have to be a special partner and their software is more complex. Back then I did notice that they cascaded information from one table to another. After certain calculations were completed in one table, the completed records were archived as complete and the data would get written to another table via the primary key of the first table.

I have not done it yet, but QuickBooks offers a way to import data to their application via IIF. It makes perfect sense to build a front end application that talks to a back end accounting application like QuickBooks. QuickBooks can get updates for new tax laws and has all of the form templates etc. What QuickBooks and Peachtree don't know are all of your business rules.

What June mentioned about using unbound forms is true. Those are the options I see and they are limited. I would look at the feasibility of storing the data in permanent tables and using a Boolean to archive or indicate a record as complete. You could always run a year end clean up code or something. Just throwing ideas out there. I may take on the task of updating QuickBooks via IIF at a later date myself.

[BobBridges]

I am not sure why you feel the need to store the data in a separate table to move it to its proper place at a later time.

Well, I picture it this way: I start a new transaction. I type in debits and credits, however many are required. Maybe I think I'm done at some point, but my program says "wait a minute, they're not balanced yet; you have $1234 in debits and $2345 in credits", so I have to go back and figure out which number I fat-fingered. Meanwhile the phone rings, and the dog wants to go out, and I run out of time and have to get to a meeting. So I finish building the transaction when I get back ... unless I forget what I was doing and decide to start over, or have to reboot, or decide to cancel that transaction and do it differently.

BUT if I finish recording the transaction and hit the Accept button, THEN it can store it in the database. I don't want it in there until then.

(While I was writing this, you posted again with the possibility of having a field in the table that marks whether a record is pukka ("official", so to speak). Yeah, it's an option I'm keeping in reserve. I'm just looking for a better one, first.)

If it is necessary to store your user's input temporarily, you could store the data in an unbound form.

An unbound form could require lengthy code to save the entered data.

An unbound form would probably be my first choice if I was adding just one record at a time. But each transaction consists of multiple records, so the simple unbound form is out; and the actual number will vary—I don't want to set an upper limit—so I'm not thinking in terms of an array. What I figure will be most convenient is to bind the form to a temporary table, or a recordset maybe (I can do that, right?), and have Me.AllowAdditions turned on, which allows me to keep on typing in new rows until I'm finished without having to do so much work behind the scenes.

I'm still weighing options, and I'm still listening to your suggestions. But in all this we haven't yet discussed my original question. If I do go with a bound form, I want that form to be capable of multiple instances. In that case, is there a way to bind it to a table without multiple instances of the form conflicting? (Can a table be instantiated like a form?) Or is this a reason to bind the form to a recordset instead?—if indeed that's even possible.

[June7]

Maybe this will address the question of multiple instances of form: http://allenbrowne.com/ser-35.html

[ItsMe]

I think you are asking the right questions and you have a lot to chew on right now. Let us know if you hit a hurdle. The one thing that bugs me is deleting information the user inputs. For me, I would stick to the basic rule of storing the data in a table; maybe even time stamping the entries. It probably would be easier than designing around deleted tables. Over time you could analyze the data you believe is temporary and see what needs to be stored and kept as relative data then. You may discover a trend within the data that could be a tool at a later date.

[BobBridges]

Maybe this will address the question of multiple instances of form: http://allenbrowne.com/ser-35.html

I'm always up for reading more on this topic. But my present question isn't really about multiple instances of the form itself—I've already done a few of those—but about the tables they're going to store data in, to keep the instances of the form from interfering with each other. This article doesn't seem to address that, though it has some helpful things to say about the forms themselves.

I think you are asking the right questions and you have a lot to chew on right now. Let us know if you hit a hurdle.

I am letting you know, ItsMe: I'm asking about the best way to let these instances of a form write to a temporary table without them writing over each other's data. Use a recordset? Make up a different table name for each instance of the form? Or is there actually a way to instantiate a table, too? (I doubt it, but maybe there is.)

If no one has any ideas on it, I'll just experiment until I find something that works. I just thought someone else might have done this already and be able to tell me that there's a much easier way that goes like this... Oh, well, no harm in asking.

[ItsMe]

I have been thinking about the multiple instances of a form. Building out many tables to accommodate each instance seems to be cumbersome. I may approach this by using a global user ID or locking a row in a table for editing or storing the user input in virtual memory. So it is your unique approach that does not fit well with my past practices.

The three suggestions made here don’t really fit for one reason or another. Maybe there is a way to combine different techniques to get your solution. A global user ID doesn’t work because you seem to be the single user for this app. Maybe you could keep track of the many instances in a global module. Input from any one form could lock a row and place its respective identifier in said row along with a date time status as well as an active status. This would allow you to use a common table.

You could create a few or couple relative tables versus many many temp tables. A main form that is unbound could have some pulldowns or list boxes to create the unique identifiers. You could have a subform query your few tables and show it in DS view. If you index your tables well you can have your subform create new records on the fly, inserting the globally held identifiers and time stamps as you go.

A control button on your main form could query information and provide summaries in report or in another form. Another control could archive and import the appropriate primary keys from your few tables into your ledger table.

Using this methodology you could always review past work. You could reverse edits by searching time parameters, global ID's, etc.
This is just me thinking out loud. It is how I would approach it and it does not use temp tables. I didn’t want to steer you down a road you don’t want to go. Maybe you can use these ideas to incorporate the temp tables too. It is all just food for thought.

[BobBridges]

I'm with you on that; at this early stage I'm just musing aloud, trying to think over the possibilities.

What strikes me as the best way to do it, if Access provides the capability, is an instance of a table that is bound to an instance of the form and exists only while the form does, a table that does not appear in the database's list of tables. Like any other VBA object, such as a collection, it would exist only in RAM and only while the program was using it, could never be confused with a table bound to another instance of the form, and would magically disappear when the form does. Is it possible for a form to have a table as one of its properties, for instance?

If I cannot find a way to do that, I surely can create a table that would appear in the database's list of tables, yet would be explicitly deleted before the form goes away. I could create it using CREATE [TEMPORARY] TABLE, giving each instance a unique name based on a combination of the form and some random four-digit number, or after the form's unique window handle, or even using a sequential value tracked in my Datum table.

(I generally create a table named Datum in my applications; it stores information from session to session that routines can look up and/or update. For example, in my contact database the Datum table has seven rows; one of them has record key "LJSR", and the Datum field is the timestamp when the JSR was last run. The Datum table is a sort of miscellaneous catch-all, you see. Come to think of it, it's so useful, maybe everyone does that?)

Failing that—and I don't see why that should fail—I can have all the instances of the form use the same table, with a unique record key to keep them from trampling on each other's feet. That sounds more complicated to me, but I haven't tried writing it yet; maybe it wouldn't be.

And failing that, maybe I should just put the records in the main ledge table right away, using a flag (as we discussed) to show whether the records have been officially accepted. Or maybe it's time I learned how to use the normal, everyday database concepts of commit and rollback. I've never found it necessary to depend on them, so far.

Building out many tables to accommodate each instance seems to be cumbersome.

Yes, well, I'd do it in the Form_Open routine so it would be only as cumbersome as necessary to create one table and make sure it's unique from all the others.

....you seem to be the single user for this app.

Yes. I mean, if it's a howling success I could sell it for a bazillion dollars, but each buyer would be the sole user.

You could create a few or couple relative tables versus many many temp tables.

How is "a few" different from "many"? They both mean "more than one", right? Perhaps more interesting, what are "relative tables" as opposed to "temp tables"? That sounds promising...if I knew what a relative table is.

One thing I may not have made clear is that this temporary data, held in a table separate from the ledger, isn't going to stay there long, not even from one session to the next. Its duration is only the duration of the input form; I open the form, create a new transaction and hit Accept, and the Form dumps the accepted data into the ledger and deletes the table. There would be no facility for storing it and coming back to it later; if I have to go away, I'd just discard my unfinished work and start over next time.

[ItsMe]

The best approach to input multiple records into a recordset is to base your recordset on a table or query of tables. In your case where you only want to store the result or sum of the input, you can then delete the table.

I would use the example I gave above. The only difference would be to delete the table after your calculations are complete. You can create an unbound main form that would store values in memory while the user inputs debits and credits into a subform that is bound to your temp table.

When you ask about what I mean by having a few or couple permanent relative tables I am referring to the very basics of relational databases. Two tables share a relationship. Each table has a primary key. One of the two tables has an additional (not its own primary key) column that is indexed and holds reference to the other table’s primary key. This is a relationship that allows you to query relative data from the two tables. I imagine you could expedite data entry in your project by having a few or a couple tables specific to your credits and debits by indexing repetitive data such as vendors or purchase agents.

Back to your original question and considering the totality of this thread I would create a complex data base that managed the front end calculations. This front end would do everything you want to do in a temporary table and then some. I would then export data to a file that would later be imported to Quicbooks via an IIF. Quickbooks would be the backend data base and provide all of my reporting for tax, balance, PandL, payroll, etc. This is how I would approach it. You have your own set of obstacles I may not be privy to. So you need to use the info within and best apply the techniques to your situation.

Bottom line, like June mentioned in a very concise manner, using more than several unbound controls in any given form will require a lot of unnecessary code. Temporary tables are the best solution for your described desire.