First select record before click event

[Gaztry80]

Morning !

I have made a continues subform (frmA) on my main form (frmMainA) with an button on it. In the clickevent I have made some statements to run eventually a delete query. There is also another subform (frmB > which is linked to frmA by frmMainA). Now I want to make a If statement that the event is only happening when an iffcount value in frmB is > 0. I have tried to create this by making a new linked text box (txtCheck) on frmMainA and then refer to that value.

Summary: Basically I want to check an IIFCOUNT in another subform and if this is > 0 it is not allowed to delete the records in frmA, since there are corresponding records in frmB.
The problem: When I am for example on record 2 of frmA and select the button of record e.g. 10 frmA (launch delete query), the value of txtCheck displays the value of frmB linked to record 2 of frmA, since the click event is launched before selecting the record 10.
Question: Is there a easy way to first select the record on frmA and then launch the click event of this button?

Thank you for support!

 

[bob fitz]

Can't you just requery frmB first.

 

[sonic8]

Summary: Basically I want to check an IIFCOUNT in another subform and if this is > 0 it is not allowed to delete the records in frmA, since there are corresponding records in frmB.

It would probably be much easier to address your requirements, if you would not focus on forms but on the underlying tables/queries.

 

[The_Doc_Man]

Look into relational integrity if you are talking about not wanting to delete are record if a related child record exists. Don't turn on Cascade Delete if you go that route.

 

[Gaztry80]

Thanks for replying ! I am using sql server by the way as back-end.
I don't think the relational integritry is working, since the table (with product key*) related to frmA can have records while they don't have to be exist in frmB (TableB). Below a small example in excel what i want to achieve.

	TableA		TableB
TableA delete action	ProductId		CustomerId	ProductId
Delete not possible, since Productid does exist in table B	1​		1​	1​
Delete not possible, since Productid does exist in table B	2​		1​	2​
Delete not possible, since Productid does exist in table B	3​		2​	1​
Delete possible, since productId does not exist in table b	4​		2​	2​
Delete possible, since productId does not exist in table b	5​		2​	3​
Delete possible, since productId does not exist in table b	6​
Delete possible, since productId does not exist in table b	7​
Delete possible, since productId does not exist in table b	8​

 

[Gaztry80]

"Your understanding of how the database works seems to be lacking"

If you create the database yourself and read this, I realise I am being pulled down the rabbit hole

 

[sonic8]

Therefore, my advice is - set the RI to do what you want and just delete. Don't check first. The only time you would have to do that is if RI can't do what you need to do.

I agree regarding RI.
Still, I prefer to run a check query first. This makes it easier to produce comprehensible messages to the users, particularly if there is a number of tables linked with RI which might prevent deleting.

 

[sonic8]

Just to be clear: I'm all for referential integrity as the decisive mechanism to ensure the integrity of data in a database.

2. Users rarely make input mistakes. This means, if your validation requires I/O, leave it to the server whenever possible and catch the error. T

No general contradiction, just an aspect to keep in mind: If it is about a sizeable transaction spanning multiple tables and affecting a significant number of records, the error and the resulting rollback of the transaction might require more I/O and cause more contention than thousands of preliminary checks in advance would have done.