Hi
In my attempt to keep the post shorter I have perhaps miscommunicated what I am having trouble with.
The Function I am modifying is this
Code:
Public Function AddNewToList(NewData As String, stTable As String, _ stFieldName As String, strPlural As String, strLinked As String, _
Optional strNewForm As String) As Integer
On Error GoTo err_proc
'Adds a new record to a drop down box list
'If form name passed, then open this form to the newly created record
'Declare variables
Dim rst As DAO.Recordset
Dim IntNewID As Long
Dim strPKField As String
Dim strMessage As String
' Display message box asking if user wants to add the new item
strMessage = "'" & NewData & "' is not in the current list. " & Chr(13) & Chr(13) & _
"Do you want to add it to the list of " & strPlural & "?" & Chr(13) & Chr(13) & _
"(Please check the entry before proceeding)."
If MsgBox(strMessage, vbYesNo + vbQuestion + vbDefaultButton2, "Add New Data") = vbYes Then
Set rst = CurrentDb.OpenRecordset(stTable, , dbAppendOnly)
rst.AddNew
rst(stFieldName) = NewData 'Add new data from combo box
rst(strLinked) = Forms!frm_CombinedData.cboSubCat_FK 'How do I pass the name of the current form as an argument??
strPKField = rst(0).Name 'Find name of Primary Key (ID) Field
rst.Update
rst.Move 0, rst.LastModified
IntNewID = rst(strPKField)
'if a form specified, then open the form with the primary key equal to the new record ID as the criteria
If strNewForm <> "" Then DoCmd.OpenForm strNewForm, , , strPKField & "=" & IntNewID, , acDialog
AddNewToList = acDataErrAdded 'Set response 'Data added'
Else
AddNewToList = acDataErrContinue 'Set response 'Data NOT added'
End If
exit_proc:
I added
Code:
rst(strLinked) = Forms!frm_CombinedData.cboSubCat_FK
to write the current ID value of the combobox one level above to the FK of the table proving the Row Source values for the cboBox I am calling the NotInList event from.
strLinked variable = the current ID value in "cbosubcat_FK" and is 2nd of 3 cascading comboxes. All comboboxes are bound controls in my form.
Everything is working but I would like the option to re-use the function from other forms where I have other cascading combo boxes. Obviously I need to change the reference to the Form and cboControl name and was hoping to do this by passing an argument (well two actually the Form name and the cboxControl Name ) from the NotInList event of which ever combobox I am working with.
I could probably have dispensed with any mention of the fact this part of a NotinList function but wasn't sure whether there were differences with the arguments permitted for a NotinList event compared to say 'onCurrent' - I don't suppose there is but wanted it known this was the event type I was calling the Function from just in case it made any difference?