Add item to combo box

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a combo box on a subform which gets it's values from a table. I need
to be able to add items that are not in the combo box to the table. It is
for ordering products so if it is a new product we haven't ordered before, it
would be nice to add it for future reference in case we order it again. So
far it will only let me select the items that are already in the table (which
I entered manually). Please Help!

Thanks!
 
Use the notinlist event of the combo box. This is described in "Access
Developer's Handbook" (Litwin, Getz, Gilbert). Following are two examples.
The first opens a form to allow a new part number to be added. The second
example just fills in information without opening a form.

Sample One:

Private Sub cboPartNo_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_Label
Dim strMessage As String
Dim strFormName As String
strMessage = "Part Number: '" & UCase$(NewData) & "' is not in the
list. " _
& vbCrLf & "Would you like to add it?"
If MsgBox(strMessage, vbYesNo + vbQuestion, "Add New Part") = vbYes
Then
strFormName = "frmTool_PartAddShort"
DoCmd.OpenForm strFormName, acNormal, , , acFormAdd, acDialog,
UCase$(NewData)
Response = acDataErrAdded
Else
Response = acDataErrDisplay
End If

Exit_Label:
Exit Sub
Err_Label:
MsgBox Err.Description
Resume Exit_Label
End Sub

Sample Two:

Private Sub Country_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_Label
Dim strMessage As String
Dim db As DAO.Database
Dim rst As DAO.Recordset
strMessage = "'" & UCase$(NewData) & "' is not in the list. " _
& vbCrLf & "Would you like to add it?"
If MsgBox(strMessage, vbYesNo + vbQuestion, "New Country") = vbNo Then
Response = acDataErrDisplay
Else
Set db = CurrentDb()
Set rst = db.OpenRecordset("tlkCountry")
With rst
.AddNew
.Fields("Country") = UCase$(NewData)
.Update
End With
Response = acDataErrAdded
End If
Exit_Label:
On Error Resume Next
rst.Close
db.Close
Set rst = Nothing
Set db = Nothing
Exit Sub
Err_Label:
MsgBox Err.Description
Resume Exit_Label
End Sub
 
Thanks! That worked!

Bill Edwards said:
Use the notinlist event of the combo box. This is described in "Access
Developer's Handbook" (Litwin, Getz, Gilbert). Following are two examples.
The first opens a form to allow a new part number to be added. The second
example just fills in information without opening a form.

Sample One:

Private Sub cboPartNo_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_Label
Dim strMessage As String
Dim strFormName As String
strMessage = "Part Number: '" & UCase$(NewData) & "' is not in the
list. " _
& vbCrLf & "Would you like to add it?"
If MsgBox(strMessage, vbYesNo + vbQuestion, "Add New Part") = vbYes
Then
strFormName = "frmTool_PartAddShort"
DoCmd.OpenForm strFormName, acNormal, , , acFormAdd, acDialog,
UCase$(NewData)
Response = acDataErrAdded
Else
Response = acDataErrDisplay
End If

Exit_Label:
Exit Sub
Err_Label:
MsgBox Err.Description
Resume Exit_Label
End Sub

Sample Two:

Private Sub Country_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_Label
Dim strMessage As String
Dim db As DAO.Database
Dim rst As DAO.Recordset
strMessage = "'" & UCase$(NewData) & "' is not in the list. " _
& vbCrLf & "Would you like to add it?"
If MsgBox(strMessage, vbYesNo + vbQuestion, "New Country") = vbNo Then
Response = acDataErrDisplay
Else
Set db = CurrentDb()
Set rst = db.OpenRecordset("tlkCountry")
With rst
.AddNew
.Fields("Country") = UCase$(NewData)
.Update
End With
Response = acDataErrAdded
End If
Exit_Label:
On Error Resume Next
rst.Close
db.Close
Set rst = Nothing
Set db = Nothing
Exit Sub
Err_Label:
MsgBox Err.Description
Resume Exit_Label
End Sub
Click to expand...
 
Back
Top