Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Not in list question (a2003)

    I'm using an unbound combo box to find a record on my form. If there is no such entry in the combo box, I'd like to have the Not In List event procedure for the combo box open a new record. I've tried the following with no luck. Any suggestions?

    Private Sub cboType_NotInList(NewData As String, Response As Integer)
    DoCmd.GoToRecord , , acNewRec
    End Sub

    E

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

    Re: Not in list question (a2003)

    You can't move to another record in the On Not In List event procedure. You must either add a new record in code, or pop up another form in which the user can enter a new record. If you do a search for NotInList in this forum, you'll find several code examples.

  3. #3
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not in list question (a2003)

    I decided to use a second form to enter the new record. I'm having a devil of a time getting the cboFind (the combo box I use to seach for and display the needed recond) to display the value of the just added record.

    I've found a "how to" article that included code. My problem is that I don't know how to adjust the code to take into account that I have a two field contact name instead of the one field/control she uses.

    My cboFind displays the PK and a concatenation of the lastname and firstname felds/controls. And my add form includes each of these three fields (ContactID, FirstName, and LastName). Let me know if you need more info.

    Thanks for your assistance, E

    The code to support adding an entire new record is broken into two segments: one for the NotInList event, and one for a cmdClose button on the QDF form.

    Private Sub cboFind_NotInList(strNewData As String, _
    intResponse As Integer)

    Dim strTitle As String
    Dim intMsgDialog As Integer
    Dim strMsg1 As String
    Dim strMsg2 As String
    Dim strMsg As String
    Dim ctl As Control
    Dim strEntry As String
    Dim strFormName As String
    Dim frm As Form
    Dim intReturn As Integer

    strFormName = "fdlgContact"
    strEntry = "Contact"
    Set ctl = Me![cboFind]

    'Display a message box asking if the user wants to add
    'a new entry
    strTitle = strEntry & " Not in List"
    intMsgDialog = vbYesNo + vbExclamation + vbDefaultButton1
    strMsg1 = "Do you want to add "
    strMsg2 = " as a new " & strEntry & " entry?"
    strMsg = strMsg1 + strNewData + strMsg2
    intReturn = MsgBox(strMsg, intMsgDialog, strTitle)

    If intReturn = vbNo Then
    intResponse = acDataErrContinue
    ctl.Undo
    Exit Sub
    ElseIf intReturn = vbYes Then
    'Open form for adding new Contact
    ctl.Undo
    intResponse = acDataErrContinue
    DoCmd.OpenForm strFormName
    Set frm = Forms(strFormName)
    frm![txtContactName] = strNewData
    End If

    End Sub

    The QDF has a Close button and a Cancel button. If the user clicks the Cancel button, code (not listed here) cancels the saving of the new record. If the user clicks the Close button, the following code runs, to close the QDF, requery the main form (and the combo box), and select the new Contact in the combo box:
    Private Sub cmdClose_Click()

    Dim frm As Form
    Dim strForm As String
    Dim strContactID As String

    strForm = "frmAddContact"
    Set frm = Forms(strForm)
    strContactID = Me![txtContactID]

    DoCmd.Close acForm, Me.Name
    frm.Requery
    frm.[ContactID] = strContactID
    frm![cboFind].Requery

    End Sub

    As is standard practice, the cboFind combo box is bound to an AutoNumber ContactID field, but the Contact name is displayed in the combo box (this is done by setting the width of Column 1 to 0).

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

    Re: Not in list question (a2003)

    When you close the form where you have added the new record, you need to requery both the form and the combo, set the combo equal to the new contactID, then also run the code that normally executes in the afterupdate event to find that record.
    <pre> Dim frm As Form
    Dim strForm As String
    Dim lngContactID As long

    strForm = "frmAddContact"
    Set frm = Forms(strForm)
    lngContactID = Me![txtContactID]

    DoCmd.Close acForm, Me.Name
    frm.Requery
    frm![cboFind].Requery
    frm.[cboFind] = lngContactID
    frm.RecordsetClone.FindFirst "[ContactID] = " & frm![CboFind]
    frm.Bookmark = frm.RecordsetClone.Bookmark
    </pre>


    Note also that ContactID is a long integer not a string
    Regards
    John



  5. #5
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not in list question (a2003)

    Working sweet. One question tho: In the NotInList code, the NewData is inserted into a single control. What I'd like to do is insert everything to the left of the comma into the LastName control and everything to the right of the comma and space into the FirstName control. To complicate matters sometimes there is no comma, no space and nothing to add to the FirstName control. How is that done?

    Thanks for your help,
    e

  6. #6
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not in list question (a2003)

    (Edited by HansV to preserve indentation - inserted <!t>[tab]<!/t> tags)

    Have been working on the above. This is what I have so far:

    Private Sub cboFind_NotInList(strNewData As String, intResponse As Integer)
    Dim strTitle As String
    Dim intMsgDialog As Integer
    Dim strMsg1 As String
    Dim strMsg2 As String
    Dim strMsg As String
    Dim ctl As Control
    Dim strEntry As String
    Dim strFormName As String
    Dim frm As Form
    Dim intReturn As Integer
    Dim intCS As Integer
    Dim intC As Integer

    strFormName = "frmAddContacts"
    strEntry = "Contact"
    Set ctl = Me![cboFind]
    intCS = InStr(strNewData, ", ")
    intC = InStr(strNewData, ",")

    'Display a message box asking if the user wants to add
    'a new entry
    strTitle = strEntry & " Not in List"
    intMsgDialog = vbYesNo + vbExclamation + vbDefaultButton1
    strMsg1 = "Do you want to add "
    strMsg2 = " as a new " & strEntry & " entry?"
    strMsg = strMsg1 + strNewData + strMsg2
    intReturn = MsgBox(strMsg, intMsgDialog, strTitle)

    Select Case intReturn = vbNo
    Case vbNo
    intResponse = acDataErrContinue
    ctl.Undo
    Case vbYes
    'Open form for adding new Contact
    ctl.Undo
    intResponse = acDataErrContinue
    DoCmd.OpenForm strFormName
    With Forms(strFormName)
    Select Case True
    Case intCS > 0
    ![LastName] = left(strNewData, intCS - 1)
    ![FirstName] = Mid(strNewData, intCS + 2)
    Case intC > 0
    ![LastName] = left(strNewData, intC - 1)
    ![FirstName] = Mid(strNewData, intC + 1)
    Case Else
    ![LastName] = strNewData
    End Select
    End With
    End Select
    End Sub

    Needed to rework the If...Else and Select Case into a single structure. Currently getting the standard NotInList error message. Is there something wrong with the "With" line?

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

    Re: Not in list question (a2003)

    Elizabeth,

    Your code could be improved a bit, but I don't think it will do what you want. You are trying to do far too much with one combo box:
    - I don't think you can combine the "find" functionality with adding new data.
    - I don't think you can add composite names in the Not In List event.
    I would put a command button on the form to add new contacts, and have it requery the combo box.
    The combo box should just undo any attempt to enter a non-existing contact.

  8. #8
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Cedar Falls, Iowa, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not in list question (a2003)

    May look like a donkey pulling the Budweiser wagon but it works. I just needed to change "Select Case intReturn = vbNo" to "Select Case intReturn" and I was in business.

    Started with Helen Fedemma's code from Archon Article 8 and started modifying with John Hutchinson's fix. This cboFind will take the entry Winter, Jack, who is not in the list, pop up an "Add Contact" form and place Winter in the LastName control and Jack in the FirstName control. The OnClose event of the Add Contact takes care of the rest including setting the cboFind at Winter, Jack.

    Probably isn't the most elegant solution but it does work.

    E

Posting Permissions

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