Look into NotInList. There is a special event that you can code to allow the
entering of a new value to your combo box, as long as there is a physical
table to add the new entry.
The code below works for Access 2000 and above only.
This code goes in the form with the combo box. Note below that my combo box
is named: cboFather and the event is NotInList.... If you look at the
properties of the combo box under name, this is where I typed cboFather. In
your case you will substitute your subject name, such as cbomeds or
cbomedications. Substitute "Invalid Relative" with "Invalid Medication" (or
any other message you wish to convey); substitute "frmRelatives" with the
name of the form you will be opening in order to add the new value, such as
frmMedications. Yes, make a form to enter your new value to your combo box
source table. All other code stays as written.
Private Sub cboFather_NotInList(NewData As String, Response As Integer)
Dim mbrResponse As VbMsgBoxResult
Dim strMsg As String
strMsg = NewData & _
" is not in the list. " & _
"Would you like to add it?"
mbrResponse = MsgBox(strMsg, _
vbYesNo + vbQuestion, "Invalid Relative")
Select Case mbrResponse
Case vbYes
DoCmd.OpenForm "frmRelatives", _
DataMode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=NewData
'Stop Here and wait until the form goes away
If IsLoaded("frmRelatives") Then
Response = acDataErrAdded
Me.Requery
DoCmd.Close acForm, "frmRelatives"
Else
Response = acDataErrContinue
End If
Case vbNo
Response = acDataErrContinue
End Select
End Sub
Private Function IsLoaded(strName As String, _
Optional lngType As AcObjectType = acForm) As Boolean
IsLoaded = (SysCmd(acSysCmdGetObjectState, lngType, strName) <> 0)
End Function
-----------------------------------------------
This code goes in the form used to add the new data. In frm Medication
create two command buttons, one named cmdCancel and the other cmdOK. You can
type this code verbatim, without having to select the Click event directly
in the command button.
Private Sub cmdCancel_Click()
Me.Undo
DoCmd.Close acForm, Me.Name
End Sub
Private Sub cmdOK_Click()
Me.Visible = False
End Sub
Private Sub Form_Load()
If Len(Me.OpenArgs) > 0 Then
Me.LName = Me.OpenArgs ' substitue the .LName with the field name
from your table, such as Me.MedName
End If
End Sub
After typing all this in to both areas, select Debug Compile while you are
still in the Code Editor window. This will help you find any typos or
problems with the code.
