In subform, Recordsource=Query (updateable) while =Recordset(read-only)

[tnjarrett]

Hello, I have a MS Access continuous subform that was using a query as the recordsource.

I changed it to use a recordset instead because when the query was used, the changes to the subform values directly changed the tables under the query. I wanted the changes to be 'in-memory' so that I could check them before applying them to the tables.

When I changed the subform to use the recordset, it is now 'read-only' and I can't change anything on the subform (bummer). Here is my creation of the recordset:

    Set rsLandSpecies = CurrentDb.OpenRecordset _
    ("SELECT LandingSpecies.cd_Document, LandingSpecies.cd_Species, " _
    & "Species.Description, LandingSpecies.Quantity, " _
    & "LandingSpecies.Num_Indiv, LandingSpecies.Price, LandingSpecies.Value " _
    & "FROM LandingSpecies, Species " _
    & "WHERE LandingSpecies.cd_Document = " & tx_cd_doc.Value & " " _
    & "AND LandingSpecies.cd_Species = Species.cd_Species " _
    & "ORDER BY Description", dbOpenDynaset)
    Set Me!SpeciesPerDocument.Form.Recordset = rsLandSpecies
 

Am i missing some parameter to allow editing on the recordset while it is in memory?

Thanks for any assistance with this.

[tnjarrett]

Bummer#2
OK - I found out why the recordset was not editable - it was my query. I changed it to be:

    Set rsLandSpecies = CurrentDb.OpenRecordset _
    ("SELECT LandingSpecies.cd_Document, LandingSpecies.cd_Species, " _
    & "Species.Description, LandingSpecies.Quantity, " _
    & "LandingSpecies.Num_Indiv , LandingSpecies.Price, LandingSpecies.Value " _
    & "FROM LandingSpecies " _
    & "INNER JOIN Species ON LandingSpecies.cd_Species = Species.cd_Species " _
    & "WHERE (((LandingSpecies.cd_Document) = " & tx_cd_doc.Value & ")) " _
    & "ORDER BY Species.Description", dbOpenDynaset)
    Set Me!SpeciesPerDocument.Form.Recordset = rsLandSpecies

and now the subform is editable. The problem is that I'm back to actually immediatley updating the values in the tables again though i want to do it 'in-memory' Would anyone know if i make a clone of the recordset and then assign that to the subform, would that be editable in memory only? Thanks for any advice. Cheers, Tony

[missinglinq]

You're making a load of unnecessary work for yourself! Validation, what you're referring to as checking changes 'while in memory' should be done in the subforn's Form_BeforeUpdate event. prior to saving the record. If mistakes are found, you can cancel the update and make needed corrections.

Return you Record Source back as it was originally and do the validation in the Form_BeforeUpdate event. This is pretty standard stuff. If you need help with the code, post back detailing exactly what you need to check.

Welcome to Bytes!

Linq ;0)>

[tnjarrett]

Hello Linq,

Thank you for the suggestion. That is not a good option though.

In the subform, I present many records in a continuous form (simulating a datagrid). I want to do the validation when the subform is exited (after all the records are updated). If I use the Form_BeforeUpdate event, validation is done after every record is changed. How would I do the validation when the subform loses focus?

Maybe I should use transactions? What do you think?

Thanks for any hints.

[missinglinq]

You wouldn't! Doing validation against multiple records at the same time just isn't done. If the user needs to make a correction to a record it should be done while that record's data is fresh in the user's mind, not after another five or ten or fifty records have been entered. Doing it as you currently are attempting is, to put it kindly, very poor design.

Linq ;0)>

[tnjarrett]

@missinglinq

Poor design, eh... OK - how would ensure that all the weights (entered by the user within the subform) of the individual species samples (Quantity) sum up to the Q-sample total in the 'Document' record? There could be anywhere from 5 to 30 species. Like I have said twice, the subform is a continuous form (like a datagrid). Do you understand the design now? Do you still consider this a 'poor' design?

Thank you for your help. Regards, Tony

[OldBirdman]

I read this thread as an attempt to mimic Excel using Access.
In Excel, one can make changes anywhere in the grid, and when satisfied with the various subtotals and totals, can then save all the changes. If a solution is not found, none of the changes need to be saved.
I certainly can understand the uses of this approach. I don't know how to do this, but either Excel can do some database-like operations, or Access can create an Excel spreadsheet to be used here, and then converted back to Access as a 'Save' operation.

[NeoPa]

@tnjarrett
Tony,

I'm sorry to say that Linq's right. That design is almost certainly going to leave you with problem after problem. That's why it's not done. Not simply because the more experienced developers have some strange form of snobbery about it. Most of us appreciate why it will naturally and unavoidably cause problems.

I'm sure you're not keen to hear that, but it's nevertheless true.

Good luck with your project anyway.