Delete Blank Records When Exit Form won't work, (Help) (1 Viewer)

Radian89

New member
Local time
Tomorrow, 00:42
Joined
Jan 5, 2016
Messages
7
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? :confused:

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 :)
 

Attachments

  • Database11.zip
    853 KB · Views: 97

MarkK

bit cruncher
Local time
Today, 10:42
Joined
Mar 17, 2004
Messages
8,178
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?
 

sneuberg

AWF VIP
Local time
Today, 10:42
Joined
Oct 17, 2014
Messages
3,506
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
 
Last edited:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 12:42
Joined
Feb 28, 2001
Messages
26,999
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.
 

sneuberg

AWF VIP
Local time
Today, 10:42
Joined
Oct 17, 2014
Messages
3,506
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.
 
Last edited:

Radian89

New member
Local time
Tomorrow, 00:42
Joined
Jan 5, 2016
Messages
7
Sorry for the late reply, yesterday was pretty hectic...

Hi MarkK

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

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

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,

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
 

sneuberg

AWF VIP
Local time
Today, 10:42
Joined
Oct 17, 2014
Messages
3,506
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
 

Radian89

New member
Local time
Tomorrow, 00:42
Joined
Jan 5, 2016
Messages
7
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 :banghead:

thanks a lot....
warm regards
 

Users who are viewing this thread

Top Bottom