Form with subform: The main form updates the table when the user has not validated

[Demerit]

I have a table in my DB named TblStock and another named TblStock2

I also have a form in the DB named "frmStock" with the control source being "TblStock" . This form has a sub form "FrmStock2Subform" with control source being "tblStock2". The two forms are lined by "PurchaseID". This form is made to be used in entering products that have been bought for replenishment of stock.

The problem I have is that when I enter data in the main form, by the time i click on the subform to start entering data, the table "TblStock" gets already updated.

Now lets assume somebody was about to enter data in the subform and later on changes his mind when the "TblStock" had already been updated, this will cause a waste of useful space because there is no corresponding record in the table "TblStock2". Secondly, if many users are entering data into the DB using that same form, I guest there will be some problems too.



What i want is that the form should update both tables when the users validates the records. Here attached is part of my DB

Thanks in advance for your assistance
Business2.zip

[ItsMe]

Maybe you can revisit how your tables are structured. Why would any single user need to update more than one table at a time? If a client provides, let's say, a purchase order for services, then the PO table would get populated with a new record. That is one table that get's updated. Then another table would get updated to "Detail" the services requested within the PO, creating many records in the Jobs table or Detail table. In this scenario there is not any write conflict. Only one table at a time will need to be edited.

You can apply this matrix or topology to inventory too. Instead of a PO, you would have a packing list. Instead of a Detail table, you would have an inventory table. Using PK's and FK's will insure referential integrity.

[Demerit]

Thanks for your reply ItsMe,

In the sample DB i have uploaded, it seems to be structured the way you propose. I have PurchaseID as PK in the table "TblStock" and as a foreign key in the table "TblStock2".

The table "TblStock2" is made to contain details of the items purchased at any given moment. Do I need to create two different forms or what do you think of the form "FrmStock" found in the DB uploaded.

On the Before update Event of the form, I have attached a VBA that automatically creates a PurchaseID.

I will be waiting to hear from you.

[ItsMe]

Why are you trying to control when the record is saved? You have auto number fields. Why not use these fields and not waste time and energy in "Saving" records? I don't see a problem with having a new record created in tblStock every time a User decides to enter a new Purchase Invoice.

It would be less hastle to delete a record that was created by mistake than to "Save" a record that just happens to be a good one. There are going to be more good than bad.

You have referential integrity enforced in your relationship with tblStock2. With this, there will always be a need for a new record in tblStock2 that is associated with tblStock. So, there is a conflict there. Instead of fighting intrinsic behavior of Access, just embrace it and go with the flow. Let the user create records. Just give the user the ability to back out and edit their mistakes. That is where I would put my energy anyway.

Also, the code you have to add text to the Autonumber does not make sense to me. Again, let Access do what it will do naturaly. Spend time writing code and procedures to deal with the "results" of what Access does.

For instance, you can format a control to display text before a Purchase ID. In your forms and report that display this field, you can add

>"PU-"

in the format property of the textbox.

This single step, alone, may free you from the path you are going down currently. You are working much harder than need be.

[Demerit]

Thank you very much ItsMe. I think i will only stick to autonumber as data type for PurchaseId in the TblStock.

I wish to ask, the idea of creating two tables for purchases I hope its not a bad one.

You know I got myself introduced to access in February 2013 and i have had no teacher.
Waiting to hear from you.

[ItsMe]

I wish to ask, the idea of creating two tables for purchases I hope its not a bad one.

That is how I do it. The only thing is you are not going to be able to update the two tables at the same time. That is the idea of having two. Creating one record in the Customer Order table will give you a PK value that you can place in the FK of the other table. This way you can create a detail of the Customer Order. So it is importartant to create and save the record in the one table. Then, with that, you can create records in another table. Without the first record in the first table, you don't have anything to move forward with.

[Demerit]

thanks ItsMe I will go into reforming the DB.