Implementing public VBA function in Access 2003, passing multiple parameters

[Arsene]

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

 

[Stuart McCall]

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

OpenModule won't do what you want. It'll open a module in 'design view', ie
for you to type in code. You need to pass the field, table etc. as
parameters to your function. Try this code:

http://www.smccall.demon.co.uk/Forms.htm#AutoAdd

which I've been using for years without trouble. It takes an extra optional
parameter for the name of a form to pop-up in case you need the user to
complete more fields in the record. Use it as the comments illustrate. Hope
that helps you.

 

[Arsene]

On Sun, 13 Mar 2011 01:44:14 -0000, "Stuart McCall"

Stuart,

Thanks much for the great code (you have a fantastic web site, by the
way).

I am having a bit of trouble making this function work. I think that
after I copy this function into a module, I then have to call it from
the "Not in List" event of my combo box. I would do that via the
following line:

=AutoAdd(["NewData"],["add2table"],["fieldName"],["popForm"])

In the above expression, I then replace "add2table" and "fieldName"
with the names of my own table and data field, as shown below. In
addition, as I will not be passing a fourth parameter, I leave the
last comma in place followed by the closing parenthesis.

AutoAdd("NewData", [name.of.my.add.2.table],
[name.of.my.add.2.table]![ name.of.my.add.2.data.field] ,)

However, this is obviously not working as I do not know how to
properly reference the "NewData" as one of the arguments of the
function.

Just one small push and I'll get there (me thinks).

Thanks !!!

 

[Arsene]

OpenModule won't do what you want. It'll open a module in 'design view', ie
for you to type in code. You need to pass the field, table etc. as
parameters to your function. Try this code:

http://www.smccall.demon.co.uk/Forms.htm#AutoAdd

which I've been using for years without trouble. It takes an extra optional
parameter for the name of a form to pop-up in case you need the user to
complete more fields in the record. Use it as the comments illustrate. Hope
that helps you.

Stuart,

Thanks much for the great code. I am having a bit of trouble making it
work. I think that after I copy this function into a module, I then
have to call it from the "Not in List" event of my combo box. I would
do that via the following line:

=AutoAdd(["NewData"],["add2table"],["fieldName"],["popForm"])

In the above expression, I then replace "add2table" and "fieldName"
with the names of my own table and data field, as shown below. In
addition, I will not be passing a fourth parameter, so I leave the
last comma in place followed by the closing parenthesis.

AutoAdd("NewData", [name.of.my.add.2.table],
[name.of.my.add.2.table]![ name.of.my.add.2.data.field] ,)

However, this is obviously not working as I do not know how to
properly reference the "NewData" in the argument section of the
function.

Just one small push and I'll get there (me thinks).

Thanks !!!

 

[Stuart McCall]

On Sun, 13 Mar 2011 01:44:14 -0000, "Stuart McCall"

Stuart,

Thanks much for the great code (you have a fantastic web site, by the
way).

Thanks for your kind words.

I am having a bit of trouble making this function work. I think that
after I copy this function into a module, I then have to call it from
the "Not in List" event of my combo box. I would do that via the
following line:

=AutoAdd(["NewData"],["add2table"],["fieldName"],["popForm"])

Nope. You can't use that function as an expression in the property sheet. It
must be called from the NotInList event procedure, because the function's
return value must be assigned to the Response parameter, which Access has
passed to the procedure. So, say your combo is called MyCombo, your field to
update is called MyField and the table to update is called MyTable, the code
in the NotInList event should be:

Response = AutoAdd(Me.MyCombo, "MyField", "MyTable")

or:

NewData = Me.MyCombo
Response = AutoAdd(NewData, "MyField", "MyTable")

Hope that gets you going. (I think I'll update the comments to clarify the
issue)

In the above expression, I then replace "add2table" and "fieldName"
with the names of my own table and data field, as shown below. In
addition, as I will not be passing a fourth parameter, I leave the
last comma in place followed by the closing parenthesis.

AutoAdd("NewData", [name.of.my.add.2.table],
[name.of.my.add.2.table]![ name.of.my.add.2.data.field] ,)

However, this is obviously not working as I do not know how to
properly reference the "NewData" as one of the arguments of the
function.

Just one small push and I'll get there (me thinks).

Thanks !!!

OpenModule won't do what you want. It'll open a module in 'design view',
ie
for you to type in code. You need to pass the field, table etc. as
parameters to your function. Try this code:

http://www.smccall.demon.co.uk/Forms.htm#AutoAdd

which I've been using for years without trouble. It takes an extra
optional
parameter for the name of a form to pop-up in case you need the user to
complete more fields in the record. Use it as the comments illustrate.
Hope
that helps you.

 

[Arsene]

On Sun, 13 Mar 2011 01:44:14 -0000, "Stuart McCall"

Stuart,

Thanks much for the great code (you have a fantastic web site, by the
way).

Thanks for your kind words.

I am having a bit of trouble making this function work. I think that
after I copy this function into a module, I then have to call it from
the "Not in List" event of my combo box. I would do that via the
following line:

=AutoAdd(["NewData"],["add2table"],["fieldName"],["popForm"])

Nope. You can't use that function as an expression in the property sheet. It
must be called from the NotInList event procedure, because the function's
return value must be assigned to the Response parameter, which Access has
passed to the procedure. So, say your combo is called MyCombo, your field to
update is called MyField and the table to update is called MyTable, the code
in the NotInList event should be:

Response = AutoAdd(Me.MyCombo, "MyField", "MyTable")

or:

NewData = Me.MyCombo
Response = AutoAdd(NewData, "MyField", "MyTable")

Hope that gets you going. (I think I'll update the comments to clarify the
issue)

In the above expression, I then replace "add2table" and "fieldName"
with the names of my own table and data field, as shown below. In
addition, as I will not be passing a fourth parameter, I leave the
last comma in place followed by the closing parenthesis.

AutoAdd("NewData", [name.of.my.add.2.table],
[name.of.my.add.2.table]![ name.of.my.add.2.data.field] ,)

However, this is obviously not working as I do not know how to
properly reference the "NewData" as one of the arguments of the
function.

Just one small push and I'll get there (me thinks).

Thanks !!!

OpenModule won't do what you want. It'll open a module in 'design view',
ie
for you to type in code. You need to pass the field, table etc. as
parameters to your function. Try this code:

http://www.smccall.demon.co.uk/Forms.htm#AutoAdd

which I've been using for years without trouble. It takes an extra
optional
parameter for the name of a form to pop-up in case you need the user to
complete more fields in the record. Use it as the comments illustrate.
Hope
that helps you.

Understood. It is working now, thank you so very much.