subform Not In List

[dynamictiger]

I have made a form to handle not in list events. It works fantastic until I hit a subform.

The procedure works by setting properties in the calling form for the the recordsource of the combobox and the field we are trying to add new data too. The newdata and the formname are passed as openArgs.

As you can see my problem is that the subform Me.Name property returns the form name of the subform not the name the subform is opened by. I have tried playing around with string expressions to make this work for a subform but don't seem to be able to get the correct syntax.

Anyone got a better idea?

 

[Fornatian]

I use this function:

Public Function Add2Source(tbl As String, fld2update As String, NewData As String) As Integer
'Adds record to the list if not already present
'This only works if there is just one field to update....
Dim strMessage As String
Dim dbs As Database
Dim RstTypes As Recordset
Dim Response As Integer
strMessage = "'" & NewData & "' is not in current list" & _
" To add the item for future reference choose yes, or choose no to select from the present options."
Response = MsgBox(strMessage, vbYesNo, "Not in List")
If Response = vbYes Then
Set dbs = CurrentDb()
Set RstTypes = dbs.OpenRecordset(tbl)
RstTypes.AddNew
RstTypes.Fields(fld2update) = NewData
RstTypes.Update
Add2Source = acDataErrAdded
dbs.Close
Else
Add2Source = acDataErrDisplay
End If
strMessage = ""
Set dbs = Nothing
Set RstTypes = Nothing
End Function

and call it as follows:

Private Sub AssessmentUndertaken_by__NotInList(NewData As String, Response As Integer)
Response = Add2Source("Officers", "Name", NewData)
End Sub

if that helps, as you can see it does not rely on form references, it just needs the name of table, name of field and NewData to be passed to add the value to the source table.