Display new record option upon NotInList event in combobox

[Kevin via AccessMonster.com]

Hello all,

I have an unbound combobox (Combo12) that looks up a value (Medical Record
Number) from the table (Patient Information). The entry of a value causes a
second unbound combobox (Combo16) to display the various visit dates
associated with that patient. The selection of one of these dates causes a
subform with patient information to be updated to that specific visit (so
that changes/additions can be made). This system is working well, however, I
would like to be able to add information about new visits as well.

When I try to change the property of Combo16 to allow values not in the list,
I receive a message to the effect of "Access cannot change the property to no
right now," and that "The first visible column, which is determined by the
ColumnWidths property, isn't equal to the bound column. Adjust the
ColumnWidths property first, and then set the LimitToList property." The
current column widths are 0";1.2" where 0 corresponds to the bound column
that is the Master Link to the subform, and 1.2 corresponds the unbound date
that is displayed. I'm reticent to change these settings, as they allow me to
accurately look up a MR Number, select a date, and then view/change the
information that is presented in the subform.

The main form is based on a Patient Information table (PK=Medical Record
Number), while the subform is based on a Patient Visits table (PK=Chart ID
Number). Is there any way to change the "NotInList" property so that the user
will receive a prompt asking him/her whether he/she wants to create a new
record in the Patient Visits table and then bring up an empty subform for
data entry? Thanks in advance for any help you can offer.

Kevin

 

[B. Edwards]

Assuming LimitToList is set to Yes, then in the NotInList event for the
combo box something like (air-code, not tested):

DIM intResponse As Integer
Dim strMessage As String
Dim strFormName as string
strFormName = "frmEditForm"
strMessage = "The entry " & NewData & " is not currently listed." & vbCRLF &
"Would you like to add it?"
intResponse = MsgBox (strMessage, vbYesNo + vbDefaultButton2, "Add New
Entry")
If intResponse = vbYes then
DoCmd.OpenForm strFormName, acNormal, acFormAdd, acDialog
Response = acDataErrAdded
Else
Response = acDataErrDisplay
End If

 

[Kevin via AccessMonster.com]

B. Edwards,

Thanks for the response. Unfortunately, I have very little familiarity with
VBA coding; could you please explain what substitutions I will need to make
in the code that you presented? Thanks again.

Kevin

Assuming LimitToList is set to Yes, then in the NotInList event for the
combo box something like (air-code, not tested):

DIM intResponse As Integer
Dim strMessage As String
Dim strFormName as string
strFormName = "frmEditForm"
strMessage = "The entry " & NewData & " is not currently listed." & vbCRLF &
"Would you like to add it?"
intResponse = MsgBox (strMessage, vbYesNo + vbDefaultButton2, "Add New
Entry")
If intResponse = vbYes then
DoCmd.OpenForm strFormName, acNormal, acFormAdd, acDialog
Response = acDataErrAdded
Else
Response = acDataErrDisplay
End If

Hello all,

[quoted text clipped - 32 lines]

Kevin