temorary recordset or records?

[vicsaccess]

I have an ideal and need yourthoughts, experience and guidance. The business model you could call it, wouldbe that the user takes a product that we'll call the parent, and processes itseveral parts that we'll call the children.



The entry form is a form based ontblparent called frmparent with a sub form based on tblchildren calledfrmchildren. The current way this is set up allows null values in the tables. IfI add the checks for errors and nulls it takes longer than I like.

What my thought was, is that boththe form frmparent and its subform frmchildren be unbound and have a savecommand button that looks at the frmparent record set checking for errors andsaving it. Then captures the PK as a FK for the subform. It would then go throughall the records from frmchildren checking for errors and loop thru them andsave. I know this is sounding odd the way I’m describing it but I think what I’masking is- can I hold the record of the parent and lets say 10 records ofchildren, in a temporary spot or memory for a short period until the userfinishes producing the parent, then checks the records and saves them bothparent and children at the same time?

[Micron]

I know it can be a lot of work validating an unbound form/subform combination. Your post title doesn't nail down (for me anyway) what you mean by the second option. Thus I might be suggesting here what you're already referring to, which is to use two temp tables and bind to those. That way, you get to make use of table and form control level validation, and if need be, can dump the record rather than append it to the main tables. It also allows you to edit records and not affect the main tables if the operation goes bad. Naturally, where you'd put these tables has to be conducive whether or not you split and shared (or not) the db. In a shared case, you must plan to affect only the records applicable to a given user. I know many don't believe in a shared back end, but just sayin'.

[vicsaccess]

thanks micron, you basically have the ideal that's in my head. do you know of any references or examples i can look at to start working on it?

[Micron]

Not really, no. It's not much different than working on ordinary form based on a table or updatable query, and the temp table should have all the same properties as the main. I have done this in the past, but to extract the code would probably raise more questions than it answers for you. I've found the best approach is for the form to have a Save and a Save & Close button as well as Cancel - probably simple enough concept to grasp. However, I have found the need to have boolean variables at the form declaration level that captures if the form or subform is dirty. As I recall, updating either a subform or main form can save the other record as well make either to be not dirty. Thus when I tried to trap whether or not there was a new record to be saved, or warn if Cancel was chosen after edits to either the main or sub, or simply close an unchanged form, I could not rely on checking the Form.Dirty property. Hope that helps some.

[vicsaccess]

again thanks, if i'm understanding correct you actually have a table called tempwhatever and you use it as a storage and corrections till update and then delete from the temwhatever table. in my unexperianced still drinking from a fire hose mind i was wondering if there was a dependable way of holding the information in a temp memory so to speak and then update to the table but this sounds more dependable.

[Perceptus]

Holey moley can I buy some punctuation! Still trying to read the first post. got lost 3 times.

[Perceptus]

I have what you have going on. Took me a long to get it going. but basicly.

I replaced most of the basic access interactions for updating with my own set of class libraries.

These libraries handle all the saving of information, Bound and Unbound managed. If I make a new unbound record that needs to be saved. the class library handles all of that. I think i have post about this some time ago. With having foreign associate keys within a associate table to handle the relations.

[vicsaccess]

Thanks Perceptus, sorry for the original post. i had been reading so many papers and such trying to research new methods to me that i guess reading it now, my mind was probably ready to put it all away for the night. if you happen to know the link to the post or other information on this please send it my way.

[Micron]

perceptus, thatsoundslikealotofwork upfrontbutI bet it'squiteuseful downtheroad.

[vicsaccess]

Man, i can't catch a break can i.

[Perceptus]

No worries Vic, i murder posts all the time with my own attempt at english.
https://www.accessforums.net/showthr...133#post294133 is the post you want. i think.


Micron is a TON of work. But 1 year down the road it is all the amazing.

[vicsaccess]

I didn't see much at the link other than a few comments with june about working around changing table structure. Actually i did have some clarity this afternoon and re-read the original post and it actually seemed ok to me.
my thoughts on my desired way of holding the data temperarily until the user has all the data complete and correct sounds like its doable. if i rethink my plan on doing this form, based on the comments the two of you have made and a little more research, would be to have my two existing tables so clearly described above and then also have another pair to be used as temp tables. The form and subform would be bound to these temp tables for users entry. When the user was satisfied that they had completed processing the parent product into its individual pieces they would then have the ability to edit, clear or save the record. I could then at the time of the save command, check data for accuaccy, run a SQL or recordset to move the data to original tables and then run a delete to clear the temp tables for the next entry. Not sure if this is what the two of you was talking about but i kind of like it because it would give me the ability to maybe have several pairs of temp tables. this would give the user the ability to do data rntry ahead of time of production. Not sure if in this case it would be better to put each pair of tables on a tab control or just control a filtered recordsourse from a forward and back button. Any thoughts or concerns?

[Micron]

check data for accuaccy, run a SQL or recordset

Or simply open an update query or append query. You'll need both if you're allowing edits, so why write to open an rs or sql statement when a canned query will do?

then run a delete to clear the temp tables for the next entry

Maybe if you're forcing user to close the form. Otherwise, just allow all the new OR record edits on the temp then move the record. The issue with this whole approach is having to determine what the form mode is - new or edit, so you know which query to run. Maybe someone else has a better idea. I've done it this way, along with form fields that if edit mode, had to be locked as there were identifiers that could not be changed once created, but it's more work.

put each pair of tables on a tab control

I like the form/subform setup better than having to flip between tabs to work on a one-to-many relationship. But if you're talking about a bunch of these setups, maybe something else is amiss if your design is going to duplicate just about every pair of data tables as temps.

[orange]

vicsaccess,

Can you give an example or clear set of requirements in plain English of what you are dealing with?

I saw the Product and Parts reference in the first post, but it got lost in various attempts to solve something --form, subform, a pair of temp tables etc --before a clear requirement was described.

I looked at the link Perceptus gave and I didn't see anything specific either. Could be a message there, but I didn't see it.

Then, your statement

Not sure if this is what the two of you was talking about but....

adds a little more uncertainty.

So if you have an example; or step by step clear requirements, then other readers may offer some options or advice. It seems to me that there are a lot of comments re How, but nothing concrete on What is to be solved.

Any way just some comments in hopes of clarifying the issue.

Good luck with the project.

[vicsaccess]

Man what is this, pick on Vic day, I’m starting to feel abused. Ok actually I do understand, my original post was very vague partly due to the hours that I had been staring at this screen but also because as I’ve mentioned before, I’m learning as I go. I don’t have a problem with the learning or doing the research but in some cases I don’t know what’s available to do until I ask some of these strange sounding questions of the more experienced here. Actually I’ve learned a lot and been pointed in many new directions of learning either by mine or someone else’s inquiries.

The other part is that in my business we do production that not everyone understands and sometimes I can take that for granted with the number of years I’ve been there. The short version is that we take a product with its own unique ID weighing about 45,000 pounds and produce it into many, many small products each with their own unique ID’s.

For the clarity of description let me go a different route, let’s build a few sandwiches. If I have a loaf of bread, I open it and make 10 sandwiches. Right now my forms are based on the tables, a main form to track the loaf of bread and some characteristics about it such as density and thickness. On this form is a sub form to track the sandwiches and information unique to the sandwich. Everything runs great and each machine puts out many,many loafs of bread per day. Oh, and I have about 20 of these sandwich making machines across the US. running 8 to 24 hours a day 5 days a week.

Now for the most part sandwich making is rocking along with only a few glitches, but I can normally take care of those at the time. All is well until the end of the month and the monthly reports won’t run, queries start spitting out errors to the sandwich shop managers. Most of the time the problem is that some sandwich machine operator during the month has neglected to put down what brand of mustard he used. Thus a null or some other wrong data in a field.

Now I know what you’re thinking, put checks and traps in the entry form to watch for this, add functions like NZ and IIF’s and many others available for this to stamp out these errors in my queries. I have but you know what, at some point, rather than continue to add more things that slow down the operator’s efficacy to input or the managers queried reports with more checks and balances you just have to admit you need a better mouse trap, still with me. Basically let’s engineer my problem away.

What my original thought was, can I have this situation of form and sub form with records that are not bound to my tables as they are now, holding in sandwich making limbo. Similar to the relationship between RAM and the hard drive. Nothing is permanent that is held in the RAM, it becomes permanent when its checked out for errors and given the command to go live on the hard drive. Still with me, how’s my use of those comma’s. So what I was asking was, is there a random access memory within VBA for records that could hold data until all thesandwiches are made from the loaf of bread and then looked over all at once by both the operator and code before saving.

From that misworded original post, from the replies and research I was doing at the same time the idea came about to use the advantages of a real physical tables in our favor. I just need to create two new tables, calling them temp-table and temp-subtable. Bind my entry form to this new setup and use them as a storage point for the operator’s entry till the loaf of bread is completed and he knows how many and what type to update. At that point he has the option buttons to cancel it, edit it or save the record. Here’s where the new sandwich magic happens, recordset or SQL statements add all the records from the two temp tables and then deletes the data from the temp tables making ready for the next loaf of bread.

Ok, this is where it started getting real strange. My mind was thinking of all the new possible sandwiches that could be made. Actually what hit me like a ton of bread was something the operators had requested some time back but at the time wasn’t sure of the best way to handle it. I can duplicate this new combination of temp-table and temp-subtable and give each new pair a unique number such as temp-table1 and so on till I have 6 pairs of tables as temp. The operators then would not have to rush into their updates and could have the ability to work ahead entering the next few expected loafs of bread while at the same time having a few stacks of sandwiches being checked before the records are saved and moved into the permanent forms. Now my question was if all this is making sense would it be better to put each form/subform on a tabcontrol with 6 tabs or just have a main form with 6 numbered buttons that filter the record source to the appropriate temp tables.

Ok, sorry but that was fun. That’s basically the ideal just put to a warped punctuated flow. What I’m now interested to know, am I on a known path or am I off my rocker? Do you, the experts here in the land of Access use tables like this for temporary storage and what is the best method of updating and clearing out the tables, I would think a recordset, everything I’ve read puts them as handling data updates faster, and I need to get more comfortable with them. Hey I hope this is making a little more sense now so everyone will quit bullying me.