Adding subform-like data without updating DB

[tpolony]

I'm trying to come up with a solution where users can add/edit rows of data in memory only (the "many" part of a 1-to-many relationship, for example adding/editing several orders for a single customer), so that if they want to cancel, changes will not go to the database. I thought a listbox could work, although other suggestions are welcome. I couldn't figure it out with a linked subform, as updates always occur automatically as you move to each record.

After populating the listbox initially from a query of db data, I would like users to be able to go to an add/update panel, the results of which would then be updated/appended to the list box, without actually saving to the DB. Is there any way to update a multi-column list box manually this way (also deleting rows)? I would then need to be able to iterate back through the list to do the actual updates/additions that would be flagged and indicated as part of the list box's hidden row data.

Maybe I'm making this too complicated, but I just don't know how to get these multiple-row changes/updates to be reversible. I don't know if recordsets are the answer, but I only know the basics of recordsets (opening, moving within them, etc), and I thought that updates to them also go to the DB as you go along.

Thanks for any advice.

 

[willir]

tpolony

Great question!

Would an array (or arrays) work?

It may be a bit tricky, but get the array to emmulate your record set. Then if approved / confirmed, loop through the array to write to the table or tables.

I am not sure what your needs are, but arrays have to dimmed to a certain size. If you are not expecting a lot records, this may not be an issue. However, the neet thing about Access and arrays is that they can also be created / "dimmed" / redimmed and preserve your data.

I do see three possible draw backs -- if your code requires a control number of some sort, you may not be able to get easy access to it until after the update; I have never used an array with a combo or list box; I have found arrays and continuous forms to be a pain in the butt. It alsmost seems that you have to fake a continuous form by using fixed fields in a single page form, and use command buttons to advance or move back your position in the array (current 10 records, previous and next ten record)

Good luck
Richard

 

[tpolony]

Thanks for the suggestion. I will either try the "fake continuous form" approach as a popup panel, or I can try the array with a list box. I could figure out how to use the convoluted routine that allows you to populate a list box from a function (a new challenge), with the function reading an array that is populated from a recordset. Then when the user updates the list box via data entry panel, the array is updated and the list box is reloaded from the array. Tedious either way. I should figure out which one is less work! Thanks again!