Solved Now allowing Sub-form to update table based on condition in subfrom (1 Viewer)

Ashisht76

Member
Local time
Tomorrow, 01:07
Joined
Jan 31, 2022
Messages
44
Hello Everyone!

Thx in advance to read and reply my question and thanks to this site for providing opportunity for getting help.

I have main form (FrmFabricReceive) and subform (SFrmTblFabricPieceEntry), I have set code in Subform so that if condition not met it will not run update code, But I failed to write code that stops subform to update its value in its related table (SFrmTblFabricPieceEntry). I can not use beofore update as the Subfrom is in datasheet view and its updating all previously entered values in its table.

this is code that is in after update of subfrom of main form :

Private Sub Form_AfterUpdate()
On Error GoTo Error_Handler

'----------------
Dim aq, aw, au, az
aq = "UPDATE TblGreyFabricOrder "
aw = DSum("[ReceiveGreyFabricMeter]", "TblFabricPieceEntry", "[FabricReceiveID] =" & Forms!FrmFabricReceive!FabricReceiveID)
au = "where TblGreyFabricOrder.GreyOrderID = " & Forms!FrmFabricReceive![GreyOrderID]
aq = aq & " SET TblGreyFabricOrder.TotalGreyReceive =" & aw & " " & au
az = DLookup("[TotalGreyPurchaseInvoiceQuantity]", "TblGreyFabricOrder", "[GreyOrderID] =" & Forms!FrmFabricReceive!GreyOrderID)


If aw > az Then
'Debug.Print aw, az
MsgBox "The Quantity is more than Invoice quantity, this can not be updated."
Me.Undo
Exit Sub
Else
DoCmd.SetWarnings (False)
DoCmd.RunSQL aq
End If
'--------------------
' Before End sub ends, In here couple of update statement that update "TblFabricReceive" but I have removed it to make it simple. but if condition now met in above they won't update the table.

HandleExit:
Exit Sub
Error_Handler:
MsgBox Err.Description
Resume HandleExit
End Sub
 

Attachments

  • Screenshot_1.png
    Screenshot_1.png
    38.9 KB · Views: 148
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 15:37
Joined
Feb 19, 2002
Messages
42,973
The form's AfterUpdate event runs AFTER THE DATA HAS BEEN SAVED. So the code is useless to prevent bad data where you have it.

The code needs to be in the form's BeforeUpdate event AND you have to cancel the update if you don't want the data to be saved. Since you are running an update query that affects multiple rows, it doesn't even make sense to run it in the subform. You need to rethink the logic of what you are doing. Why isn't the quantity being checked as each subform record is added? That seems to be the appropriate place.

Code:
If aw > az Then
    'Debug.Print aw, az
    MsgBox "The Quantity is more than Invoice quantity, this can not be updated."
    Me.Undo
    Cancel = True
    Exit Sub
Else
    DoCmd.SetWarnings (False)
    DoCmd.RunSQL aq
End If

PS, you are setting warnings off but not setting them back on. You will be veeeeeeeeeery sorry if you forget to set the warnings back on. This is extremely dangerous when you are developing since when the warnings are off, you won't get a prompt to save an object you've changed if you close it. Access will silently discard any changes and there goes potentially hours of work into the trash.

The safest method of toggling warnings is to make two macros. One turns warning of and the hourglass on and the other turns warnings on and the hourglass off.

Having the hourglass on when warnings are off is sufficiently annoying to remind you to never forget to turn warnngs back on.
 
Last edited:

Ashisht76

Member
Local time
Tomorrow, 01:07
Joined
Jan 31, 2022
Messages
44
The form's AfterUpdate event runs AFTER THE DATA HAS BEEN SAVED. So the code is useless to prevent bad data where you have it.

The code needs to be in the form's BeforeUpdate event AND you have to cancel the update if you don't want the data to be saved.

Code:
If aw > az Then
    'Debug.Print aw, az
    MsgBox "The Quantity is more than Invoice quantity, this can not be updated."
    Me.Undo
    Cancel = True
    Exit Sub
Else
    DoCmd.SetWarnings (False)
    DoCmd.RunSQL aq
End If

PS, you are setting warnings off but not setting them back on. You will be veeeeeeeeeery sorry if you forget to set the warnings back on. This is extremely dangerous when you are developing since when the warnings are off, you won't get a prompt to save an object you've changed if you close it. Access will silently discard any changes and there goes potentially hours of work into the trash.

The safest method of toggling warnings is to make two macros. One turns warning of and the hourglass on and the other turns warnings on and the hourglass off.

Having the hourglass on when warnings are off is sufficiently annoying to remind you to never forget to turn warnngs back on.
Thanks for information, I will set the warning on again with hourglass. I had read about the danger of doing the same but couldn't fully understood that time. Thanks again for explaining.
 

Ashisht76

Member
Local time
Tomorrow, 01:07
Joined
Jan 31, 2022
Messages
44
The form's AfterUpdate event runs AFTER THE DATA HAS BEEN SAVED. So the code is useless to prevent bad data where you have it.

The code needs to be in the form's BeforeUpdate event AND you have to cancel the update if you don't want the data to be saved. Since you are running an update query that affects multiple rows, it doesn't even make sense to run it in the subform. You need to rethink the logic of what you are doing. Why isn't the quantity being checked as each subform record is added? That seems to be the appropriate place.

Code:
If aw > az Then
    'Debug.Print aw, az
    MsgBox "The Quantity is more than Invoice quantity, this can not be updated."
    Me.Undo
    Cancel = True
    Exit Sub
Else
    DoCmd.SetWarnings (False)
    DoCmd.RunSQL aq
End If

PS, you are setting warnings off but not setting them back on. You will be veeeeeeeeeery sorry if you forget to set the warnings back on. This is extremely dangerous when you are developing since when the warnings are off, you won't get a prompt to save an object you've changed if you close it. Access will silently discard any changes and there goes potentially hours of work into the trash.

The safest method of toggling warnings is to make two macros. One turns warning of and the hourglass on and the other turns warnings on and the hourglass off.

Having the hourglass on when warnings are off is sufficiently annoying to remind you to never forget to turn warnngs back on.
I had already tried and stumbled upon some issue that i cant recollect at this point, but I have tried it again and it seems it will work. Thanks for your suggestion and finding precious time for replying. If any thing will come up I will update here and if all goes well I will mark it as "Solved"
 

Ashisht76

Member
Local time
Tomorrow, 01:07
Joined
Jan 31, 2022
Messages
44
I had already tried and stumbled upon some issue that i cant recollect at this point, but I have tried it again and it seems it will work. Thanks for your suggestion and finding precious time for replying. If any thing will come up I will update here and if all goes well I will mark it as "Solved"
Hi. Your valuable suggestion was absolutely correct however when i had tried putting codes in Before update event of sub-form, the last entry was not getting updated, hence after your suggestion I have changed the sequence of codes and again tried putting it in Before update and all the things are going well except the sub-form is taking a with zero value and I believe it could be because cancel = true either not working or that its not correctly placed. I am working on it and hopefully will get resolved soon. Below corrected codes for your valuable suggestion or any one can chip in, thx to everyone for helping out!

Private Sub Form_BeforeUpdate(Cancel As Integer)
'On Error GoTo Error_Handler

'below code for updting sum of "TotalPieceEntryMeter" in "TblFabricReceive" from "ReceiveGreyFabricMeter" field of "TblFabricPieceEntry" where FabricRecieveID is a match
Dim sq, sw, su, cq, cu
Dim cw As Integer
sq = "UPDATE TblFabricReceive "
sw = DSum("[ReceiveGreyFabricMeter]", "TblFabricPieceEntry", "[FabricReceiveID] =" & Forms!FrmFabricReceive![FabricReceiveID])
su = "where TblFabricReceive.FabricReceiveID = " & Forms!FrmFabricReceive![FabricReceiveID]
sq = sq & " SET TblFabricReceive.TotalPieceEntryMeter =" & sw & " " & su
DoCmd.Hourglass True
DoCmd.SetWarnings False
DoCmd.RunSQL sq
DoCmd.Hourglass False
DoCmd.SetWarnings True
'Debug.Print sw
'below code for updting count of "TotalPieceEntryTaka" in "TblFabricReceive" from "ReceiveGreyFabricMeter" field of "TblFabricPieceEntry" where FabricRecieveID is a match
cq = "UPDATE TblFabricReceive "
cw = DCount("[ReceiveGreyFabricMeter]", "TblFabricPieceEntry", "[FabricReceiveID] =" & Forms!FrmFabricReceive![FabricReceiveID]) - 1
cu = "where TblFabricReceive.FabricReceiveID = " & Forms!FrmFabricReceive![FabricReceiveID]
cq = cq & " SET TblFabricReceive.TotalPieceEntryTaka =" & cw & " " & cu
DoCmd.Hourglass True
DoCmd.SetWarnings False
DoCmd.RunSQL cq
DoCmd.Hourglass False
DoCmd.SetWarnings True
'Debug.Print cw



Dim aq, aw, au, az, ax
aq = "UPDATE TblGreyFabricOrder "
aw = DSum("[ReceiveGreyFabricMeter]", "TblFabricPieceEntry", "[FabricReceiveID] =" & Forms!FrmFabricReceive!FabricReceiveID)
au = "where TblGreyFabricOrder.GreyOrderID = " & Forms!FrmFabricReceive![GreyOrderID]
aq = aq & " SET TblGreyFabricOrder.TotalGreyReceive =" & aw & " " & au
az = DLookup("[TotalGreyPurchaseInvoiceQuantity]", "TblGreyFabricOrder", "[GreyOrderID] =" & Forms!FrmFabricReceive!GreyOrderID)
ax = aw + Me.ReceiveGreyFabricMeter.Value
'Debug.Print aw
DoCmd.Hourglass True
DoCmd.SetWarnings False
DoCmd.RunSQL aq
DoCmd.Hourglass False
DoCmd.SetWarnings True

If ax > az Then
Debug.Print ax, az
MsgBox "The Quantity is more than Invoice quantity, this can not be updated."
Cancel = True
Me.Undo
Exit Sub
End If


'HandleExit:
'Exit Sub
'Error_Handler:
'MsgBox Err.Description
'Resume HandleExit

End Sub
 

Attachments

  • Screenshot_4.png
    Screenshot_4.png
    134 KB · Views: 157
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 15:37
Joined
Feb 19, 2002
Messages
42,973
You are running the update query before you are checking the quantity in inventory.
 

Ashisht76

Member
Local time
Tomorrow, 01:07
Joined
Jan 31, 2022
Messages
44
I am closing this one as i have found solution, I have split code in two and one is for before update and rest in after update, that works. If any one is interested in codes kindly let me know, I will be glad to share. Thanks everyone to take your time and put efforts.
 

Users who are viewing this thread

Top Bottom