Form Code (1 Viewer)

arnelgp said:

Code:Copy to clipboard

Private Sub Combo139_Change()
On Error GoTo Err_code
Dim ssql
Dim db As DAO.Database
If (Me.Combo139 = 1) Then
If MsgBox("Are you sure you want to CANCEL the record?", vbDefaultButton2 + vbYesNo + vbQuestion) = vbNo Then
[COLOR="Blue"]Me.Combo139 = Me.Combo139.OldValue[/COLOR]
Exit Sub
End If
Set db = CurrentDb
 ssql = "DELETE FROM [Tbl_ALLOCATION-I] WHERE [CR_NO] =" & Me.Employee_ID
       db.Execute ssql
Set db = Nothing
End If
Me.Refresh
Exit Sub
Err_code:
MsgBox Error$
End Sub

Click to expand...

Pat Hartman said:

The Change event is used if you want to examine each character as it is typed so it runs multiple times. Once for each character that is entered.

The BeforeUpdate event of the control runs only ONCE when the control loses focus or the record loses focus and so needs to be saved.

I really have no idea what will happen with the code suggested by arnel. You are modifying a bound control so Access is going to want to save the record you changed but you are using that event to delete a dirty record???? My head is spinning.

Use the wizard to add a button to the form. One of the options is to delete a record. This will generate an embedded macro. Convert it to code and then add whatever validation you want to the code.

PS, For your own sanity and that of anyone who has to take on this application after you leave, please use meaningful names for controls. If you create a control by dragging it from the ribbon, Access will generate a name like combo139 or text48. If you immediately change the Name property of the control to cboDNO or txtSomeField, then when you add event code, the events will have meaningful names and if you reference the controls in code or queries, you will have some idea what you are referring to.

Even worse than controls named combo139 is one named [Employee ID] which is bound to a field named CR_NO. That should be fixed sooner rather than later. Many experts prefix their control names to distinguish them from the bound fields they hold so the bound field name is DNO but the control is cboDNO and the bound field is CR_NO but the control is named txtCR_NO. Notice that the control name includes the actual bound field as part of its name.

Click to expand...

theDBguy said:

Hi. You might be better off using the BeforeUpdate event instead of the Change event. Just a thought...

Click to expand...

Kundan said:

In Before Update I receive a message - Your code prevents Access from updating the record.

Click to expand...