Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 01-04-2016, 08:12 PM   #1
Radian89
Newly Registered User
 
Join Date: Jan 2016
Posts: 7
Thanks: 6
Thanked 0 Times in 0 Posts
Radian89 is on a distinguished road
Delete Blank Records When Exit Form won't work, (Help)

Hi All,

Happy New Year 2016, I'm still newbie here. Here's my story, I have an invoice form, which there's auto increment numbering there using DMAX function for each new record.

for the user, when the form is blank (but there's invoice number in it), when I press "save and exit" button, I want to delete that empty records. so

when i open the form again, it'll automatically load new records with the same last invoice number when i close it.

but I can't get it right, it always returns with 1 number higher than previous, and the last records is blank, is there something wrong with the code?

I'm also attaching the database, the form is under the name "InvoiceF".

example :
1. currently invoice number 32, without any items there >> click "save & close" >> records number 32 = deleted
2. open the form >> automatically create new records >> the invoice number = 32.

here's my code

Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)

'TO INSERT AUTO INCREMENT INVOICE NUMBER

If Me.NewRecord = True Then
    Me.InvoiceNumber = Nz(DMax("InvoiceNumber", "InvoiceT") + 1, 1)
End If
    
End Sub

-------------------------------------------------------------------------------------------------------------------
Private Sub Form_Current()

Me.PaymentMethod.Value = "Cash"

End Sub

-------------------------------------------------------------------------------------------------------------------
Private Sub Form_Load()

DoCmd.GoToRecord , , acNewRec

End Sub

-------------------------------------------------------------------------------------------------------------------
Private Sub SaveExit_Click()

If Me.Total.Value = 0 Or IsNull(Me.TotalBayar) = True Then
    DoCmd.RunCommand acCmdDeleteRecord
    DoCmd.SetWarnings (warningsoff)
    DoCmd.Close
End If

End Sub
thanks a lot for the help
cheers
Attached Files
File Type: zip Database11.zip (853.0 KB, 42 views)

Radian89 is offline   Reply With Quote
Old 01-04-2016, 08:57 PM   #2
MarkK
Super Moderator
 
MarkK's Avatar
 
Join Date: Mar 2004
Location: Vancouver BC
Posts: 7,761
Thanks: 10
Thanked 1,286 Times in 1,225 Posts
MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all
Data is stored in tables. You can just delete directly everything in the table that doesn't satisfy certain constraints, like . . .
Code:
const SQL_DELETE as string = _
      "DELETE FROM YourTable WHERE [Total] = 0 OR [Total] Is Null"

Private Sub SaveExit_Click()
   currentdb.execute SQL_DELETE, dbfailonerror
End Sub
See what's going on there?
__________________
formerly known as lagbolt | Windows 10 | Access 2010 | Visual Studio 2013 | "Institutions have a vested interest in perpetuating the problems to which they are the solution." - Clay Shirky
MarkK is offline   Reply With Quote
The Following User Says Thank You to MarkK For This Useful Post:
Radian89 (01-05-2016)
Old 01-04-2016, 09:10 PM   #3
sneuberg
AWF VIP
 
Join Date: Oct 2014
Location: Tucson, Arizona
Posts: 3,493
Thanks: 359
Thanked 959 Times in 929 Posts
sneuberg will become famous soon enough sneuberg will become famous soon enough
Re: Delete Blank Records When Exit Form won't work, (Help)

The following seems to do what you what.


Code:
Private Sub SimpanKeluar_Click()

If Me.Total.Value = 0 Or IsNull(Me.TotalBayar) = True Then
    CurrentDb.Execute "DELETE FROM InvoiceT WHERE InvoiceNumber = " & Me.InvoiceNumber, dbFailOnError
    DoCmd.Close
End If


End Sub
Added: This isn't right. See my next post

__________________
And now nothing will be restrained from them, which they have imagined to do. Genesis 11:6

Steve

Last edited by sneuberg; 01-05-2016 at 07:12 AM. Reason: Add information
sneuberg is offline   Reply With Quote
The Following User Says Thank You to sneuberg For This Useful Post:
Radian89 (01-05-2016)
Old 01-04-2016, 09:18 PM   #4
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 12,462
Thanks: 62
Thanked 1,174 Times in 1,074 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Delete Blank Records When Exit Form won't work, (Help)

Radian, we need some precision of terms here because two answers spring forward simultaneously.

If you are using the "DMax" method of generating a number system then you need to solve the problem of deleting the last blank record, which means that when closing the form you might need to have a Form_BeforeUpdate event and perhaps in certain cases cancel the update and do a Form.Undo in the event routine.

If on the other hand, you really ARE using a true autonumber (which shows up as a distinct field type), it will not restart numbering at the first unused number because you used it when you created the record. The autonumber fielddef entry did that for you and as long as your table is built that way, you will never get rid of the unwanted behavior.

Please clarify which case you really have.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
The Following User Says Thank You to The_Doc_Man For This Useful Post:
Radian89 (01-05-2016)
Old 01-05-2016, 07:10 AM   #5
sneuberg
AWF VIP
 
Join Date: Oct 2014
Location: Tucson, Arizona
Posts: 3,493
Thanks: 359
Thanked 959 Times in 929 Posts
sneuberg will become famous soon enough sneuberg will become famous soon enough
Re: Delete Blank Records When Exit Form won't work, (Help)

While the code in my previous post does more than having the DoCmd.RunCommand acCmdDeleteRecord, it still isn't right. There are a few problems.
  • The Docmd.Close shouldn't be inside the if-then. Otherwise the close button doesn't function when the user enters data.
  • The record isn't delete if the user clicks the close box instead of the close button. One way to fix this is to set the Close Button or Control Box property of the form to No. The other way involves putting this code in the form's unload event.
  • The user can add subrecords to the SubInvoiceT table through this form without the Me.Total changing if the quantities are left blank. The integrity constraint prohibits the delete of the InvoiceT record in this case. You are going to have to either delete the related records (by casacading deletes or in code) in the SubInvoiceT table or check for the existence of records in the SubInvoiceT table (Use DCount) and then don't delete the InvoiceT record.
__________________
And now nothing will be restrained from them, which they have imagined to do. Genesis 11:6

Steve

Last edited by sneuberg; 01-05-2016 at 07:46 AM.
sneuberg is offline   Reply With Quote
The Following User Says Thank You to sneuberg For This Useful Post:
Radian89 (01-05-2016)
Old 01-05-2016, 07:36 PM   #6
Radian89
Newly Registered User
 
Join Date: Jan 2016
Posts: 7
Thanks: 6
Thanked 0 Times in 0 Posts
Radian89 is on a distinguished road
Re: Delete Blank Records When Exit Form won't work, (Help)

Sorry for the late reply, yesterday was pretty hectic...

Hi MarkK

Quote:
Data is stored in tables. You can just delete directly everything in the table that doesn't satisfy certain constraints, like . . .
Code:
const SQL_DELETE as string = _
"DELETE FROM YourTable WHERE [Total] = 0 OR [Total] Is Null"

Private Sub SaveExit_Click()
currentdb.execute SQL_DELETE, dbfailonerror
End Sub
See what's going on there?
Thanks a lot for the reply, the code, give me a hint bout this case,
I tried the code, but it return error 3061 too few parameters, but the same approach as Sneuberg replied, just do the trick...


Hi The_Doc_Man

Quote:
Radian, we need some precision of terms here because two answers spring forward simultaneously.

If you are using the "DMax" method of generating a number system then you need to solve the problem of deleting the last blank record, which means that when closing the form you might need to have a Form_BeforeUpdate event and perhaps in certain cases cancel the update and do a Form.Undo in the event routine.

If on the other hand, you really ARE using a true autonumber (which shows up as a distinct field type), it will not restart numbering at the first unused number because you used it when you created the record. The autonumber fielddef entry did that for you and as long as your table is built that way, you will never get rid of the unwanted behavior.

Please clarify which case you really have.
Thanks for the reply. I'm using the Dmax method of generating number, and not unique keynumber, to prevent gap between numbering which will result difficulties in sales report. the Dmax was placed on Form_BeforeUpdate event. I've tried Sneuberg's code the same approach as MarkK, and it does the magic.


Hi Sneuberg

Quote:
The following seems to do what you what.


Code:
Private Sub SimpanKeluar_Click()

If Me.Total.Value = 0 Or IsNull(Me.TotalBayar) = True Then
CurrentDb.Execute "DELETE FROM InvoiceT WHERE InvoiceNumber = " & Me.InvoiceNumber, dbFailOnError
DoCmd.Close
End If
End Sub

Added: This isn't right. See my next post
Yes, it does the magic, thanks a lot, but after reading your next post,

Quote:
While the code in my previous post does more than having the DoCmd.RunCommand acCmdDeleteRecord, it still isn't right. There are a few problems.
The Docmd.Close shouldn't be inside the if-then. Otherwise the close button doesn't function when the user enters data.
The record isn't delete if the user clicks the close box instead of the close button. One way to fix this is to set the Close Button or Control Box property of the form to No. The other way involves putting this code in the form's unload event.
The user can add subrecords to the SubInvoiceT table through this form without the Me.Total changing if the quantities are left blank. The integrity constraint prohibits the delete of the InvoiceT record in this case. You are going to have to either delete the related records (by casacading deletes or in code) in the SubInvoiceT table or check for the existence of records in the SubInvoiceT table (Use DCount) and then don't delete the InvoiceT record.
point 1
the close button doesn't work when user enter data, and return runtime error 3200, when i add data on the subform

then i use ErrorHandler to force the user clear up the subforms before close it.

Code:
On Error GoTo ErrorHandler

'the code here

Exit_Proc:
    Exit Sub

ErrorHandler:
    MsgBox "Please remove items before exit", vbOKOnly, "Can't close!"
    Resume Exit_Proc

Point 2
Just realize it, I'll just disable the close button properties.


Point 3
scenario 1
I tried fill the subform with item but without the qty, so the me.Total = 0, the ErrorHandler do the works when user click exit button, prompting to remove items before exit.

scenario 2
I tried fill the subform with item with qty, but TotalBayar (Paid Amount) remain empty/null, the ErrorHandler do the works when user click exit button.

But if Total.Bayar = 0 (user accidentally do that), it still exit, so I added another OR me.TotalBayar = 0 condition

Maybe most of the issues in my head already tackled, Thanks a lot for the suggestion & reminders, it helps me a lot improving the invoice form.
still learn a lot from this forum.

Thanks a lot,
Warm Regards
Radian89 is offline   Reply With Quote
Old 01-06-2016, 06:21 AM   #7
sneuberg
AWF VIP
 
Join Date: Oct 2014
Location: Tucson, Arizona
Posts: 3,493
Thanks: 359
Thanked 959 Times in 929 Posts
sneuberg will become famous soon enough sneuberg will become famous soon enough
Re: Delete Blank Records When Exit Form won't work, (Help)

I have a couple of suggestion that might be helpful

1. You could avoid having user go back and delete items that he left with zero quantities by requiring quantities in the SubInvoiceSUBform before update. The code below, shown in the subform’s before update, follows. This also prevent negative quantities.

Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)

If Len(Me.ItemQuantity & vbNullString) = 0 Or Me.ItemQuantity <= 0 Then
    MsgBox "You must enter a quantity greater than zero"
    Me.ItemQuantity.SetFocus
    Cancel = True
End If

End Sub
2. In the SubInvoiceSUBform after the user selects an item in the Subinvoice.BarCodeDItem I suggest putting the focus in the quantity textbox, i.e., add Me.ItemQuantity.SetFocus to CBOProduct combo box afterupdate as shown below.

Code:
Private Sub CBOProduct_AfterUpdate()

Me!AgreedToPrice = Me.CBOProduct.Column(2)
Me.ItemQuantity.SetFocus

End Sub

__________________
And now nothing will be restrained from them, which they have imagined to do. Genesis 11:6

Steve
sneuberg is offline   Reply With Quote
The Following User Says Thank You to sneuberg For This Useful Post:
Radian89 (01-06-2016)
Old 01-06-2016, 09:12 PM   #8
Radian89
Newly Registered User
 
Join Date: Jan 2016
Posts: 7
Thanks: 6
Thanked 0 Times in 0 Posts
Radian89 is on a distinguished road
Re: Delete Blank Records When Exit Form won't work, (Help)

Quote:
Originally Posted by sneuberg View Post
I have a couple of suggestion that might be helpful

1. You could avoid having user go back and delete items that he left with zero quantities by requiring quantities in the SubInvoiceSUBform before update. The code below, shown in the subform’s before update, follows. This also prevent negative quantities.

Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)

If Len(Me.ItemQuantity & vbNullString) = 0 Or Me.ItemQuantity <= 0 Then
    MsgBox "You must enter a quantity greater than zero"
    Me.ItemQuantity.SetFocus
    Cancel = True
End If

End Sub
2. In the SubInvoiceSUBform after the user selects an item in the Subinvoice.BarCodeDItem I suggest putting the focus in the quantity textbox, i.e., add Me.ItemQuantity.SetFocus to CBOProduct combo box afterupdate as shown below.

Code:
Private Sub CBOProduct_AfterUpdate()

Me!AgreedToPrice = Me.CBOProduct.Column(2)
Me.ItemQuantity.SetFocus

End Sub
Hi SneuBerg,

sorry late reply, thank again for the suggestion. that's really helpful & thoughtful. I missed the crucial one, user can entry minus

thanks a lot....
warm regards

Radian89 is offline   Reply With Quote
Reply

Tags
delete , dmax

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Form deleting records on exit & manual to Autonumber id, HELP PLEASE! WillJ Forms 1 10-25-2012 07:43 AM
Delete blank records from linked table arifmasum Macros 5 04-27-2008 09:04 PM
Delete duplicate records or blank records davesmith202 Queries 3 03-19-2007 09:38 AM
loop to delete records doesn't quite work lscheer Modules & VBA 5 04-27-2004 08:21 PM
[SOLVED] delete certain records based if a certian field in that record is blank josa Tables 1 04-29-2003 01:44 PM




All times are GMT -8. The time now is 07:55 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World