drop down combo box

  • Thread starter Thread starter Dan
  • Start date Start date
D

Dan

i have a form where users input a lender name in and they
choose the lender by clicking on a drop down combo box.
if the lender is not there is there a way the users can
type i a new lender? like adding a new lender to the
drop down list
 
Good timing; I just got help on this last week.

The strategy is to use the NotInList property of the combo
box. Limit to List must be set to Yes for the event
procedure to be triggered. The procedure could either
open up another second form, if there are many fields to
be entered for the new record, or just automatically add
the typed value to the list (all other fields in the
source table would be left blank). The following code
does the latter:

Private Sub cboBidType_NotInList(NewData As String,
Response As Integer)
' When copying to new procedure, change strTblName,
strThing, and fieldname
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String
Dim strTblName As String

strTblName = "tblBidTypeList" ' Your Lookup Table Name
strThing = "Bid Type" ' The type of "thing" in
the Lookup table
strMsg = "'" & NewData & "' is not in the lookup list
stored in " & strTblName & vbCrLf & vbCrLf
strMsg = strMsg & "Add to lookup table? "

' Ask the user if he'd like to add the new value to the
lookup table
If MsgBox(strMsg, vbQuestion + vbYesNo, "Add New " &
strThing & "?") = vbNo Then
' Don't add it, return user to the combo box
Response = acDataErrContinue
Else
' Create a recordset clone, and add the new record
Set db = CurrentDb
Set rs = db.OpenRecordset(strTblName, dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!BidType = NewData
rs.Update

If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If

' Clean up
rs.Close
Set rs = Nothing
Set db = Nothing
End If
End Sub


HTH
Kevin Sprinkel
 
i have a form where users input a lender name in and they
choose the lender by clicking on a drop down combo box.
if the lender is not there is there a way the users can
type i a new lender? like adding a new lender to the
drop down list
Click to expand...

The combo box's "NotInList" event procedure would be the way to go. You can use
the following method (it requires that you have, or set, a reference to the DAO
Object Library appropriate for your version of Access).

See http://support.microsoft.com/default.aspx?kbid=197110.

'*****EXAMPLE START
Private Sub cboColors_NotInList(NewData As String, _
Response As Integer)

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String

'Open recordset containing no existing rows by
'filtering to non-existent value (faster)
strSQL = "SELECT Color " & _
"FROM tblColorsLkp " & _
"WHERE ColorID=0"

'Prompt user to add or not
If MsgBox("""" & NewData & """ is not in the list. " _
& "Would you like to add it?" _
, vbYesNo + vbQuestion, "New Value") = vbYes Then
'User said "Yes"
'Open recordset and add new value
Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
With rs
.AddNew
!Color = NewData
.Update
End With
'Tell Access that the value has been added
' and to requery combo box
Response = acDataErrAdded
Else 'User said "No"
'Tell Access to show default error message
Response = acDataErrDisplay
End If

End Sub
'*****EXAMPLE END

If you need further assistance with this, just post back with any questions you
might have.
 
i have a form where users input a lender name in and they
choose the lender by clicking on a drop down combo box.
if the lender is not there is there a way the users can
type i a new lender? like adding a new lender to the
drop down list
Click to expand...

You can use the combobox's NotInList event.
Set it's LimitToList property to Yes and then use the Not In List
event to add the new data.

- Jim
 
how do i use the notInList event what would i put in
there?
Click to expand...
Assumptions:
Your combo box use a query for it's RowSource.
You have a table for lenders (tblLender).
Your combo box is named cboLender.

The code would be something like...
---<watch for newsreader wrap>------

Private Sub cboLender_NotInList(NewData As String, Response As
Integer)

If MsgBox(NewData & " was not found in the Lender table." _
& vbCrLf & vbCrLf & "Would you like to add it?", _
vbYesNo + vbDefaultButton2, _
"Confirm New Lender") = vbNo Then
Response = acDataErrContinue
Else
'Add new lender
CurrentDb.Execute "INSERT INTO tblLender (LenderName) VALUES
('" & NewData & "');", dbFailOnError
Response = acDataErrAdded
End If

End Sub
--------------------------------------------
The main idea being to use NewData to update your source - if the user
confirms it. And change the Response depending on whether it was added
or not. Response=acDataErrAdded will update the combo box.


- Jim
 
Back
Top