Implementing public VBA function in Access 2003, passing multiple parameters

#11

Marshall Barton wrote:

Quote:

Arsene (AT) selenium (DOT) net wrote:

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

Arsene (AT) selenium (DOT) net wrote:

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

Arsene (AT) selenium (DOT) net wrote:

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

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

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

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

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.

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.

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

I totally understand. When helping we cut and paste code from a test or
from existing code, maybe write code on the fly, etc. I guess it
depends on the OP and how conversent he/she is with programming.

#12

On Mon, 14 Mar 2011 10:31:04 -0500, Marshall Barton
<marshbarton (AT) wowway (DOT) com> wrote:

Quote:

Arsene (AT) selenium (DOT) net wrote:

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

Arsene (AT) selenium (DOT) net wrote:

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

Arsene (AT) selenium (DOT) net wrote:

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

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

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

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

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.

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.

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.

Marshall,

The code was 99% good as originally posted, and an assist from Salad
got me there. I cannot thank you fellows enough for your help.

Arsene

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Implementing public VBA function in Access 2003, passing multiple parameters

comp.databases.ms-access comp.databases.ms-access

Re: Implementing public VBA function in Access 2003, passing multipleparameters - 03-14-2011 , 10:39 AM

Re: Implementing public VBA function in Access 2003, passing multiple parameters - 03-14-2011 , 02:24 PM