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

[patt]

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.

[grovelli]

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>

[HansV]

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.

[patt]

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.