Adding Records to a SubForm

[gray]

Hi

Access 2002
Windows XPPro Sp2

From the hours of research I've done today, I seem to be in good company, this is obviously an area of confusion for most of us.

I have a simple subform (i.e. no Record Source with 5 or 6 unbound fields). I am extracting some records using a recordset and want to write that resulting recordset into the subform (and then display it in datasheet view). I tried a number of methods from the posts I've read in order to create new a record(s) in the subform (I need to add many new records in a loop) including the following:-

RunCommand acCmdRecordsGoToNew
DoCmd.GoToRecord , , acNewRec
me.mysubform.form.newrecord

I've carefully set the focus to the subform and subform controls.

I want to avoid the recordset.clone method as I want the user to be able to cancel out or "undo" any changes they've made. That is to say, when they hit a "save" button, I'll permanently write the records back to the table myself .

So far none of the methods I've tried have worked for me and I've tried so many times I've now confused myself as to which did not work and for what reason!! Sometimes I got the "you can't use this now" error, other times I got syntax problems..... I've been programming various computers for nearly 30 years now and I am just about to give up with VBA / Access hah!

Does anyone have any reliable code that will work (for Access 2002) please to perform this simple operation (I thought!) .

I need to go and open a number of beer cans now.....

Thanks as always

 

[wazz]

if there's no record source (and your controls are unbound), there is no recordset to work with and no new record to go to. you can copy values to your controls and save them back as i think you said but if you want a recordset to work with you have to set the row source of the form first or work with one set of values at a time. hth.

 

[gray]

Hi

Thanks for your help.... I had a horrible feeling that was the case.... so if one wants to display a list of data in a subform that is bound to a table and to let the user tinker with the data in the datasheet view before it is saved back, do you know what is the best practice for acheiving that? ... I want to let users make use of an "undo" and canel button I've implemented for all the other fields on the main form

 

[qdogfball]

A couple of things to play with..

Could you use a temp table for the subform and then when they click on "save", you update the real table?

Could you just use a simple "Are you sure that the data is ok?" popup asking to click Yes or No. If they click yes, let them go on, if no, keep them on the subform. Not a great way of checking, but simple if you have competent people.

("What? Competent? Sheesh!! Stupid Yanks!" )

 

[gray]

Hi QdogFBall

Thanks for the reply.... I like the idea of the temp table...in fact I started work on same but then I discovered the concept of "disconnected recordsets" bound to the subform.... Obviously, having followed Msoft's example as near as I can, it doesn't work! Well to be fair it does partially.

The idea is to create a recordset, set the form's own recordset to that set and then change the connection state to "nothing". You can then alter the "local" recordset at will before changing the connection state again and calling a batch update. Hey presto! Your records are saved back.

So far, I can do all this provided I contain it all within the onLoad event of the form. I moved the re-connect and batch update to the click event of a button but it fails with an object problem... I don't think the recordset is persistent outside of the onLoad event maybe.

Anyway, If I fix it I'll post further details... seems to me there's lots of uses for it.

Rgds

P.S. Don't worry, it's not just Yanks that can act stupid.... we've plenty of em' here too.... and far less room in which to avoid em' !