Close subform on main form and NOT save changes

[burrina]

I have a main form named NEWAddProducts with a subform on it name NEWAddProductsSub. I want to make changes on the subform but NOT have it save them. I can use this code for the subform and it works if I ONLY open the subform, if I open the main form as I would normally with the subform on it then the code does not work. Is there a way to make this code execute on the main form? I select a Category on the main form then choose a product(s) on the sub and the qty.

Private Sub Form_Close()
Dim Answer As String

If Me.Dirty = False Then

Answer = MsgBox("Do you want save changes?", vbQuestion + vbYesNo, "Confrim")

Select Case Answer

Case vbYes

DoCmd.RunCommand acCmdSaveRecord


Case vbNo
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70


End Select

Else



DoCmd.RunCommand acCmdClose

End If

End Sub

[Rod]

... if I open the main form as I would normally with the subform on it then the code does not work ...

What exactly do you mean by not working?

Personally I would use the form's Before Update event along these lines:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Select Case MsgBox("Do you want save changes?", vbQuestion + vbYesNo, "Confrim")
Case vbNo
Cancel = True
Me.Undo
End Select
End Sub

[burrina]

Thanks, that works but of course it ask me after every product change. I want to be able make all my changes and then let it ask me, better yet, not even ask me, just don't save the changes.

[Rod]

It asks before every update of the db table, which occurs on a record by record basis. If you want to do this once for multiple records then a form bound to the db table will not work (barring using BeginTrans and Commit/Rollback which would be very wasteful, and impractical in a multi-user situation). You need to extract to a separate work table and add/delete/change there. Once finished you update the db or not according to the user response.

[burrina]

So, are you saying to create a new Categories table and Products table and use Unbound forms for these?

[Rod]

Not exactly, the form still needs to be bound to the temporary table. Here, roughly, are the steps to taken:

1. Clear (empty) and records from an existing work table;
2. Populate the work table with latest data (creating it if it doesn't exist);
3. Allow user to make changes to the work table through your subform;
4. At some event (?) scan the work table for changes and update the 'real' table(s) with these changes.

This is really a cumbersome solution; I have used it only once for a single-user application where the user needed to see 'before' and 'after' financial data across all open accounting periods. Consequently the work table was populated only with data from open periods.

This is not a suitable solution for a multi-user (split) database unless you are prepared to lock the original table(s) or handle situations where the original data has been changed by another user in the interrim.

You mention two tables, Categories and Products, so I guess your work table would be populated from a linked table query.

[burrina]

Yes, 2 tables. One for Categories and one for the Products for that particular category. Actually the main form is only linked to the products table. The subform on it uses a Dlookup to get the category and and corresponding products for it with price,tax rate and so forth. I think I have stumped the band here and maybe need to just leave as is. I have searched for a solution to the problem with no results and I know this is a common problem and someone has solved it and implemented it into a db. I will try the bound approach with a table I already have so I can experiment and see what happens. If I could put a subform that used another table and then have it thru a append query or something, this might work since it is the approach I am using now except that I use the real data tables instead. The False table(s) would not have to be locked and only when someone was updating at the exact same time as you would there be a potential for a lockout as I see it.

I think I will take this approach for now. It will look like krap though unless I can be creative with my design.

Thanks,

[Rod]

... I know this is a common problem and someone has solved it and implemented it into a db ...

I assume you mean the ability to confirm or reject updates across a set of records as one. Access, as explained, works on a record-by-record basis in an attempt to keep data as current as possible. Yes, it has been solved; I described my own solution that uses a temporary work table (actually it uses four such tables but the principle is the same). I shall also, out of interest, experiment to see whether it is possible to use the JET Transaction feature to solve this requirement.

Meanwhile I had to read your post a couple of times in order to understand what you are doing.

Actually the main form is only linked to the products table. The subform on it uses a Dlookup to get the category and and corresponding products for it with price,tax rate and so forth.

This seems back-to-front; the main form displays a product and the subform displays all products (including the one displayed on the main form?) within the same category as the procuct on the main form? You are allowing changes to the data (including the product displayed on the main form?) through the subform? Also, why is it necessary to perform a DLookup when the category could be retrieved with the main form's product and the subform linked on that category? What exactly is the user requirement for all this?

I can't help thinking that the requirement needs some more analysis. However the challenge to use JET Transactions with subform record updates is a worthy one. I'll report back.

[burrina]

do you have my db? I use a AddProducts form that uses the table Category as its record source and the subform on it uses the products table so that you can choose a category and see all the products associated with it. The category form (AddProducts) does NOT duplicate the products at all. You are getting lost here. 2 separate issues, forget about the categories and products.

On my main form which is named TimeCards it has a subform named TimeCardCatAndSub. Here is the issue. I need to add multiple products at a time. I can add single products right now and the db works fine excluding the multi item selection.


Take a look at the TimeCards form and then open the form FProductMultiItemSelect this is what I am after ONLY using the form TimeCardCatAndSub


Only exception is that I also need to be able to add a quantity for EACH item selected instead of a quantity for all as it is right now.


Let me know if this makes sense. THANKS for your help.

[Rod]

I am not getting lost. I and others have been in the 'maze' a long time; we know all the twists and turns; we know all the entrances and exits. I can only base my help on what you tell me so please try to be accurate and consistent.

OK, on to the immediate requirement.

It does not help that there are no table relationships defined so I have no idea of the interplay of your data; I have spotted at least one case where the data is not properly normalised - but we can live with that. Now the form, TimeCards, makes no sense:

1. The form is bound to a query, TimeCards, that joins the table, Time (bad use of reserved word!) to the table, Orders. This means the result set will contain a separate row for each Time-Order combination.
2. The way the table attributes are defined, I assume there are zero to many Orders for each Time, yet the join seems incorrect for this.
3. There can only ever be one Product for one Order (as given by the db design). So given 1 above, how can there be multiple products on the subform?

Something is dreadfully wrong with the data design and/or the design of the TimeCards form. I'm sorry to say that you need to step back and get your data design sorted out before attempting anything else.

---

As an additional comment: why the over complex way of adding to a subform list? Continuous forms come with a provision for adding new records; you seem to be reinventing the wheel. If additions have to be 'special' then there are better techniques than the one you use.

[Rod]

To close the topic about using JET Transactions with subform updates: it does not work. I tried opening a transaction upon entering the subform and then prompting for accept or reject on leaving the subform with commit or rollback as appropriate. There was no run time error but the rollback (and presumably the commit) was simply ignored. I tried for both the Workspaces and the DBEngine variations.

[burrina]

Good Catch, My apologies. I have been so busy with other problems I did not notice the bad relationship for the form TimeCards. The problem is that I pulled this from another db I wrote and was modifying it to fit. I have since removed the orders table from the query and added Freight and OrderDiscount to the Time table and ran an update query and all is well.
I have also been adding demo databases on here with removed tables,etc for size restrictions and maybe I have not fixed everything on them. Again, my apologies. As for the name of the Time table, I will change it to TPurchaseOrders. Although this never caused any problems I will concede that I am the student here. The spaces and other bad design stuff I have been correcting along the way.

NOW, this brings me back to my original problem. I am happy to say that I have made some good progress with this. I can now add multiple items form different categories and have them added to another table. I am still left with the problem of setting a quantity for each item selected. Right now it is one quantity for all.