A
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
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