Thanks for the links. I'm familiar with the first 2 and particularly with Roger's work as he has some very helpful examples of subforms. Though this is my first db I've spent significant time researching and think I have a reasonable grasp of the basics - I will upload some stuff soon so can get into specifics.
Edit Junction Table - Unbound - VBA
- Thread starter CJ.B
- Start date
Back to this after a few days.
I believe I’ve resolved this issue by deleting the entire record set (for a given Order) in the Junction table and then replacing it with new data from the form for Orders that need to be edited. The original “parent record” in the Orders table is not affected and the new replacement junction rst is re-linked to it via the PK as normal.
I have been testing this in a demo db which I have attached (note the form itself is bound but the PK in the Orders table is the only bound field). It seems to work. I would appreciate feedback on whether this approach would be OK (note, this will be a very light use db with only one user at time.) Also, I know the code and methods etc. are not the best (to say the least!) so any other pointers, snippets, suggestions etc. would also be appreciated.
As well, if anyone wants to modify the demo db would be great. Thanks for your interest.
I believe I’ve resolved this issue by deleting the entire record set (for a given Order) in the Junction table and then replacing it with new data from the form for Orders that need to be edited. The original “parent record” in the Orders table is not affected and the new replacement junction rst is re-linked to it via the PK as normal.
I have been testing this in a demo db which I have attached (note the form itself is bound but the PK in the Orders table is the only bound field). It seems to work. I would appreciate feedback on whether this approach would be OK (note, this will be a very light use db with only one user at time.) Also, I know the code and methods etc. are not the best (to say the least!) so any other pointers, snippets, suggestions etc. would also be appreciated.
As well, if anyone wants to modify the demo db would be great. Thanks for your interest.
- Local time
- Today, 18:33
- Joined
- Jan 23, 2006
- Messages
- 15,554
A couple of observations based on a quick look at your database:
Your relationships are typical of altering a M:M to 2 1:M relationships
Your tbl2_Junction is normally called OrderDetails (or similar).
It seems to me that when you have an Order, OrderDetails and Product, you also have a Customer and/or Supplier. This seems to be missing from your relationships. As mentioned earlier, you seem hesitant to provide details, and you may have accounted for these other "entities" somewhere.
Another point is that if you rely on (Product) Price in the Product table to calculate Order Total Cost/Price, you will risk changing the Price on all historic Orders. A better technique is to include the "AgreedToPrice" in the OrderDetail record. Usually this is the unit Price that was Agreedto for this Product on this Order for this Customer. The advantage of this approach is that you can change the Price of the Product in the Product table without affecting historic Orders. Also, it allows you to have clearance, sale items, loyalty program etc without having to have multiple prices in your product table.
Good luck.
Your relationships are typical of altering a M:M to 2 1:M relationships
Your tbl2_Junction is normally called OrderDetails (or similar).
It seems to me that when you have an Order, OrderDetails and Product, you also have a Customer and/or Supplier. This seems to be missing from your relationships. As mentioned earlier, you seem hesitant to provide details, and you may have accounted for these other "entities" somewhere.
Another point is that if you rely on (Product) Price in the Product table to calculate Order Total Cost/Price, you will risk changing the Price on all historic Orders. A better technique is to include the "AgreedToPrice" in the OrderDetail record. Usually this is the unit Price that was Agreedto for this Product on this Order for this Customer. The advantage of this approach is that you can change the Price of the Product in the Product table without affecting historic Orders. Also, it allows you to have clearance, sale items, loyalty program etc without having to have multiple prices in your product table.
Good luck.
... all the points that jdraw mentioned plus it's over-engineered as plog and myself stated.
Thanks for the feedback, especially the note about "AgreedToPrice" - I will follow up on that. And again, the idea that unbound forms are a lot of work is not lost on me, only used as I wanted the UI.
Having struggled with this thought I would post a couple of links in case anyone is interested:
Thanks to Mike375 in this forum for post # 7 on displaying a custom record counter on a form =[CurrentRecord] & =Count(*) -- helped a lot!
http://www.access-programmers.co.uk/forums/showthread.php?t=164220
Best overview I found on record sets / unbound data entry -- Lessons 22 & 23
http://functionx.com/vbaccess2010/Lesson22.htm
Having struggled with this thought I would post a couple of links in case anyone is interested:
Thanks to Mike375 in this forum for post # 7 on displaying a custom record counter on a form =[CurrentRecord] & =Count(*) -- helped a lot!
http://www.access-programmers.co.uk/forums/showthread.php?t=164220
Best overview I found on record sets / unbound data entry -- Lessons 22 & 23
http://functionx.com/vbaccess2010/Lesson22.htm
