Not in List event - Standardize?

[SAC]

I would like to standardize the Not in List event in a module so I can just
call it from a combo box, but I'm not sure how to reference the table and
the current combo box control.

Here's the code I use now:

Response = acDataErrContinue

Dim intAnswer As Integer
Dim dbs As DAO.Database, rst As DAO.Recordset
Dim strOldValue As String
Dim strName As String
Dim varCost As Variant

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblCustPros", dbOpenDynaset) 'Open the
recordset for the combobox table


intAnswer = MsgBox("Not in File. Add " & NewData & "?", vbQuestion +
vbYesNo) 'Caption of ComboBox

If intAnswer = vbYes Then
With rst
.AddNew
!Name = NewData
.Update
End With

Response = acDataErrAdded ' Requery the combo box list.

Else
Me.Undo
Response = acDataErrContinue ' Require the user to select
' an existing item
End If

Any ideas?

Thanks.

 

[Dirk Goldgar]

I would like to standardize the Not in List event in a module so I
can just call it from a combo box, but I'm not sure how to reference
the table and the current combo box control.

Here's the code I use now:

Response = acDataErrContinue

Dim intAnswer As Integer
Dim dbs As DAO.Database, rst As DAO.Recordset
Dim strOldValue As String
Dim strName As String
Dim varCost As Variant

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblCustPros", dbOpenDynaset) 'Open
the recordset for the combobox table


intAnswer = MsgBox("Not in File. Add " & NewData & "?",
vbQuestion + vbYesNo) 'Caption of ComboBox

If intAnswer = vbYes Then
With rst
.AddNew
!Name = NewData
.Update
End With

Response = acDataErrAdded ' Requery the combo box list.

Else
Me.Undo
Response = acDataErrContinue ' Require the user to select
' an existing item
End If

Any ideas?

Thanks.

Here's a barely-tested, very general solution. It will only work if the
combo box's rowsource (table or query) is updatable, and if the NewData
to be added is the only required field in the table.

'---- start of "quickie" code ----
Sub StdComboNotInList(NewData As String, Response As Integer)

Dim ctl As Access.ComboBox
Dim rst As DAO.Recordset
Dim strName As String
Dim astrColWidths() As String
Dim intCol As Integer

If Not (TypeOf Screen.ActiveControl Is Access.ComboBox) Then
Err.Raise 5, "StdComboNotInList", _
"Active control is not a combo box!"
Exit Sub
End If

Set ctl = Screen.ActiveControl

With ctl

If ctl.RowSourceType <> "Table/Query" Then
Err.Raise 5, "StdComboNotInList", _
"Combo box rowsource is not a table or query!"
Exit Sub
End If

strName = .Name
If .Controls.Count > 0 Then
strName = .Controls(0).Caption
End If

If MsgBox("Not in File. Add " & NewData & "?", _
vbQuestion + vbYesNo, strName) _
= vbYes _
Then
' Determine the combo box's first visible column.
' This will be the index of the recordfset field
' to which NewValue should be added.
astrColWidths = Split(ctl.ColumnWidths, ";")
For intCol = 0 To UBound(astrColWidths)
If astrColWidths(intCol) <> "0" Then
Exit For
End If
Next intCol

'Open a recordset on the combo's rowsource,
' and add a record to it with the new data.
Set rst = CurrentDb.OpenRecordset(.RowSource, dbOpenDynaset)
rst.AddNew
rst.Fields(intCol) = NewData
rst.Update
rst.Close
Set rst = Nothing

' Requery the combo box list.
Response = acDataErrAdded
Else
ctl.Undo
' Require the user to select an existing item.
Response = acDataErrContinue
End If

End With

End Sub
'---- end of "quickie" code ----

You still have to call this routine from each combo box's NotInList
event procedure, because you have to have the NewData and Respons
arguments that are passed to that procedure. Each NotInList procedure
would look like this:

'---- start of example NotInList event proc -----
Private Sub Combo1_NotInList( _
NewData As String, Response As Integer)

StdComboNotInList NewData, Response

End Sub

'---- end of example NotInList event proc -----

I'm not convinced that this approach is really worthwhile, because there
are so many variations in the way combo boxes and their rowsources may
be set up that you may not be able to use it all that often.

 

[SAC]

WOW!

Thanks.

Here's a barely-tested, very general solution. It will only work if the
combo box's rowsource (table or query) is updatable, and if the NewData
to be added is the only required field in the table.

'---- start of "quickie" code ----
Sub StdComboNotInList(NewData As String, Response As Integer)

Dim ctl As Access.ComboBox
Dim rst As DAO.Recordset
Dim strName As String
Dim astrColWidths() As String
Dim intCol As Integer

If Not (TypeOf Screen.ActiveControl Is Access.ComboBox) Then
Err.Raise 5, "StdComboNotInList", _
"Active control is not a combo box!"
Exit Sub
End If

Set ctl = Screen.ActiveControl

With ctl

If ctl.RowSourceType <> "Table/Query" Then
Err.Raise 5, "StdComboNotInList", _
"Combo box rowsource is not a table or query!"
Exit Sub
End If

strName = .Name
If .Controls.Count > 0 Then
strName = .Controls(0).Caption
End If

If MsgBox("Not in File. Add " & NewData & "?", _
vbQuestion + vbYesNo, strName) _
= vbYes _
Then
' Determine the combo box's first visible column.
' This will be the index of the recordfset field
' to which NewValue should be added.
astrColWidths = Split(ctl.ColumnWidths, ";")
For intCol = 0 To UBound(astrColWidths)
If astrColWidths(intCol) <> "0" Then
Exit For
End If
Next intCol

'Open a recordset on the combo's rowsource,
' and add a record to it with the new data.
Set rst = CurrentDb.OpenRecordset(.RowSource, dbOpenDynaset)
rst.AddNew
rst.Fields(intCol) = NewData
rst.Update
rst.Close
Set rst = Nothing

' Requery the combo box list.
Response = acDataErrAdded
Else
ctl.Undo
' Require the user to select an existing item.
Response = acDataErrContinue
End If

End With

End Sub
'---- end of "quickie" code ----

You still have to call this routine from each combo box's NotInList
event procedure, because you have to have the NewData and Respons
arguments that are passed to that procedure. Each NotInList procedure
would look like this:

'---- start of example NotInList event proc -----
Private Sub Combo1_NotInList( _
NewData As String, Response As Integer)

StdComboNotInList NewData, Response

End Sub

'---- end of example NotInList event proc -----

I'm not convinced that this approach is really worthwhile, because there
are so many variations in the way combo boxes and their rowsources may
be set up that you may not be able to use it all that often.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[SAC]

I'm not convinced that this approach is really worthwhile, because there
are so many variations in the way combo boxes and their rowsources may
be set up that you may not be able to use it all that often.

Thanks a bunch. I have a form that has a bunch of very similar ones and I
was trying to figure out how to make them all standard rather than pasting
in the same procedure into each NotInList event.