B
Bryan
Hopefully this will be a simple bit of ignorance on my part. I am using the
following code in a general Module:
Function Append2Table(cbo As ComboBox, NewData As Variant) As Integer
On Error GoTo Err_Append2Table
' Purpose: Append NotInList value to combo's recordset.
' Assumes: ControlSource of combo has the same name as the foreign key
field.
' Return: acDataErrAdded if added, else acDataErrContinue
' Usage: Add this line to the combo's NotInList event procedure:
' Response = Append2Table(Me.MyCombo, NewData)
Dim rst As DAO.Recordset
Dim sMsg As String
Dim vField As Variant ' Name of the field to append to.
Append2Table = acDataErrContinue
vField = cbo.ControlSource
If Not (IsNull(vField) Or IsNull(NewData)) Then
sMsg = "Do you wish to add the entry " & NewData & " for " &
cbo.Name & "?"
If MsgBox(sMsg, vbOKCancel + vbQuestion, "Add new value?") = vbOK Then
Set rst = CurrentDb.OpenRecordset(cbo.RowSource)
rst.AddNew
rst(vField) = NewData
rst.Update
rst.Close
Append2Table = acDataErrAdded
End If
End If
Exit_Append2Table:
Set rst = Nothing
Exit Function
Err_Append2Table:
MsgBox "Error " & err.Number & ": " & err.Description, vbInformation,
"Append2Table()"
Resume Exit_Append2Table
End Function
Then in my form's events I am using the following:
Private Sub AssignedCAD_NotInList(NewData As String, Response As Integer)
Response = Append2Table(Me![AssignedCAD], NewData)
End Sub
Private Sub Originator1_NotInList(NewData As String, Response As Integer)
Response = Append2Table(Me![Originator1], NewData)
End Sub
Private Sub Originator_NotInList(NewData As String, Response As Integer)
Response = Append2Table(Me![Originator], NewData)
End Sub
Private Sub TaskDescrShort1_NotInList(NewData As String, Response As Integer)
Response = Append2Table(Me!TaskDescrShort1, NewData)
End Sub
Private Sub TaskDescrShort2_NotInList(NewData As String, Response As Integer)
Response = Append2Table(Me![TaskDescrShort2], NewData)
End Sub
Private Sub TaskDescrShort3_NotInList(NewData As String, Response As Integer)
Response = Append2Table(Me![TaskDescrShort3], NewData)
End Sub
Private Sub TaskDescrShort4_NotInList(NewData As String, Response As Integer)
Response = Append2Table(Me![TaskDescrShort4], NewData)
End Sub
The "Originator" and "AssignedCAD" work perfectly, but the 4 others
generate the error in my subject. All 4 use the same lookup table, which I
am sure is my problem, but I cannot see a reason to use 4 different lookup
tables all with the same data. The issue is that they can have up to four
different short task descriptions which I would like to be searchable by.
Any ideas on a solution here? TIA
following code in a general Module:
Function Append2Table(cbo As ComboBox, NewData As Variant) As Integer
On Error GoTo Err_Append2Table
' Purpose: Append NotInList value to combo's recordset.
' Assumes: ControlSource of combo has the same name as the foreign key
field.
' Return: acDataErrAdded if added, else acDataErrContinue
' Usage: Add this line to the combo's NotInList event procedure:
' Response = Append2Table(Me.MyCombo, NewData)
Dim rst As DAO.Recordset
Dim sMsg As String
Dim vField As Variant ' Name of the field to append to.
Append2Table = acDataErrContinue
vField = cbo.ControlSource
If Not (IsNull(vField) Or IsNull(NewData)) Then
sMsg = "Do you wish to add the entry " & NewData & " for " &
cbo.Name & "?"
If MsgBox(sMsg, vbOKCancel + vbQuestion, "Add new value?") = vbOK Then
Set rst = CurrentDb.OpenRecordset(cbo.RowSource)
rst.AddNew
rst(vField) = NewData
rst.Update
rst.Close
Append2Table = acDataErrAdded
End If
End If
Exit_Append2Table:
Set rst = Nothing
Exit Function
Err_Append2Table:
MsgBox "Error " & err.Number & ": " & err.Description, vbInformation,
"Append2Table()"
Resume Exit_Append2Table
End Function
Then in my form's events I am using the following:
Private Sub AssignedCAD_NotInList(NewData As String, Response As Integer)
Response = Append2Table(Me![AssignedCAD], NewData)
End Sub
Private Sub Originator1_NotInList(NewData As String, Response As Integer)
Response = Append2Table(Me![Originator1], NewData)
End Sub
Private Sub Originator_NotInList(NewData As String, Response As Integer)
Response = Append2Table(Me![Originator], NewData)
End Sub
Private Sub TaskDescrShort1_NotInList(NewData As String, Response As Integer)
Response = Append2Table(Me!TaskDescrShort1, NewData)
End Sub
Private Sub TaskDescrShort2_NotInList(NewData As String, Response As Integer)
Response = Append2Table(Me![TaskDescrShort2], NewData)
End Sub
Private Sub TaskDescrShort3_NotInList(NewData As String, Response As Integer)
Response = Append2Table(Me![TaskDescrShort3], NewData)
End Sub
Private Sub TaskDescrShort4_NotInList(NewData As String, Response As Integer)
Response = Append2Table(Me![TaskDescrShort4], NewData)
End Sub
The "Originator" and "AssignedCAD" work perfectly, but the 4 others
generate the error in my subject. All 4 use the same lookup table, which I
am sure is my problem, but I cannot see a reason to use 4 different lookup
tables all with the same data. The issue is that they can have up to four
different short task descriptions which I would like to be searchable by.
Any ideas on a solution here? TIA