Feeding Combo Box

[Guest]

Hello,

I have a form that has a combo box field called "QC'd By". I have this bound
to a table called "QCby" that I have entered three names of people who QC. If
other people QC and manually type their name in the combo box it shows on the
form when you click back to it but the name doesn't show in the combo box or
report. How can I set up this combo box so when user types their name
directly in the combo box field it automatically updates the "QCby" table?

Thanks!

 

[Guest]

Put code in the After Updat event of your combo box to update the table if
the name entered is not in the table.

 

[Guest]

I'll give it a try, what's the code?

Put code in the After Updat event of your combo box to update the table if
the name entered is not in the table.

 

[Guest]

I was mistake on the location, it really should be in the Not In List event
and the Limit To List property should be set to yes.
Untested Air Code:


Private Sub QC'dby_NotInList(NewData As String, _
Response As Integer)
Dim ctl As Control
Dim rst as recordset

' Return Control object that points to combo box.
Set ctl = Me!QC'dby
' Prompt user to verify they wish to add new value.
If MsgBox("Value is not in list. Add it?", _
vbOKCancel) = vbOK Then
' Set Response argument to indicate that data
' is being added.
Response = acDataErrAdded
' Add string in NewData argument to row source.
Set rst = db.OpenRecordset("QCby", dbOpenDynaset)
With rst
.addnew
.fields(QCName) = NewData
.update
End With
rst.close
set rst = nothing
Else
' If user chooses Cancel, suppress error message
' and undo changes.
Response = acDataErrContinue
ctl.Undo
End If
End Sub



If msgbox( Me.cboMyList & " Is Not in the List - OK to Add or Cancel
if IsNull( DLookup("[CompanyName]", "Shippers", "[ShipperID] = 1")