Results 1 to 4 of 4
  1. #1
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Not in list for combo box (Access@003 SP2)

    I have a table called tbl Company and it holds :
    CompanyID Autonumber
    Company Text (100 characters)

    I have another table called tbl Participants that holds CompanyID amongst other fields.

    Is there a way to use a Combo box that has the bound field no shown but the company name shown and if it doesn't appear in the tbl Company table to put it there.

    My problem is that Access tells me that the bound column must be visible, my bound column is in fact the CompanyID.

    Am i up for deleting the Autonumber field in tbl Company and just have the company name in here as well as the table tbl Participants, in this way of course the company name is recorded in both tables, somthing i was trying to aviod.
    Hope this makes sense.

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not in list for combo box (Access@003 SP2)

    Hi Pat,
    You should set the ColumnWidths property of the combobox to 0 ; 1 that way you won't see the bound column.
    Here's some sample code to achieve what you're after:
    <pre>Private Sub CompanyID_NotInList(NewData As String, Response As Integer)
    Dim intNew As Integer
    intNew = MsgBox("Do you want to add a new company?", vbYesNo)
    If intNew = vbYes Then
    RunCommand acCmdUndo
    Response = acDataErrContinue
    DoCmd.OpenForm FormName:="Companies", datamode:=acFormAdd
    Forms!Companies!CompanyName = NewData
    Else
    MsgBox "The company name you entered isn't an existing customer."
    RunCommand acCmdUndo
    Response = acDataErrContinue
    End If
    End Sub</pre>


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

    Re: Not in list for combo box (Access@003 SP2)

    Another possibility is show in the code below.
    <code>
    Private Sub cboCompanyID_NotInList(NewData As String, Response As Integer)
    Dim rst As DAO.Recordset

    On Error GoTo ErrHandler
    If MsgBox(NewData & " isn't in the list. Do you wish to add it?", _
    vbYesNo + vbQuestion, "Add new value?") = vbYes Then
    Set rst = CurrentDb.OpenRecordset(Me.cboCompanyID.RowSource)
    rst.AddNew
    rst.Fields(1) = NewData
    rst.Update
    Response = acDataErrAdded
    Else
    Response = acDataErrContinue
    End If

    ExitHandler:
    On Error Resume Next
    rst.Close
    Set rst = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub
    </code>
    Replace cboCompanyID with the name of the combo box. The code opens a recordset on the company table and adds a record. Since the primary key is an AutoNumber, it will automatically be assigned a value.

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Not in list for combo box (Access@003 SP2)

    Thank you Hans, an elegant solution.

    Groveli, your solution would have worked too but Hans has provided a solution that does not require an additional form.

    I had got stuck with the Response values.

Posting Permissions

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