First select record before click event (1 Viewer)

[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​

 

[Pat Hartman]

Running action queries while you have forms/subforms open displaying those is always delicate since you can easily be conflicting with yourself. Your understanding of how the database works seems to be lacking. Forms don't store data. Tables store data. RI is enforced by the database engine on tables NOT on forms.

If the relationship between tblA and tblB does NOT allow cascade delete, you cannot delete a row from tblA when there are any rows in tblB.
If the relationship between tblA and tblB DOES allow cascade delete, when you delete a row from tblA, it will delete all related rows from tblB also.

Generally, the only time you would ever choose cascade delete = true is if the child rows have no meaning without the parent row. For example, the items of an Order do not exist without the Order header. So, setting cascade delete on that relationship makes perfect sense. Thinking about the lower relationship between OrderDetails and Products, you would NOT allow cascade delete on Products to OrderDetails since Products may be on any order or on no order but if they are on any order, it does not make sense to allow the product record to be deleted. So, with cascade delete = false, you can NOT delete any Product record if there are related OrderDetails record. However, if no one has ever created an order for that particular product, the product may logically be deleted.

 

[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

 

[Pat Hartman]

If you have RI defined on the relationship, you can run a delete query. It will work or not work depending on the RI rules. My background is huge tables and many concurrent users so I look at certain things differently. Timing is everything. If I run the delete query, it will delete rows or not depending on the situation at that moment and the RI rules. If I run a query that checks first, then I decide whether or not to run the query. Then I run the query, someone could have added a record in that split second. Computers are very fast but they operate sequentially.

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.

 

[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.

 

[Pat Hartman]

I'm not arguing about the check first. I only pointed out that you run the risk of the situation being different at the time you run the delete. As I said, my view of the world is colored by my early training and using those techniques in a lower volume environment won't cause any problems so I don't ever have to decide - Is contention something I have to worry about? I always worry about it In my advanced COBOL class, we went so far as studying the actual execution speed of instructions so we knew which would be most expensive. When we wrote CASE statements for example, we always gave some thought to which option would be selected most frequently and ordered the Cases based on that analysis. We applied similar logic to nested If's to find the exclusion path as soon as possible. I'm pretty sure Access developers don't even think about that for a second. Granted, our code was all being executed on the main frame and Access' code is being executed on the user's PC so code in Access isn't competing for CPU cycles with other users, BUT the SQL code is running on the server so we are back to needing to consider how what we do affects the server load. Should I run two queries or should I run one? If you care to examine this in depth, you can use the SQL Server tools to assign a price to your queries.

Again, I remind you, I was building CICS or IMS transactions, the majority of which were expected to have < 1 second response time in an environment where we were serving 4,000 concurrent users across the country. The slowest should be < 4 seconds and those transactions were few and far between.

There's two things I always keep in mind with error checking.
1. I/O is the most expensive operation a procedure executes so I don't do I/O that isn't 100% necessary
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. The only exception I make to this is when the user is entering a required field that must be unique such as SSN. Rather than allowing him to complete the entire record, I generally check the uniqueness immediately in the control. If the error was caused by a typo, I've wasted the query to validate since validating at the end would have been fine. However, if the user has the wrong number, I've saved him the time of entering all the data but not letting him save the new record.

 

[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.

 

[Pat Hartman]

As in the example I gave, there is always the possibility of wasting I/O to solve/prevent some other problem. I am talking about developing the most efficient best practices. We use them unless there is a reason to not use them We're talking milliseconds here so the individual app is not affected.

The point is that once we start sharing resources with the rest of the world, we should use them efficiently. Access is frowned on by DBA's largely because Access developers do not understand how their choices affect the rest of the network. Access is already very chatty when running queries so I choose to not run queries that are not absolutely necessary.