469,623 Members | 997 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,623 developers. It's quick & easy.

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

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:
Expand|Select|Wrap|Line Numbers
  1.     Set rsLandSpecies = CurrentDb.OpenRecordset _
  2.     ("SELECT LandingSpecies.cd_Document, LandingSpecies.cd_Species, " _
  3.     & "Species.Description, LandingSpecies.Quantity, " _
  4.     & "LandingSpecies.Num_Indiv, LandingSpecies.Price, LandingSpecies.Value " _
  5.     & "FROM LandingSpecies, Species " _
  6.     & "WHERE LandingSpecies.cd_Document = " & tx_cd_doc.Value & " " _
  7.     & "AND LandingSpecies.cd_Species = Species.cd_Species " _
  8.     & "ORDER BY Description", dbOpenDynaset)
  9.     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.
Sep 22 '09 #1
7 5299
OK - I found out why the recordset was not editable - it was my query. I changed it to be:
Expand|Select|Wrap|Line Numbers
  1.     Set rsLandSpecies = CurrentDb.OpenRecordset _
  2.     ("SELECT LandingSpecies.cd_Document, LandingSpecies.cd_Species, " _
  3.     & "Species.Description, LandingSpecies.Quantity, " _
  4.     & "LandingSpecies.Num_Indiv , LandingSpecies.Price, LandingSpecies.Value " _
  5.     & "FROM LandingSpecies " _
  6.     & "INNER JOIN Species ON LandingSpecies.cd_Species = Species.cd_Species " _
  7.     & "WHERE (((LandingSpecies.cd_Document) = " & tx_cd_doc.Value & ")) " _
  8.     & "ORDER BY Species.Description", dbOpenDynaset)
  9.     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
Sep 22 '09 #2
3,532 Expert 2GB
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)>
Sep 22 '09 #3
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.
Sep 23 '09 #4
3,532 Expert 2GB
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)>
Sep 23 '09 #5

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
Sep 23 '09 #6
675 512MB
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.
Sep 23 '09 #7
32,201 Expert Mod 16PB

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.
Sep 23 '09 #8

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

11 posts views Thread by Ian Ornstein | last post: by
3 posts views Thread by Simone | last post: by
1 post views Thread by Ig | last post: by
2 posts views Thread by Bruce | last post: by
1 post views Thread by rdemyan via AccessMonster.com | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.