Results 1 to 3 of 3
  1. #1
    OldEnough is offline Novice
    Windows 8 Access 2013
    Join Date
    Mar 2014
    Posts
    2

    Deleting records on a subform while combo box is firing a not-in-list event


    I have several forms that occasionally throw an error. The source of the error lies in a sequence of events the user does. I know what the sequence is but I haven't found a successful method for programming around it. Consider a form with 2 buttons "Save" and "Delete", with an associated sub form containing several fields, ItemID (Combobox), ItemDescription (Textbox), etc. The combo box on the sub form has a "not in list" event that opens a separate form for creating new items or clears the current field if the user has made a typo. The "Save Button" on the main form has code to save the details of data entered on the sub form. The "Delete Button" has code to discard the data in the sub form. The problem occurs when a user begins entering data in the ItemID combo box on the sub form, and while it is being edited but is not valid goes to the main form and selects "Delete". The Not In List Event fires on the combo box in the sub form and the code from the "Delete" button never executes. The current clumsy solution requires the user to select "Delete" 2 times in succession (when the not-in-list event has fired in the combo box), but I am hopeful that someone might suggest a better strategy. I would appreciate suggestions and ideas. It is enough of a nuisance that I am going to have to find a workaround.

  2. #2
    JeroenMioch's Avatar
    JeroenMioch is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2012
    Location
    Den Haag, Netherlands
    Posts
    368
    I had to read your post about three times to get a picture of what the problem is and still i cant seem to grasp what your form and subform does.
    To understand what the procedure is makes us better understand what you can do to prevent the problem from happening.
    Can you start by making a screenshot (printscreen) and post that here so we can have a visual reference.

    Especially the part of users making a new itemID or clearing the field when they made a typo makes me confused

  3. #3
    OldEnough is offline Novice
    Windows 8 Access 2013
    Join Date
    Mar 2014
    Posts
    2
    The not-in-list event for the combo box on the sub form:
    On Error Goto Error_CBOItemNIL
    Dim ctl As Control
    Set ctl = Me.cboItem

    Dim Msg As String, CR As String
    CR = Chr$(13)

    If NewData = "" Then Exit Sub
    Me.TxtNewData = NewData

    Msg = "'Item Number " & NewData & "' is not in the list." & CR & CR
    Msg = Msg & "Do you want to add it?"

    'If the new data is correct then open the add Items form
    If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then
    DoCmd.OpenForm "SA_frm_CEItem", , , , acAdd, acDialog, NewData
    Response = acDataErrAdded
    Else
    ctl.Undo
    Response = acDataErrContinue
    End If

    NormalClose:
    Exit Sub
    Error_cboItem_NIL:
    MsgBox "Error # " & Err.Number & " " & Err.Description
    Resume NormalClose
    End Sub

    The Code For the Delete Button on the Main Form:
    On Error GoTo ErrorC

    DoCmd.SetWarnings False
    CurrentDb.Execute "DELETE * FROM tblBillDetTemp;", dbFailOnError
    Forms!SA_frm_CEBill!sbfCEBill.Requery
    DoCmd.SetWarnings True

    Me.cboVendorID.SetFocus

    NormalClose:
    Exit Sub
    ErrorC:
    MsgBox "Cleanup Error. Error # " & Err.Number & " " & Err.Description
    Resume NormalClose

Please reply to this thread with any new information or opinions.

Similar Threads

  1. tab control - firing a page on-click event
    By Chuck55 in forum Programming
    Replies: 7
    Last Post: 05-01-2012, 09:57 AM
  2. Replies: 5
    Last Post: 03-02-2012, 04:43 PM
  3. Form level mouseup event not firing
    By Philhoop in forum Forms
    Replies: 3
    Last Post: 07-22-2010, 09:41 AM
  4. deleting records off a subform
    By jamin14 in forum Programming
    Replies: 10
    Last Post: 04-22-2010, 08:47 PM
  5. Replies: 3
    Last Post: 03-25-2010, 12:31 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums