Cancel the data entered when we are half way (1 Viewer)

hfsitumo2001

Member
Local time
Yesterday, 21:53
Joined
Jan 17, 2021
Messages
365
I have a form to enter supplies issuance. The form consists of Main form (data is from Sales Order Query) and subform is based on Sales Order Query(tblSalesdetails & tblInventory).

If we typed in the data upto the customer in the main form than we press ESC twice, the data can be cancelled totally, but if we already typed in the subform (continues form) with the data name of the product/supplies, and quantity etc. if we want to cancel the transaction. We can not cancel even though we press the keyboard ESC so many times.

Anyone have any idea how to cancel both in main form and subform?

Thank you
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 21:53
Joined
Oct 29, 2018
Messages
21,358
Hi. I think the safest way is to add a button on your main form for canceling a record. You could ask the user to confirm this action and then delete the main record along with the child records.
 

hfsitumo2001

Member
Local time
Yesterday, 21:53
Joined
Jan 17, 2021
Messages
365
Let me try it DB..
 

hfsitumo2001

Member
Local time
Yesterday, 21:53
Joined
Jan 17, 2021
Messages
365
Hi. I think the safest way is to add a button on your main form for canceling a record. You could as the user to confirm this action and then delete the main record along with the child records.
I have tried it DB, but It can not cancel it, as per picture here, I pressed cancel the record when I typed upto the subform but it says " The command or "Undo" isn't available now"
 

Attachments

  • undoin the middle of the road.jpg
    undoin the middle of the road.jpg
    100.9 KB · Views: 221

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 21:53
Joined
Oct 29, 2018
Messages
21,358
Hi. I didn't say "undo." I said "delete."
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 12:53
Joined
May 7, 2009
Messages
19,169
agree to delete the Record in main form.
add code to th Subform's Exit event to delete the record in main form.
Note: replace subFormName with the name of your subform
Code:
Private Sub subFormName_Exit(Cancel As Integer)
    If Me!subFormName.Form.Recordset.RecordCount < 1 Then
        MsgBox "Sales Order record will be deleted since there is no Detail entered.", vbInformation + vbOKOnly
        With Me.RecordsetClone
            .Bookmark = Me.Bookmark
            .Delete
            .AddNew
        End With
    End If
End Sub
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 04:53
Joined
Sep 12, 2006
Messages
15,614
The ways already demonstrated can cause numeric sequences to fail to be maintained intact, so if that's important to you might need to set a status flag on the header record to show it was deleted and not used.

Once the header has been entered the same point arises. You are better to set a status than allow it to be deleted. Even if you aren;t so bothered about intact sequences, unexplained gaps in sequences can still be slightly worrying.

If I have a system with a header and detail records, I would not want to commit the header if I thought it might get deleted during the entry stage. What if another process had used the new header record already?

Therefore, I always put the new header and the new details in a temporary table or a temporary structure, and then inset them all into the database at the same time. Cancelling the entry then just affects the temporary structures, and not the main data.
 

hfsitumo2001

Member
Local time
Yesterday, 21:53
Joined
Jan 17, 2021
Messages
365
agree to delete the Record in main form.
add code to th Subform's Exit event to delete the record in main form.
Note: replace subFormName with the name of your subform
Code:
Private Sub subFormName_Exit(Cancel As Integer)
    If Me!subFormName.Form.Recordset.RecordCount < 1 Then
        MsgBox "Sales Order record will be deleted since there is no Detail entered.", vbInformation + vbOKOnly
        With Me.RecordsetClone
            .Bookmark = Me.Bookmark
            .Delete
            .AddNew
        End With
    End If
End Sub
Hi Arnel, I do not have exit event on my subform MS Access 2016
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 12:53
Joined
May 7, 2009
Messages
19,169
on design view of your form, click on the Border of the subform, then you will see the Exit event.
 

Privateer

Registered User.
Local time
Today, 00:53
Joined
Aug 16, 2011
Messages
191
I hope this is not too complicated but it is something I do with every database and should be considered when building one. Continuous forms use a query as the record source, editing an existing record is bound to the table holding that record, and new data/records use an unbound form, meaning one with no record source. This gives you the control you are looking for, which is to be able to cancel a new record at any stage of the entry process without hitting a table and leaving random baggage requiring a delete command to remove that record. Behind the scenes you write code to check every text box on the form for valid data and to make sure a required field is not empty. As you are doing this, you assign each value to a variable and use a recordset to enter the new record into the table. This can also work with sub forms. After you enter the main form data using the recordset it creates a primary key number which you can grab and assign to a variable. Then with another recordset, you add the subform data and the new main form primary key. This unbound form option also allows you to check for a duplicate record, like that person is already in the database, and by assigning the data to variables with specific data types, you are very sure the value will go into the table without any problems.
I have attached a sample of my code that shows the basics of entering data into two related tables. If you can follow this and want the required field and duplicate record code, let me know.

One other thing that might help you. Consider creating two empty tables, one with all the main form fields and one for the sub form. Build a query with a left join from the main table to the sub table and make it the record source for the form. When you open the form, run a simple delete query to empty them of the last record. When a new record is ready the code behind the save button can be two other queries to take the records from the two temporary tables and put them into the two real tables. A simpler solution, but data validation, checking for duplicates and required fields being populated will have to be taken care of another way. Anyway, a long answer, but I hope it helps you out.
 

Attachments

  • Access New Record Code.txt
    1.8 KB · Views: 242

hfsitumo2001

Member
Local time
Yesterday, 21:53
Joined
Jan 17, 2021
Messages
365
I hope this is not too complicated but it is something I do with every database and should be considered when building one. Continuous forms use a query as the record source, editing an existing record is bound to the table holding that record, and new data/records use an unbound form, meaning one with no record source. This gives you the control you are looking for, which is to be able to cancel a new record at any stage of the entry process without hitting a table and leaving random baggage requiring a delete command to remove that record. Behind the scenes you write code to check every text box on the form for valid data and to make sure a required field is not empty. As you are doing this, you assign each value to a variable and use a recordset to enter the new record into the table. This can also work with sub forms. After you enter the main form data using the recordset it creates a primary key number which you can grab and assign to a variable. Then with another recordset, you add the subform data and the new main form primary key. This unbound form option also allows you to check for a duplicate record, like that person is already in the database, and by assigning the data to variables with specific data types, you are very sure the value will go into the table without any problems.
I have attached a sample of my code that shows the basics of entering data into two related tables. If you can follow this and want the required field and duplicate record code, let me know.

One other thing that might help you. Consider creating two empty tables, one with all the main form fields and one for the sub form. Build a query with a left join from the main table to the sub table and make it the record source for the form. When you open the form, run a simple delete query to empty them of the last record. When a new record is ready the code behind the save button can be two other queries to take the records from the two temporary tables and put them into the two real tables. A simpler solution, but data validation, checking for duplicates and required fields being populated will have to be taken care of another way. Anyway, a long answer, but I hope it helps you out.
Thank you Privateer, I will study it
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 12:53
Joined
May 7, 2009
Messages
19,169
here is a sample using code in post #3.
the Link Master Fields in subform (table2) is ID.
and the Link Child Fields is Table1ID.
see the Exit Event (code of the subform).
 

Attachments

  • Database8.accdb
    1,016 KB · Views: 226

hfsitumo2001

Member
Local time
Yesterday, 21:53
Joined
Jan 17, 2021
Messages
365
Hi. I think the safest way is to add a button on your main form for canceling a record. You could ask the user to confirm this action and then delete the main record along with the child records.
Hi DBGuy, do you think even though we press cancel twice is not gonna to cancel the halfway typing data? it is because the form to add such as new customer is still opened. That is why we need the VBA on the control to close it on GET Focus like this?. Long time ago when I wans in Indonesia, you help me to make a membership database. This the sample of VBA to close the form
Private Sub AdrsID_GotFocus()
If fIsLoaded("ALAMAT PER KELUARGA_JEMAAT KBY") Then
DoCmd.Close acForm, "ALAMAT PER KELUARGA_JEMAAT KBY", acSaveNo
End If
End Sub
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 21:53
Joined
Oct 29, 2018
Messages
21,358
Hi DBGuy, do you think even though we press cancel twice is not gonna to cancel the halfway typing data? it is because the form to add such as new customer is still opened. That is why we need the VBA on the control to close it on GET Focus like this?. Long time ago when I wans in Indonesia, you help me to make a membership database. This the sample of VBA to close the form
Private Sub AdrsID_GotFocus()
If fIsLoaded("ALAMAT PER KELUARGA_JEMAAT KBY") Then
DoCmd.Close acForm, "ALAMAT PER KELUARGA_JEMAAT KBY", acSaveNo
End If
End Sub
Hi. The ESC key only works for the active form and the current record. Once you move from form to form or subform, the ESC key no longer applies. That's why you need to use VBA code to delete the previously saved record, if you want to cancel the whole transaction.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 21:53
Joined
Oct 29, 2018
Messages
21,358
I appreciate if you could give me the sample of VBA DBGuy
Something like:
Code:
If MsgBox("Would you like to Cancel this record?", vbYesNo+vbQuestion,"Confirm")=vbYes Then
    CurrentDb.Execute "DELETE FROM TableName WHERE ID=" & Nz(Me.ID), dbFailOnError
    Me.Requery
End If
If you have Cascade Delete enabled, this would also delete all the data from the Subform.

Hope that helps...
 

hfsitumo2001

Member
Local time
Yesterday, 21:53
Joined
Jan 17, 2021
Messages
365
Something like:
Code:
If MsgBox("Would you like to Cancel this record?", vbYesNo+vbQuestion,"Confirm")=vbYes Then
    CurrentDb.Execute "DELETE FROM TableName WHERE ID=" & Nz(Me.ID), dbFailOnError
    Me.Requery
End If
If you have Cascade Delete enabled, this would also delete all the data from the Subform.

Hope that helps...
Where shall we put the VBA DBGuy, in which form even procedure...
Also what do you mean by Cascade delete enabled.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 21:53
Joined
Oct 29, 2018
Messages
21,358
Where shall we put the VBA DBGuy, in which form even procedure...
Also what do you mean by Cascade delete enabled.
Hi. I said it in Post #2. You could use a button and put the code in its Click event.

Regarding Cascade Delete, assuming the subform is bound to the child table of the parent table on the main form, then if you had referential integrity enabled on the relationship between the two tables, you can also turn on cascade delete. which means child records will be automatically deleted when a parent record is deleted.
 

hfsitumo2001

Member
Local time
Yesterday, 21:53
Joined
Jan 17, 2021
Messages
365
Hi. I said it in Post #2. You could use a button and put the code in its Click event.

Regarding Cascade Delete, assuming the subform is bound to the child table of the parent table on the main form, then if you had referential integrity enabled on the relationship between the two tables, you can also turn on cascade delete. which means child records will be automatically deleted when a parent record is deleted.
In the quantity column of my subform, there is an after update, the question for reconfirmation whether it is wrong or not, if correct it will ask save and the quantity of inventory will be updated if in the reconfirmation boz said no it is not saved and press esc esc, the line is cleared and I put the cursor to the main form then I press the delete button to delete record in the main form, that is the way I made. What do you think would be a problem,
 

Rene vK

Member
Local time
Today, 05:53
Joined
Mar 3, 2013
Messages
123
In the quantity column of my subform, there is an after update, the question for reconfirmation whether it is wrong or not, if correct it will ask save and the quantity of inventory will be updated if in the reconfirmation boz said no it is not saved and press esc esc, the line is cleared and I put the cursor to the main form then I press the delete button to delete record in the main form, that is the way I made. What do you think would be a problem,
Are you the only one that will use the application? If this will be used by many you can bet on one thing, chaos! People tend to forget your solution when they have a lot of work or are distracted. Writing an application is to make worklife easier or automated. That is the main question for creating an application.
 

Users who are viewing this thread

Top Bottom