Results 1 to 10 of 10
  1. #1
    Star Lounger
    Join Date
    Mar 2002
    Location
    Haaltert, Belgium
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    error when vbyes on not in list (2003)

    I have written a code on a combo box "on not in list".
    This is my code:
    Private Sub selKlant_NotInList(NewData As String, Response As Integer)
    Dim ctl As Control
    If MsgBox(NewData & " Deze bestaat nog niet." & vbCrLf & _
    "Klant toevoegen? ", vbYesNo, "Fout !") = vbYes Then
    DoCmd.OpenForm "frmklantenadd", acNormal, , , acFormAdd
    Response = acDataErrAdded
    Else
    Response = acDataErrContinue
    End If
    End Sub

    When it is "vbyes" it first give an error msgbox that the item is not in list, then it opens the forms.
    It works fine, except that msgbox of access self.
    How can I escape this error?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: error when vbyes on not in list (2003)

    When you open a form in the normal way, code will continue running. If you change the line that opens the form to

    DoCmd.OpenForm "frmklantenadd", acNormal, , , acFormAdd, acDialog

    the acDialog argument will make the code wait until the user has closed the form.

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: error when vbyes on not in list (2003)

    Change

    DoCmd.OpenForm "frmklantenadd", acNormal, , , acFormAdd

    to

    DoCmd.OpenForm "frmklantenadd", acNormal, , , acFormAdd, acDialog

    or

    DoCmd.OpenForm "frmklantenadd", acNormal, , , acFormAdd, acDialog, NewData

    By adding acDialog the form is opened in dialog mode. this means that the current code ceases at that point until the form is closed.

    The second option passes the new data to the form as an Openargs value. the form could check that and use it to fill in a field on the form.
    Regards
    John



  4. #4
    Star Lounger
    Join Date
    Mar 2002
    Location
    Haaltert, Belgium
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: error when vbyes on not in list (2003)

    Ok that works, on my addform I have 2 codes, 1 to cancel and 1 to close and save
    The save button works fine, but when I click on my cancel button, I'll get the error that the item is not in list.
    How can I escape this error?
    This is my code on the cancel button of the add form:
    If Me.txtKlant = "" Then
    Me.txtKlant = 1
    End If

    On Error GoTo err_cmdannuleer_click
    DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
    DoCmd.Close

    exit_cmdannuleer_click:
    Exit Sub

    err_cmdannuleer_click:
    MsgBox Err.Description

    Resume exit_cmdannuleer_click
    End Sub

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: error when vbyes on not in list (2003)

    The user indicated that he (or she) wanted to add a value, then canceled it. The value in the combo box is still not in the list, so the error message is to be expected. If you want to suppress it, you could declare a public variable at the top of a standard module (created by selecting Insert | Module):

    Public blnAdded As Boolean

    Insert the following line above DoCmd.OpenForm in the NotInList event procedure:

    blnAdded = False

    Add the following line to the OnClick event procedure for the OK button on frmKlantenAdd (but not for the Cancel button!)

    blnAdded = True

    Change the line

    Response = acDataErrAdded

    in the NotInList event procedure to

    If blnAdded = True Then
    Response = acDataErrAdded
    Else
    Me.selKlant.Undo
    Response = acDataErrContinue
    End If

  6. #6
    Star Lounger
    Join Date
    Mar 2002
    Location
    Haaltert, Belgium
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: error when vbyes on not in list (2003)

    Ok that works, but I just keep on asking things <img src=/S/confused3.gif border=0 alt=confused3 width=45 height=45>
    When I close the addform with the ok button (because new record was added), I want to refresh that combobox of the form where I started.
    I've tried with requery, but that does not work.
    I don't know if I should add this refresh or requery on the add form or the startform.
    This Is know my code on "not in list"
    If blnAdded = True Then
    Response = acDataErrAdded
    Else
    Me.selKlant.Undo
    Response = acDataErrContinue
    End If

    That does not work. there is also the problem that the add form can be opened from two different forms, so I have th check first which form is loaded.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: error when vbyes on not in list (2003)

    You don't have to requery the selKlant combo box - setting Response to acDataErrAdded causes Access to requery the combo box automatically.

    If you pass NewData to frmKlantenAdd in the OpenArgs argument of DoCmd.OpenForm as suggested by John Hutchison, frmKlantenAdd doesn't have to "know" from which form it is opened. You can inspect OpenArgs in the On Load event of frmKlantenAdd.

  8. #8
    Star Lounger
    Join Date
    Mar 2002
    Location
    Haaltert, Belgium
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: error when vbyes on not in list (2003)

    The openargs does not work with me :-(
    This I have in the add form "on_load" and "on_open"
    If blnAdded = True Then
    Response = acDataErrAdded
    Else
    Me.selKlant.Undo
    Response = acDataErrContinue
    End If

    This code is behind the close (ok) button on the add form:
    If blnAdded = True Then
    Response = acDataErrAdded
    Else
    Me.selKlant.Undo
    Response = acDataErrContinue
    End If

    This code below is now behind the "not in list" combobox (when vbyes open the add form):
    If blnAdded = True Then
    Response = acDataErrAdded
    Else
    Me.selKlant.Undo
    Response = acDataErrContinue
    End If

    If necessary I strip my mdb and post it with it?

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: error when vbyes on not in list (2003)

    You can't use Response in frmKlantenAdd.

    I'll be offline for several hours, but if you post a stripped down and zipped copy of your database, I'll look at it tonight - or perhaps someone else will.

  10. #10
    Star Lounger
    Join Date
    Mar 2002
    Location
    Haaltert, Belgium
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: error when vbyes on not in list (2003)

    I don't have response in the add form.

    On the combobox "not in list" I've added following line:
    DoCmd.DoMenuItem acormBar, acRecordsMenu, 5, , acMenuVer70

    And now my combobox refresh with the added data in it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •