Problems with Not In List Code

[David Wetmore]

I wish to move search terms from a combobox into a list box. I also want to be able
to use the Not In List event to add a new search term to the combo box and list box. My combo box,
cboD1, has a query as a row source, SELECT tblDescriptors.Dkey, tblDescriptors.Name
FROM tblDescriptors WHERE (Class=1) And (FLAG=0) ORDER BY tblDescriptors.Name. The bound
column is column 1, tblDescriptors.Name.

My not in list routine is:
Private Sub cboD1_NotInList(NewData As String, response As Integer)
If (GoodDescriptor(NewData) = True) Then
response = acDataErrAdded
Call DPool.AddRecord(NewData, 1, cboD1, lstD1)
Else
response = acDataErrContinue
End If
End Sub 'cboD1_NotInList

"GoodDescriptor" just makes sure the string entered in the combobox is valid.

DPool.AddRecord is:

Public Sub AddRecord(strName As String, intClass As Integer, CBox As ComboBox, LBox As ListBox)
mrstDescriptorSet.Open
With mrstDescriptorSet
.AddNew
!Name = strName
!RefCount = 1
!Class = intClass
!Flag = 1
.Update
End With
LBox.AddItem (CStr(mrstDescriptorSet!DKey) & ";" & strName)
mrstDescriptorSet.Close
CBox.Requery
End Sub 'AddRecord

When I run, the CBox.Requery raises error 2118, "must save current field before requery" and, when I close
the form, the usual non in list message. BUT when I open the form and look in the combo box, the descriptor I just
added is there.

What am I doing wrong?

 

[boblarson]

Try swapping these two lines:
response = acDataErrAdded
Call DPool.AddRecord(NewData, 1, cboD1, lstD1)

--
Bob Larson
Access MVP
Free Tutorials and Samples at http://www.btabdevelopment.com

__________________________________

 

[boblarson]

Oops posted before I was done -

Swap these two lines:
response = acDataErrAdded
Call DPool.AddRecord(NewData, 1, cboD1, lstD1)

to this

Call DPool.AddRecord(NewData, 1, cboD1, lstD1)
response = acDataErrAdded

--
Bob Larson
Access MVP
Free Tutorials and Samples at http://www.btabdevelopment.com

__________________________________

 

[Steve Sanford]

Hi David,

I noticed a few things

cboD1, has a query as a row source, SELECT tblDescriptors.Dkey, tblDescriptors.Name
FROM tblDescriptors WHERE (Class=1) And (FLAG=0) ORDER BY tblDescriptors.Name. The bound
column is column 1, tblDescriptors.Name.

Actually, according to your SQL statement, the bound column is
"tblDescriptors.Dkey"


You have the cart before horse.
acDataErrAdded tells Access that the data has been added, so it needs to be
*after* the code that enters the new data.

When the NotInList event receives this information, it requeries the combo
box to refresh the list then compares the entry with it again. If everything
is OK the form accepts the entry and allows the user to move on. The next
time the combo box opens, the new item will be in the list.



Here is your code, modified. It should run but I can't test it.....
watch for line wrap.....
'-------------------------------------------
Private Sub cboD1_NotInList(NewData As String, response As Integer)
' check if good descriptor
If (GoodDescriptor(NewData) = True) Then
' add new data
Call DPool.AddRecord(NewData, 1, cboD1, lstD1)
' this tells the Not InList event new data was added
response = acDataErrAdded
Else
response = acDataErrContinue
End If
End Sub 'cboD1_NotInList

Public Sub AddRecord(strName As String, intClass As Integer, CBox As
ComboBox, LBox As ListBox)
mrstDescriptorSet.Open
With mrstDescriptorSet
.AddNew
!Name = strName
!RefCount = 1
!Class = intClass
!Flag = 1
.Update
End With
LBox.AddItem (CStr(mrstDescriptorSet!DKey) & ";" & strName)
mrstDescriptorSet.Close
'CBox.Requery ' not needed
End Sub 'AddRecord
'-------------------------------------------

HTH

 

[David Wetmore]

Following your suggestions, I moved the call to preceed the Response assignment
and also deleted the "CBox.Requery" in AddRecord.

Now, when I step through all the code I raise the Access message "The text you
entered is not an item on the list." when I try to step out of the NIL routine at
the "End Sub". When I reopen the form, the new text is in the combobox.

There may be something else going on because when I close the form containing
all this code I get a repetition of the error message followed by something about Access
not being able to save a record at this time. I have no code attached to
the deactivation or closing of the form.

The current code is as follows:


Private Sub cboD1_NotInList(NewData As String, Response As Integer)
If (GoodDescriptor(NewData) = True) Then
Call DPool.AddRecord(NewData, 1, cboD1, lstD1)
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub 'cboD1_NotInList


Public Sub AddRecord(strName As String, intClass As Integer, CBox As ComboBox, LBox As ListBox)
mrstDescriptorSet.Open
With mrstDescriptorSet
.AddNew
!Name = strName
!RefCount = 1
!Class = intClass
!Flag = 1
.Update
End With
LBox.AddItem (CStr(mrstDescriptorSet!DKey) & ";" & strName)
mrstDescriptorSet.Close
End Sub 'AddRecord

 

[AccessVandal via AccessMonster.com]

Else, try this.

David Wetmore wrote:
Private Sub cboD1_NotInList(NewData As String, Response As Integer)
If (GoodDescriptor(NewData) = True) Then
Call DPool.AddRecord(NewData, 1, cboD1, lstD1)
'-----------------------------------------------------------------------------
-----
Response = acDataErrContinue 'change it, see the subroutine
'-----------------------------------------------------------------------------
----
Else
Response = acDataErrContinue
End If
End Sub 'cboD1_NotInList

Public Sub AddRecord(strName As String, intClass As Integer, CBox As ComboBox,
LBox As ListBox)
mrstDescriptorSet.Open
With mrstDescriptorSet
.AddNew
!Name = strName
!RefCount = 1
!Class = intClass
!Flag = 1
.Update
End With
LBox.AddItem (CStr(mrstDescriptorSet!DKey) & ";" & strName)
mrstDescriptorSet.Close
'------addition---------------------------------------------------------------

 

[Steve Sanford]

Now, when I step through all the code I raise the Access message "The text you
entered is not an item on the list." when I try to step out of the NIL routine at
the "End Sub". When I reopen the form, the new text is in the combobox.

What line does the error message occur at??

There may be something else going on because when I close the form containing
all this code I get a repetition of the error message followed by something about Access
not being able to save a record at this time. I have no code attached to
the deactivation or closing of the form.

Without seeing the code for the function fGoodDescriptor and the sub
mrstDescriptorSet, it is really hard to say what is happening.


So I created a table, form and code (modified) to check the NotInList code.

I changed the names of some of the fields: "Name" and "Class" are reserved
words. (See http://allenbrowne.com/AppIssueBadWord.html)


The table names I used are: txtName, RefCount, lngClass, lngFlag. There is
also an autonumber field named "ID".

The form has the the recordsource set to the table. The detail section has
the fields in a row; Default view is set to Continuous Forms.

In the form header, I created a combo box and a list box - used you names.
The unbound combo box has these properties:

Name: cboD1
Row Source: SELECT txtName FROM Objectives;
Limit to List: Yes

This is my test code:
'-------------------------------
Option Compare Database
Option Explicit

Private Sub cboD1_NotInList(newdata As String, Response As Integer)
If (fGoodDescriptor(newdata) = True) Then
Call sAddRecord(newdata, 1, cboD1, lstD1)
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub

' fake out the test - note name change of function
Public Function fGoodDescriptor(newdata)
fGoodDescriptor = True
' fGoodDescriptor = False
End Function


' deleted CBox As ComboBox from parameter list
Public Sub sAddRecord(strName As String, intClass As Integer, LBox As ListBox)
Dim mrstDescriptorSet As DAO.Recordset

'need a recordset
Set mrstDescriptorSet = Me.RecordsetClone
' mrstDescriptorSet.Open
With mrstDescriptorSet
.AddNew
!txtName = strName
!RefCount = 1
!lngClass = intClass
!lngFlag = 1
.Update
End With
' LBox.AddItem (CStr(mrstDescriptorSet!DKey) & ";" & strName)
mrstDescriptorSet.Close
End Sub 'AddRecord
'-------------------------------

This adds a new record to the table - no errors.

The problems you are having might be in the standard module "DPool". Would
need to see the code to troubleshoot further.

HTH