Selecting ListBox in MS Access

Question

I need a small help dear people. I have a form where I have listbox and with selecting listbox I populate some fields and subform in the form. What I`m trying to achieve is that the subform should not be with 0 records. So I have a control where is checking if the recordset is 0 and is pop up a message but exit sub is not helping and user can stil get to the next record.

    Private Sub lstRev_BeforeUpdate(Cancel As Integer)
    Dim DataConn10 As New ADODB.Recordset
    Dim Comm10 As String
    Set Conn = CurrentProject.Connection
    Comm10 = " SELECT tblLIVE.SID " & _
   " FROM tblLIVE " & _
   " WHERE tblLIVE.CID = " & Me.txtCID & " And tblLIVE.PID =
   " & Me.txtPIDRev & " And tblLIVE.MNumber = '" & Me.txtSMNum & "'"
    DataConn10.Open Comm10, Conn, adOpenKeyset, adLockOptimistic
    If DataConn10.RecordCount = 0 And Not IsNull(Me.txtMIDRev) Then
    Dim x As Integer
    x = MsgBox("Are sure that you want to leave the form without adding Line in subform.    If you press yes Rev will be deleted. If you press No please enter Line", vbYesNo)
    If x = vbYes Then
   MsgBox "Delete"
  Else
  MsgBox "EnterSOV"
  'Here I need something to tell him to stay in the same record :(
  DataConn10.Close
  Exit Sub
  End If
  End If
  End Sub

Zaider Zaider · Accepted Answer · 2014-04-09T18:21:05

You can add Cancel=True to prevent the before update method from finishing. Just add this before your Exit Sub.

Selecting ListBox in MS Access

1 Answers