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

 

[Dewi]

You could use a dcount function to check how many records exist in tblstock2 for each instance of ProductID on an event line, be it a save button or maybe lose focus. If there are less than 1 then run an SQL command line to delete the corresponding empty record in TblStock. This will mean though that the users will have to enter a sub record level after creating the main form record initially, which I take it from your explanation is what you need. You could always put in a message box before the delete line to warn the user that his header record will be deleted unless he enters a child record.

use something along the lines of

if dcount("PurchaseID","tblstock2","purchaseID =" & Forms!tblstock!purchaseid) <1
then

SQLDeleteNull = "Delete From Tblstock where ID = Forms!tblstock!purchaseid"
DoCmd.RunSQL SQLDeleteNull

end if

it should work, I use the same process for clearing dead records.
Cheers

 

[Demerit]

Using the dcount function and deleting records in the TblStock to me I think will call an error msg to be populated. This is based on how the PurchaseID is being form.

Remember the PurchaseID is automatic and it counts the number of records, then adds one(1) to create the next PurchaseID. Deleting a record in the TblStock will mean reducing the counts and this will cause a problem when the DB counts the records and adds 1 to create the next ID but discovers that its already existing.

The VBA enabling to create the PurchaseID automatically is found in the Before Update event of the form FrmStock.

What do you think?

 

[Dewi]

If you are using an autonumber to create your purchase id then when deleted it will never raise another id of the same number, i.e if you delete ID, "9" then a new record would take on ID="10". the "9" record is lost forever.

The error message you are talking about is probably the one referring to refferential integrity which follows the premise that if you delete a parent record with associated child records then you will also lose your child records. I took it that the idea was to delete any parent record if no child records existed, so in theory you should not get an RI warning message. You will also get a message warning you that an SQL command is about to delete some records....you can supress this message thus

DoCmd.SetWarnings False

just before your docmd.sql command

 

[Dewi]

..Just looked at how you generate your PurchaseID, its not an autonumber as I thought and I realise now what you mean. I think though that the method you are using is going to give you issues as the ID is only unique if the number of records increases. As you said , if you delete one the next one will have the same ID as the last one in the recordset. I would use Autonumber as your primary key and link you stocktable2 to your main table by this number, this will enforce a true individual and unique reference. Having the ID as a number also fits in to the syntax of the code I supplied as a string value needs slightly different syntax.

 

[Demerit]

Thank you Dewl

I think i will only have to use autonumber as primary key in the table TblStock.

My second worry lies on the VBA you supplied. By your proposal, where should i attached this code? If possible, you could attached it to a form in the DB and upload.

Waiting to hear from you

 

[Dewi]

Have a look at the frmstock.
My amendments in yellow and a function button which has the code behind it.
I have also added a few new fields to tblstock and tblstock2 and altered the relationship between these to reflect the join with the auto id. so that yo have a PO identifier I have made a field which concatenates the ID and a "PU-" to make a recognizable identifier for you.

 

[Demerit]

Its working but there are some issues that I encounter:

1. After entering a record, there are saved in the tables but the form is not cleared for the next transaction to take place.

2. For this second issue plse so the following on the form to understand better:
a) Select a product name (just one is enough)
b) Then select a supplier name

You must have noticed that the product name you first selected has disappeared and has updated the tblStock2.

The consequence is that : 1) The LinkToParent field is empty for this record.
2) If one has entered 20 records and this happens it will mean the person has to repeat all the process.

Waiting to hear your proposal

 

[Dewi]

Generally speaking when you link a child record to a parent record you need to create a parent record first. So in your example it works ok if you select the supplier first and then select your product names. You will notice that the ID for the main form remains "New "until you select a supplier.

Are you saying then that you wish to build a list of products first and then assign to a supplier? If this is the case then your parent record needs to be created against another attribute, say "Order" which you can then assign to a supplier after building it.

If you are happy to choose a supplier first and then build in your product list then you could always disable the form until the supplier field is not null.

 

[Demerit]

To disable the field until the supplier field is not null I guest in this case i will prefere that. But which code do i need to use and where should i place the code?