Reload Form RecordSet

[gray]

Hi

I've a form set up where I dynamically build SQL strings and apply them as recordsources to various forms.

I switch form Rs's to a 'temporary' recordset when a record is being edited.... this way, if the user clicks my custom cancel button, I can simply re-set the recordsource to the original and throw away the uncommited details in the temp recordset. I've had this working for ages.

I've just hit a new problem tho' when returning from the temp to the 'permanent' recordset. Having reset the recordsource, all subsequent references to "me.form.recordset" (clone,recordcount etc) fail with

Error 3420 Object Invalid or No Longer Set

I reset the Rs like this:-

Rec_Src_String = "SELECT * FROM Addresses etc etc"
Me.Form.RecordSource = Rec_Src_String
msgbox me.form.recordset.recordcount <---- fails

I know there is a recordset in there because if I do a

msgbox Me!Unique_No

I get a valid record number???

Any ideas why it thinks the object s closed please?

Thnks

 

[missinglinq]

That seems like an awful lot of work to simply cancel an update and return the record to its previous state; you could have simply used Me.Undo in the custom 'Cancel' button. OR am I missing something here?

That aside, what's has changed recently in your app? You haven't, perhaps, upgraded Access, have you? Allen Browne has reported that 2007/2010 has a bug causing

[Form].[Recordset].[RecordCount]

to fail. He gives a workaround for the problem here:

http://allenbrowne.com/RecordCountError.html

Linq ;0)>

 

[gray]

Hi

Thanks I'll have a look at that link...

As for cancellling updates... I have gone thru' the Access mill on that times without nunmber... the problem is that the me.undo' , cancel=vbtrue's etc only work under certain circumstances.... I found that Rs's with joined tables often go non-updateable if bound to tables so the above mentioned techniques can't be applied anyway... I then thought of using disconnected RS's.. but found that adding, deleting and updating records in them gave them a migraine... sometimes it worked, sometimes it didn't, then I thought of switching between 'real' and temporary tables which in Access became a mangement nightmere, then thought of temporary fields in the 'real' tables, again a ton of code to manage them, then ... well you get the picture... When I discovered SQL TXs could be used I thought all my troubles were over....... actually, they had just begun.....!

 

[gray]

Hi

No luck with Allen Brownes suggestion I'm afraid.

I did upgrade to SP3 on Windows XP recently... I guess it could be something in there but needles and haystacks springs to mind.

Just a quick question please.... I populate my form with via an SQL string applied to the form's recordsource... e.g. "SELECT TBL1.* FROM Addresses AS TBL1". How do I kick the form to pick up any new records? It's not bound so me.requery or me.refresh won't work I don't think?? If I can avoid building the recordsource I will.

Thanks

 

[Galaxiom]

A form with an SQL RecordSource is still a bound form. Requery gets new records while Refresh only updates existing records.

Requery doesn't work on a form with an ADO Recordset. The requery equivalent is:

Set Me.Recordset = Me.Recordset