| You may want to
automatically add data to a combo box. 2 possibilities exist:
1. Add a single field:
After setting the Limit to List property to Yes, add the following code to
the Not In List Event:
TableName is the table where the
data should be added
FieldName is the field in which the data should be added
Private Sub cboFieldName_NotInList(NewData As String, Response As
Integer)
Dim db As Database, rs As Recordset
Dim strMsg As String
strMsg = "'" & NewData & "' is not a current name in the
list. "
strMsg = strMsg & " Do you want to add it to the list?"
strMsg = strMsg & "@Click Yes to add it or No to retype it."
If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?") =
vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("TableName",
dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!FieldName
= NewData
rs.Update
If Err Then
MsgBox
"Error. Please try again."
Response =
acDataErrContinue
Else
Response =
acDataErrAdded
End If
End If
End Sub
|
Important Note: Code was adapted from
Dev Ashish's Access page
2. Add multiple fields:
After setting the Limit to List property to Yes, add the following code to
the Not In List Event:
TableName is the table where the data should be added
FieldID is the key field
FieldName is the filed in which the data should be added
FormName is the form used to add additional data for the record
Private Sub cboFieldID_NotInList(NewData As String, Response As
Integer)
Dim db As Database
Dim rs As Recordset
Dim lngFieldID As Long
If vbYes = MsgBox("'" & NewData & "' is not a current name in
the list." & vbCrLf & "Do you want to add it to the list?",
vbQuestion + vbYesNo, " ") Then
Set db = DBEngine(0)(0)
Set rs = db.OpenRecordset("SELECT * FROM [TableName] WHERE
1=2;")
With rs
.AddNew
![FieldName] = NewData
lngFieldID = ![FieldID]
.Update
End With
rs.Close
Set rs=Nothing
Set db=Nothing
DoCmd.OpenForm "FormName", , , "[FieldID]=" & lngFieldID
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub |
Important Note: Code was adapted from
applecore99.com Access page |