vba delete command gives error?

[siamandm]

Hello
i have this code below which was working fine, but suddenly gives this error :

Capture.PNG

Code: Select all

Private Sub cmdDel_Click()
    If MsgBox("Are you sure you want to delete this record?", vbExclamation + vbYesNo, "Delete Record") = vbYes Then
        DoCmd.SetWarnings False
        DoCmd.RunCommand acCmdDeleteRecord
        DoCmd.SetWarnings True
    End If
End Sub

[HansV]

There are many possible causes for this error message. There could be related records in another table, or the current record is a new record, for example.
Does the problem occur each time you click the button, or only some of the time?

[siamandm]

yes you are right i was trying to delete an empty record !!, it works for the other records.
the reason is why i tried to delete a record is a default value for a text box and combo box.
so :
1- can we handle this error by showing a custom error msg ?
2- can we make the default value appears only when i start data entry to the record?


regards

[HansV]

1. For example:

Code: Select all

Private Sub cmdDel_Click()
    On Error GoTo ErrHandler
    If Me.NewRecord Then
        MsgBox "To cancel a new record, press Esc", vbInformation
        Exit Sub
    End If
    If MsgBox("Are you sure you want to delete this record?", vbExclamation + vbYesNo, "Delete Record") = vbYes Then
        DoCmd.SetWarnings False
        DoCmd.RunCommand acCmdDeleteRecord
        DoCmd.SetWarnings True
    End If
    Exit Sub
ErrHandler:
    MsgBox "Something went wrong!", vbExclamation
End Sub

[HansV]

2. Instead of setting the Default Value property of controls, you can use the Before Insert event of the form. For example:

Code: Select all

Private Sub Form_BeforeInsert(Cancel As Integer)
    Me.txtNewDate = Date
    Me.cboSomething = 1
End Sub

The Before Insert event fires when the user starts entering data in a new record.

[siamandm]

thats works fine ... thanks alot.

1. For example:

Code: Select all

Private Sub cmdDel_Click()
    On Error GoTo ErrHandler
    If Me.NewRecord Then
        MsgBox "To cancel a new record, press Esc", vbInformation
        Exit Sub
    End If
    If MsgBox("Are you sure you want to delete this record?", vbExclamation + vbYesNo, "Delete Record") = vbYes Then
        DoCmd.SetWarnings False
        DoCmd.RunCommand acCmdDeleteRecord
        DoCmd.SetWarnings True
    End If
    Exit Sub
ErrHandler:
    MsgBox "Something went wrong!", vbExclamation
End Sub

[siamandm]

2. Instead of setting the Default Value property of controls, you can use the Before Insert event of the form. For example:

Code: Select all

Private Sub Form_BeforeInsert(Cancel As Integer)
    Me.txtNewDate = Date
    Me.cboSomething = 1
End Sub

The Before Insert event fires when the user starts entering data in a new record.

this seems to be working fine for a single form , but for the continuous form it works before inserting any record, but after inserting one record it will add another record with a default value.

[HansV]

Did you clear the Default Value property of all controls on your continuous form?

[siamandm]

yes , i did that too.

[HansV]

The moment you start entering data in a new record, the code should populate some controls, and Access will automatically create a blank new record below that, but that one should be really blank unless you still have some Default Values set...

[siamandm]

that make sense ... i will check other things ... maybe something is making this issue ...

regards

[siamandm]

i even created a new form for test , still have same issue

this is my db , please have a look and see why is not working : subfrmDist_items

alos i have another issue , in on click event for the button changes status , it says: method or data member not found. ( for this its on frmBeneficiaries ... tab items...)

NewDistDB-v01.zip

[HansV]

The first thing I noticed is that AssessedDate is a text field. It should be a date/time field.
You still had qryAssess as name of the subform in your code (that was the name in your other sample database).

I suspect that your form is partially corrupt, it refused to recognize cboItemStatus in the SQL. I worked around it by using a variable.

You didn't use my suggestion correctly. You set the DefaultValue of some controls in code, instead of the Value. You have

Code: Select all

Private Sub Form_BeforeInsert(Cancel As Integer)
    Me.cboItemStatus.DefaultValue = 1
    Me.txtAssessedDate.DefaultValue = Date
End Sub

I deliberately did NOT use DefaultValue! It should be

Code: Select all

Private Sub Form_BeforeInsert(Cancel As Integer)
    Me.cboItemStatus = 1
    Me.txtAssessedDate = Date
End Sub

In the future, please take the trouble to read my replies more carefully.

NewDistDB-v01.zip

[siamandm]

The first thing I noticed is that AssessedDate is a text field. It should be a date/time field.
You still had qryAssess as name of the subform in your code (that was the name in your other sample database).

I suspect that your form is partially corrupt, it refused to recognize cboItemStatus in the SQL. I worked around it by using a variable.

You didn't use my suggestion correctly. You set the DefaultValue of some controls in code, instead of the Value. You have

Code: Select all

Private Sub Form_BeforeInsert(Cancel As Integer)
    Me.cboItemStatus.DefaultValue = 1
    Me.txtAssessedDate.DefaultValue = Date
End Sub

I deliberately did NOT use DefaultValue! It should be

Code: Select all

Private Sub Form_BeforeInsert(Cancel As Integer)
    Me.cboItemStatus = 1
    Me.txtAssessedDate = Date
End Sub

In the future, please take the trouble to read my replies more carefully.

NewDistDB-v01.zip

thanks alot , i still get this error :

Capture.PNG

[HansV]

I'm sorry, I attached an older version of the database. Here is the newer one.

NewDistDB-v01.zip

[siamandm]

thats working very good , thanks alot
but there is one minor thing, if the user forgot to put the date into the date from or date to text box , there will be a problem in the code .
so if we can make a validation or show warning box shows that user must put date from and to will be greater

[HansV]

Like this:

Code: Select all

Private Sub cmdChangeStatus_Click()
    Dim strSQL As String
    Dim lngStatus As Long
    If IsNull(Me.txtDateFrom) Then
        Me.txtDateFrom.SetFocus
        MsgBox "Please enter a From date first!", vbExclamation
        Exit Sub
    End If
    If IsNull(Me.txtDateTo) Then
        Me.txtDateTo.SetFocus
        MsgBox "Please enter a To date first!", vbExclamation
        Exit Sub
    End If
    lngStatus = Me.cboItemStatus
    strSQL = "UPDATE tblDist_items SET DateReceived=#" & Format(Me.txtNewDate, "yyyy/mm/dd") & "#, Status=" & lngStatus & _
        " WHERE BenefID_FK=" & Me.BenefID & " AND AssessedDate Between #" & _
        Format(Me.txtDateFrom, "yyyy/mm/dd") & "# And #" & Format(Me.txtDateTo, "yyyy/mm/dd") & "#"
    CurrentDb.Execute strSQL, dbFailOnError
    Me.subfrmDist_Items.Requery
End Sub

[siamandm]

thank you a lot , much appreciated....