Not in List - Function to use on multiple forms

[andy1968]

I would like to use a function for my not in list. I use the combo box on may forms, and would like simplify things by using a function.


Here is the code I've tried. It works as a subroutine, but when trying it as a function I am not sure how to transfer the NewData and Response:

Public Function EnterNewJob(NewData As String, Response As Integer)
Dim x As String
Dim frm As String
Dim ctl As String

frm = Screen.ActiveForm.Name
ctl = Screen.ActiveControl.Name

x = frm & "|" & ctl
Response = acDataErrContinue


Forms(frm).Controls(ctl).Undo

Dim ans As Variant

gbl_exit_name = False

ans = MsgBox("Contract Entered is not in the Database.  Do you want to add it?", _
vbYesNo, "Add New Contract?")

If ans = vbNo Then
Forms(frm).Controls(ctl) = Null
DoCmd.GoToControl ctl

GoTo exit_it
End If

' add Project

DoCmd.OpenForm ("frmEnterNewProject"), , , , , , x
Form_frmEnterNewProject.NameOfJob = NewData

exit_it:
Forms(frm).Controls(ctl).Value = NewData
End Function

 

[June7]

Can pass names of objects and controls as arguments. However, this will get very complicated if you intend to use with various subforms. Don't think I would bother with the effort.

 

[andy1968]

I've got the names of the objects and controls down, my trouble is with the NewData and Response:


Private Sub Contract_NotInList(NewData As String, Response As Integer)

 

[June7]

I expect the NotInList event will have to call Function and pass the NewData value. I have never tried nor seen code to do this. Might be able to test this weekend.

Please use CODE tags to retain indentation and readability. If you want to provide db for analysis, follow instructions at bottom of my post.

 

[andy1968]

Thanks for the tips on posting too.


I'll prepare a db for up loading later this week.

 

[moke123]

See if this works for you

 

[andy1968]

Cool! Thank moke123.


Used your method to call the function, and got my code to work.


Here is the call code I used:

Dim frm As String
Dim ctl As String
Dim ctlv As String
frm = Screen.ActiveForm.Name
ctl = Screen.ActiveControl.Name
Response = EnterNewJob(NewData, frm, ctl)

 

[Pat Hartman]

Personally I don't use the not in list event. I prefer to have tighter control over when new values are added. I find that when you make it too easy to add a new contact or something similar, people get sloppy and you end up with the same contact in the table multiple times with various misspellings of his name.

 

[moke123]

Cool! Thank moke123.

Used your method to call the function, and got my code to work.

Here is the call code I used:

Dim frm As String
Dim ctl As String
Dim ctlv As String
frm = Screen.ActiveForm.Name
ctl = Screen.ActiveControl.Name
Response = EnterNewJob(NewData, frm, ctl)

I'm assuming you changed what I posted. Theres no need for screen.activeAnything. The procedure I posted is generic and will work with any combobox. the code I posted only requires a call like below in the controls NotInList event

Private Sub cboFruit_NotInList(NewData As String, Response As Integer)
Response = AddNewToList(NewData, "tblFruit", "Fruit", "Fruits")
End Sub

I also concur with Pat in that I only use it for the simplest of things and I use the last argument in the code I posted to open a form. From there you can do some easy validation if needed or add additional info.