Hello, everyone.

I have a large application, with several forms, containing numerous
data fields (mostly bound text boxes). I have converted some of those
text boxes to combo boxes, so instead of entering new data by typing
it in, a user can pick from a drop-down list of values supplied by
what I refer to as a lookup table. If the user needs to enter a new
value that is not already contained in the lookup table, I want the
user to have the option to add this new value into the lookup table,
so it will also available from that point on from the drop down list.

This can be accomplished using the combo box's "On Not in List" event
and some VBA code. So far, so good. The problem is, I have so many of
those combo boxes, and find myself repeating almost the exact same
block of VBA code time and again ad nauseum, I thought it would be
more efficient if I placed that block of code one single time in a
public function or a public subroutine in a module, and then just
call it time and again from each combo box's "On Not in List" event.

The code below is meant to accomplish this. The private Sub
"Item_NotInList" is invoked by the combo box's "On Not in List" event.
This private sub in turn calls a public function (named in this
example "fnc_Not_In_List") located in a module named "Utilities.

I think the private sub needs to pass to the public function three (3)
arguments, as follows:

1. The new value entered by the user, which was not found in the
existing list (NewData)
2. The name of the lookup table to which the new value may be added
3. The ame of the data fild in the lookup table that will receive the
new value

I am having a problem witht the proper syntax for passing and receving
arguments.

Any help would be greeatly appreciated.

Thanks

Private Sub Item_NotInList()
DoCmd.OpenModule "Utilities", "fnc_Not_In_List"
End Sub

Public Function fnc_Not_In_List()

Dim db As Database, rs As DAO.Recordset
Dim strMsg As String
strMsg = "'" & NewData & "' is not available in the List. " & vbCrLf
& " Do you want to add it to the List?" & vbCrLf & " Click Yes to add
or No to re-type it. "

If MsgBox(strMsg, vbQuestion + vbYesNo, " Add new item to list? ") =
vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("name_of_lookup_table_here",
dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!name_of_data_field_here = NewData
rs.Update
If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If
End If

End Function

Arsene (AT) selenium (DOT) net wrote:

I think it should be more like:

Sub Combo0_NotInList(NewData As String, Response As Integer)
Response = fnc_Not_In_List(NewData, Response, _
"name_of_lookup_table_here", _
"name_of_data_field_here")
End Sub

Public Function fnc_Not_In_List(NewData As String, _
Response As Integer, _
LookupTable As String, _
DataField As String) As Integer
Dim db As Database, rs As DAO.Recordset
Dim strMsg As String
strMsg = "'" & NewData & "' is not available in the
List." _
& vbCrLf & " Do you want to add it to the
List?" _
& vbCrLf & " Click Yes to add or No to
re-type it. "

If MsgBox(strMsg, vbQuestion + vbYesNo, " Add new item to
list? ") =
vbNo Then
fnc_Not_In_List = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset(LookupTable, dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs(DataField) = NewData
rs.Update
If Err Then
MsgBox "An error occurred. Please try again."
fnc_Not_In_List = acDataErrContinue
Else
Response = acDataErrAdded
End If
End If

End Function

--
Marsh

On Sun, 13 Mar 2011 11:12:37 -0500, Marshall Barton
<marshbarton (AT) wowway (DOT) com> wrote:

Marshall,

Thanks for the code re-write. This appears to be working to a point,
as follows:

When I enter a value not present in my lookup table, the appropriate
message comes up, asking me if I want to add it. After selecting Yes,
the combo box drop down list opens, showing all available values in
the lookup table (but not showing the one I just requested added).
When I try to move forward to the next data field with the tab key,
the same previous popup message telling me the value is not in the
lookup list pops up a second time, and when I select Yes this second
time, a "An error has occured. Please try again" message pops up. It
is only when I either select one of the values listed or else blank
out the data field that I am allowed to continue to the next data
field in the form.

Interestingtly, when I later inspect the lookup table I see the new
value was indeed properly added, as desired.

The code you provided seems to do exactly what I want, and I cannot
see any obvious bug. Any thoughts? Maybe a requery or something like
that is needed at a certain point?

At any rate, you have done 99% of my work and I am very thankful.

Arsene

Arsene (AT) selenium (DOT) net wrote:

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

I think it should be
If Err Then
MsgBox "An error occurred. Please try again."
fnc_Not_In_List = acDataErrContinue
Else
fnc_Not_In_List = acDataErrAdded
End If

I don't believe Response is even necessary to be passed to the function
and could be removed if you want.

Salad wrote:
Good catch Salad.

I don't see how the Response can be set accoring to the
function's code if it is not passed back to the NotInList
event procedure.

--
Marsh

On Sun, 13 Mar 2011 13:37:43 -0500, Salad <salad (AT) oilandvinegar (DOT) com>
wrote:

Salad,

You're right on both accounts. The one-line code change
(fnc_Not_In_List = acDataErrAdded) made the function work perfectly.
In addition, I removed "Response" from both the Subroutine and the
Function and the code works just as well.

Marshall and Salad, thank you so very much for your help.

Arsene

On Sun, 13 Mar 2011 13:56:43 -0500, Marshall Barton
<marshbarton (AT) wowway (DOT) com> wrote:

It does work, Marshall, I've tried time and again without "Response",
and the correct new data is being added to both my lookup table and to
the table I am entering data into.

Arsene (AT) selenium (DOT) net wrote:

I think Marshall was not commenting on a line like
Response = function(parameters)
but the fact that the Response value was not returned from the function.

Glad it's all working now.

On Sun, 13 Mar 2011 15:57:17 -0500, Salad <salad (AT) oilandvinegar (DOT) com>
wrote:


Correct.

What I removed, was the word "Response", previously part of the
function's argument, so the line went from reading:

Response = fnc_Not_In_List(NewData, Response, "lookup_table_name",
"data_field_name")

to reading:

Response = fnc_Not_In_List(NewData, "lookup_table_name",
"data_field_name")

Arsene (AT) selenium (DOT) net wrote:


You're correct about the response argument not being used in
the function. It was a failure on my part to clean up the
code before I posted. The important point is that the
NotInList procedure must set the Response argument in the
event procedure to tell Access to requery the combo box so
it can include the new entry in the list. I apologize for
the confusion caused by my failure to finish the code.

--
Marsh