Advanced NotInList (Access 97)

Topic

New Reply

Hi all!
I have a combo box that lists all the customers that my company deals with. I need to be able to add a new customer to this list when it does not already exist. Simple enough… Now the catch. I have this new customer sent through a function that checks for certain words and changes them to their appropriate abbreviations (such as abbreviating “Company” to “Co.’, and “Department” to “Dept.”) to keep things standard. When i don’t call the abbreviation function, I get the notinlist event to work and add the new record. The problem comes in when i programmatically change the text of the combo box to the new “abbreviated” format. What happens is that access sees that as another change and tries to call the notinlist event again.. sometimes in a loop.

Is there anything that I can do to only check the notinlist event after the abbreviations have been calculated? Or at least ignore everything until it has been changed (like setting a variable before the change, then setting it back afterward).

Thanks in advance

Reply | Quote

Replies

Are you calling your function to correct words in the Not In List event? If so you should be able to change the value of the combo box, and do that before the new record is saved, so that the Not In List Event is not retriggered.

Reply | Quote

what i have been doing is calling the function at the notinlist event (which triggered because the entry wasn’t in the list).. then it changes the value of the text in the combobox with the function. When it changes the text, this new text also is not in the list, so it triggers the notinlist event in the middle of the current event. It does do all this before the record is saved, but like i said, it triggers the event more than once.

Here is the code in my form…

Option Compare Database
Option Explicit

Dim SearchValue, ReplaceValue, SearchStr, Source, CustomerString As String

Private Sub cmbCustomer_NotInList(NewData As String, Response As Integer)
Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Dim Msg As String

CustomerString = cmbCustomer.Text

On Error GoTo Err_cmbCustomer_NotInList

SearchStr = CustomerString
Source = “cmbCustomer”

Call CheckForAbbreviations

‘ Exit this subroutine if the combo box was cleared.
If NewData = “” Then Exit Sub

‘ Confirm that the user wants to add the new customer.
Msg = “The Customer ‘” & NewData & “‘ is not in the list.” & vbCr & vbCr
Msg = Msg & “Do you want to add this Customer? @@Please make sure punctuation and spelling are correct!!”
If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
‘ If the user chose not to add a customer, set the Response
‘ argument to suppress an error message and undo changes.
Response = acDataErrContinue
‘ Display a customized message.
MsgBox “Please select a Customer from the list”
Else
‘ If the user chose to add a new customer, open a recordset
‘ using the Customers table.
Set Db = CurrentDb
Set Rs = Db.OpenRecordset(“tblCustomers”, dbOpenDynaset)

Rs.AddNew
Rs.Update

‘ Set Response argument to indicate that new data is being added.
Response = acDataErrAdded

End If

Exit_cmbCustomer_NotInList:
Exit Sub
Err_cmbCustomer_NotInList:
‘ An unexpected error occurred, display the normal error message.
MsgBox Err.Description
‘ Set the Response argument to suppress an error message and undo
‘ changes.
Response = acDataErrContinue

End Sub

Private Sub CheckForAbbreviations()

SearchValue = “company”
ReplaceValue = “Co.”

Call ChangeAbbreviations

SearchValue = “corporation”
ReplaceValue = “Corp.”

Call ChangeAbbreviations

SearchValue = ” and ”
ReplaceValue = ” & ”

Call ChangeAbbreviations

End Sub

Private Sub ChangeAbbreviations()
Dim searchstart, searchend As Integer

SearchStr = CustomerString

searchstart = InStr(1, SearchStr, SearchValue)

If searchstart 0 Then
searchend = searchstart + Len(SearchValue)

cmbCustomer.text = Left(SearchStr, (searchstart – 1)) & ReplaceValue & Right(SearchStr, (Len(SearchStr) – (searchend – 1)))

End If

End Sub

Reply | Quote

May I point out that this …

Dim SearchValue, ReplaceValue, SearchStr, Source, CustomerString As String

Only dims CustomerString as string. The rest are all declared as variants.

The best way to handle this is to make your routines CheckForAbbreviations and ChangeAbbreviations functions instead of subroutines and pass the NewData value into them so that they work on the string rather than on the combobox itself. Make them return the results of working on those strings. Then when you finally get the tested value back from those routines, you can simply change the NewData to match the new item in the list. Doing it that way won’t trigger any events unexpectedly because you aren’t directly changing the value in the combobox except within the NotInList event procedure.

Reply | Quote

Thanks for the tip, Charlotte
I will try what you suggested, but i have one question… What exactly is the difference between a subroutine and a function? If i know what the difference is, i will be able to use the appropriate one in the future when something similar occurs.

Thanks for your help and patience

Reply | Quote

I was just working on a similar problem a couple of days ago. I’ve attached the code I’m using at the moment. I didn’t like the idea of hard-coding the find and replace values, so I’m filling a 2-dimensional array instaed. Dimension one
is the search value, dimension two is the replace value. The procedure MakeReplacements sends both of these arguments to function ReplaceStr which returns the new string.

The difference between Subs and Functions is that Subs process something; Functions process something and return the result to the procedure that called the function. For example, Sub Makereplacements calls Function ReplaceStr at strNewString = ReplaceStr(strTitle, strElement, m_astrData(intRow, 2), 1). You return the processed value to the calling procedure by assigning that value to the function name. So ReplaceStr returns the strNewString value to Makereplacements at ReplaceStr = strWorkText. In sthe same way, the recorset of Find/Replace values is sent to Fill array at If FillArray(rst) > 0 Then. The recordset count is returned to the calling procedure after the array is
filled at FillArray = lngCount.

Hope that helps a bit. You just need a two column table of find and replace values to run this code.

P.S How does one get posted code to format properly on this list?

Option Compare Database
Option Explicit

Option Base 1

Private m_astrData() As String

Sub AbbreviateStrings()
Dim rst As DAO.Recordset

Dim strMsg As String

On Error GoTo Err_Handler

Set rst = CurrentDb.OpenRecordset(“tblAbbreviations”, dbOpenDynaset)

If FillArray(rst) > 0 Then
Call MakeReplacements
MsgBox “All strings abbreviated successfully”
Else
MsgBox “Array was not filled successfully”
GoTo Exit_Sub
End If

Exit_Sub:
Exit Sub

Err_Handler:
strMsg = “Error No ” & Err.Number & “: ” & Err.Description
MsgBox strMsg, vbExclamation, “Abbreviate Strings”
Resume Exit_Sub

End Sub

Function FillArray(rst As DAO.Recordset) As Long

Dim lngCount As Long
Dim intRow As Integer
Dim intCol As Integer

On Error GoTo Err_Handler

rst.MoveLast
rst.MoveFirst
lngCount = rst.RecordCount

ReDim m_astrData(lngCount, 2)

Do While Not rst.EOF
For intRow = LBound(m_astrData) To UBound(m_astrData)
For intCol = LBound(m_astrData, 2) To UBound(m_astrData, 2)
m_astrData(intRow, intCol) = rst.Fields(intCol -1)
Next intCol
rst.MoveNext
Next intRow
Loop

FillArray = lngCount

Exit_Sub:
Exit Function

Err_Handler:
Dim strMsg As String
strMsg = “Error No ” & Err.Number & “: ” & Err.Description
MsgBox strMsg, vbExclamation, “Fill Array”
Resume Exit_Sub

End Function

Private Sub MakeReplacements()
Dim rst As DAO.Recordset
Dim strTitle As String
Dim strElement As String
Dim strNewString As String
Dim intRow As Integer
Dim intCol As Integer

On Error GoTo Err_Handler

Set rst = CurrentDb.OpenRecordset(“tblTitles”, dbOpenDynaset)

With rst
Do While Not .EOF
strTitle = !Title
For intRow = LBound(m_astrData) To UBound(m_astrData)
strElement = m_astrData(intRow, 1)
If InStr(1, strTitle, strElement) 0 Then
strNewString = ReplaceStr(strTitle, strElement, _
m_astrData(intRow, 2), 1)
.Edit
!Title.Value = strNewString
.Update
strTitle = strNewString
End If
Next intRow
.MoveNext
Loop
End With

Exit_Sub:
Exit Sub

Err_Handler:
Dim strMsg As String
strMsg = “Error No ” & Err.Number & “: ” & Err.Description
MsgBox strMsg, vbExclamation, “Make Replacements”
Resume Exit_Sub

End Sub

Function ReplaceStr(TextIn, SearchStr, Replacement, CompMode As Integer)

Dim strWorkText As String
Dim intPointer As Integer

On Error GoTo Err_Handler

If IsNull(TextIn) Then
ReplaceStr = Null
Else
strWorkText = TextIn
intPointer = InStr(1, strWorkText, SearchStr, CompMode)
Do While intPointer > 0
strWorkText = Left(strWorkText, intPointer – 1) & _
Replacement & Mid(strWorkText, intPointer + Len(SearchStr))
intPointer = InStr(intPointer + Len(Replacement), _
strWorkText, SearchStr, CompMode)
Loop
ReplaceStr = strWorkText
End If

Exit_Sub:
Exit Function

Err_Handler:
Dim strMsg As String
strMsg = “Error No ” & Err.Number & “: ” & Err.Description
MsgBox strMsg, vbExclamation, “MsgBox Title”
Resume Exit_Sub

End Function

Reply | Quote

As BobB indicated, a Function returns a value, while a subroutine does not. And in Access, only a function can be called from an Access macro (That’s roughly the reverse of subroutine and function characteristics in other Office products) or placed in a property of an object in design view. Both can be declared public and called from any code within the application or declared private and called from any code within their local module.

I use functions a lot because I want to know if a routine succeeded, and a function can return a boolean value, if nothing else, to tell me it worked. Plus a function can be called without assigning the return value if you don’t need it.

Reply | Quote

Charlotte,
I tried what you said about passing the newdata to the ChangeAbbreviations function, and things worked very well – the value changed, the correct value was entered in the table, etc.. However, in the end after the record was added, the Text portion of the combo box was still the original value (before the abbreviations were added). I have tried everything i could think of to change that to the NewData variable, but no matter how i do it, it still tries to call the notinlist event again. If i don’t change the text, then the old value (withouth the abbreviations) is not in the list (which is expected because the abbreviated version is already there). What can i do from this point to iron this situation out? I can post my final code/form if you’d like…

Reply | Quote

Why don’t you post your revised NotInList event procedure. That’s where you have to handle it.

Reply | Quote

Here is the notinlist event which i am using. The ChangeAbbreviations function returns an abbreviated version of the NewData string that is input. The part where i am changing the NewData to the new string is where i am having problems. Like i said, everything works (as far as adding the correct record, updating the combo box, etc), except where the text in the combo box (before the change) is not the same as the NewData. If you need more, i can post a dummy db with the sample form i created..

Thanks in advance

Private Sub cmbCustomer_NotInList(NewData As String, Response As Integer)
Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Dim Msg As String

On Error GoTo Err_cmbCustomer_NotInList

‘ Exit this subroutine if the combo box was cleared.
If NewData = “” Then Exit Sub

NewData = ChangeAbbreviations(NewData)

‘ Confirm that the user wants to add the new customer.
Msg = “The Customer ‘” & NewData & “‘ does not exist.” & vbCr & vbCr
Msg = Msg & “Do you wish to add this Customer? @@Please make sure punctuation and spelling are correct!!”
If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
‘ If the user chose not to add a customer, set the Response
‘ argument to suppress an error message and undo changes.
Response = acDataErrContinue
‘ Display a customized message.
MsgBox “Please select a Customer from the list”
Else
‘ If the user chose to add a new customer, open a recordset
‘ using the Customers table.
Set Db = CurrentDb
Set Rs = Db.OpenRecordset(“tblCustomers”, dbOpenDynaset)

‘ Create a new record.
Rs.AddNew
Rs![Customer Name] = NewData
Rs![Customer Order] = “999”
Rs![Report Customer Name] = NewData

‘ Save the record.
Rs.Update

‘ Set Response argument to indicate that new data is being added.
Response = acDataErrAdded

End If

Exit_cmbCustomer_NotInList:
Exit Sub
Err_cmbCustomer_NotInList:
‘ An unexpected error occurred, display the normal error message.
MsgBox Err.Description
‘ Set the Response argument to suppress an error message and undo
‘ changes.
Response = acDataErrContinue
End Sub

Reply | Quote

This is untested because I haven’t the time this morning, but try it this way instead:

Private Sub cmbCustomer_NotInList(NewData As String, Response As Integer)
Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Dim Msg As String
Dim strNewData as String

On Error GoTo Err_cmbCustomer_NotInList

‘ Exit this subroutine if the combo box was cleared.
If NewData = “” Then Exit Sub

strNewData = NewData
strNewData = ChangeAbbreviations(strNewData)

‘ Confirm that the user wants to add the new customer.
Msg = “The Customer ‘” & strNewData & “‘ does not exist.” & vbCr & vbCr
Msg = Msg & “Do you wish to add this Customer? @@Please make sure punctuation and spelling are correct!!”
If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
‘ If the user chose not to add a customer, set the Response
‘ argument to suppress an error message and undo changes.
Response = acDataErrContinue
‘ Display a customized message.
MsgBox “Please select a Customer from the list”
Else
‘ If the user chose to add a new customer, open a recordset
‘ using the Customers table.
Set Db = CurrentDb
Set Rs = Db.OpenRecordset(“tblCustomers”, dbOpenDynaset)

‘ Create a new record.
Rs.AddNew
Rs![Customer Name] = NewData
Rs![Customer Order] = “999”
Rs![Report Customer Name] = strNewData

‘ Save the record.
Rs.Update

‘ Set Response argument to indicate that new data is being added.
Response = acDataErrAdded
Me.cmdCustomer = strNewData
End If

Exit_cmbCustomer_NotInList:
Exit Sub
Err_cmbCustomer_NotInList:
‘ An unexpected error occurred, display the normal error message.
MsgBox Err.Description
‘ Set the Response argument to suppress an error message and undo
‘ changes.
Response = acDataErrContinue
End Sub

That should at least get your closer to your goal. I changed the value passed into your ChangeAbbreviations to a variable on the off chance that your function is working directly on NewData rather than on a ByVal argument. ByRef arguments have their original value changed when they’re modified in a routine that receives them.

Reply | Quote

Charlotte,
Thanks for your reply. You were right, this did get me closer. Now what happens is that everything gets added correctly and the combo box text changes to match the new value without triggering the notinlist event. However, when the program continues through until the end (exit sub), it says the item is not in the list (even though the item did actually get put in the list). Any idea what is happening?

Thanks for your help and patience.

Reply | Quote

Did you try changing the combobox.Text property to the new string right after you changed the value of the combobox?

Reply | Quote

Charlotte,

I tried adding it directly after the value changed, and it kept asking me to add the new value (basically calling the notinlist function again). Everytime i said OK, it asked me again. When i said No, it went to my custom message to select a customer from the list.

Any thoughts about johnhutchinson’s post? I am wondering if LostFocus would cause any unforseeable problems in the future. I will give it a whirl and see what happens

Thanks for your help, as always

Reply | Quote

Then did you try undoing the combobox before setting the value? As for lost focus, it won’t fire under some circumstances, so it is somewhat less reliable that NotInList.

Reply | Quote

Charlotte

adding Me.cmbCustomer.Undo before the value is changed appears to do nothing… is this what you meant, or am i doing something wrong here? I know you are very busy, and i appreciate the help you are giving me. I am on the verge of just telling the owners that we can’t check for abbreviations.. I would really not like to do that.

here is the code i am working with (including the undo you suggested).

Private Sub cmbCustomer_NotInList(NewData As String, Response As Integer)
Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Dim Msg As String
Dim strNewData As String

On Error GoTo Err_cmbCustomer_NotInList

‘ Exit this subroutine if the combo box was cleared.
If NewData = “” Then Exit Sub

strNewData = NewData
strNewData = ChangeAbbreviations(strNewData)

‘ Confirm that the user wants to add the new customer.
Msg = “The Customer ‘” & strNewData & “‘ does not exist.” & vbCr & vbCr
Msg = Msg & “Do you wish to add this Customer? @@Please make sure punctuation and spelling are correct!!”
If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
‘ If the user chose not to add a customer, set the Response
‘ argument to suppress an error message and undo changes.
Response = acDataErrContinue
‘ Display a customized message.
MsgBox “Please select a Customer from the list”
Else
‘ If the user chose to add a new customer, open a recordset
‘ using the Customers table.
Set Db = CurrentDb
Set Rs = Db.OpenRecordset(“tblCustomers”, dbOpenDynaset)

‘ Create a new record.
Rs.AddNew
Rs![Customer Name] = strNewData
Rs![Customer Order] = “999”
Rs![Report Customer Name] = strNewData

‘ Save the record.
Rs.Update

‘ Set Response argument to indicate that new data is being added.
Response = acDataErrAdded
Me.cmbCustomer.Undo
Me.cmbCustomer = strNewData
Me.cmbCustomer.Text = strNewData

End If

Exit_cmbCustomer_NotInList:
Exit Sub
Err_cmbCustomer_NotInList:
‘ An unexpected error occurred, display the normal error message.
MsgBox Err.Description
‘ Set the Response argument to suppress an error message and undo
‘ changes.
Response = acDataErrContinue
End Sub

Reply | Quote

Take out the line setting the text property of the control. If you undo the combobox, that should clear the text. Then setting the value should select the newly added abbreviation from the list. Your big problem is that you’re doing two things here, trapping an item not in the list and adding it, but *also* changing it before adding it, which means that the item you trapped still isn’t going to be in the list.

Reply | Quote

So, is the program still comparing the original value to the values in the list? I did what you said, and even though the new value that displays in the combo box is also in the list, it still gives me the notinlist error. It would seem to me that if the new value was in the list, it should not give me the error, but if it is still comparing the old value, i don’t know what there is that i can do to stop it Should i consider giving up at this point? My last day working here is next friday, and this project needs to be done before i leave. I was hoping this was a simple problem (simple for the experts like you, anyways), but it is turning out to be a big hassle…

So, i guess if you have any other suggestions, i would be happy to try them, otherwise i will put this issue in the graveyard of things that can’t be done

Reply | Quote

I have had a quick play with this trying out the idea of cancelling the not-in-list event and then updating the record by hand.
This looks as if it will work but I don

Reply | Quote

There is something strange going on here – I can’t explain it, and it doesn’t solve your problem, but it might be good to know.

I found that changing NewData in code in the NotInList event will work OK if the new value is longer than the text originally entered in the combo box: the new value is added, the row source of the combo box is refreshed, and the text in the combo box is set to the new value. But if the new value is as long as or shorter than the original text, the text in the combo box is *not* set to the new value, and the NotInList event occurs again, even though the row source has been refreshed to include the new value.

Reply | Quote

It sounds like the same problem you sometimes encounter with comboboxes when you select a longer string and then try to match a shorter version of that string by typing it. The match capability of comboboxes an be odd at times.

Reply | Quote

Charlotte,

You’re correct (as usual). I hadn’t tested thoroughly enough. The crucial point is not whether the modified value of NewData is longer than the original one, but whether the matching algorithm finds a match for the original value of NewData in the row source. If a match is found (even if it is not the modified value that has been inserted into the row source), that is selected and NotInList doesn’t occur a second time. If a match is not found, NotInList occurs again.

Reply | Quote

I have been experimenting with this and get the same problems you do.
Setting the combobox text property only made it worse.

I have been able to get it to work by taking a different tack.
Set the Limit to List property to No, and ignore the “onNotinList” Event
Instead handle it all with the on Lost focus event.

The following code works with a combo box selecting a country from a list of countries.

Private Sub cmboCountry_LostFocus()
    Dim strNewValue As String
    Dim Response As Integer
    Dim Title As String
    Dim Style As Integer
    Const MB_YESNO = 4
    Const MB_ICONEXCLAMATION = 48
    Const MB_DEFBUTTON1 = 0, IDYES = 6, IDNO = 7
    Dim db As Database
    Dim rs As Recordset
    Dim sql As String
    Dim strCriteria As String
    ' end of declarations

    strNewValue =ChangeAbbreviations(cmboCountry)
    Me!cmboCountry = strNewValue
    Set db = CurrentDb
    sql = "Select * from tblCountries"
    Set rs = db.OpenRecordset(sql, dbOpenDynaset)
    strCriteria = "[Country] = '" & strNewValue & "'"
    rs.FindFirst strCriteria
    If rs.NoMatch Then
        ' not match found
        Title = "Text not in list"
        Style = MB_YESNO + MB_ICONEXCLAMATION + MB_DEFBUTTON1
        Response = MsgBox("The text you have entered is not in the list. Do you want to add a new entry to the list ?", Style, Title)
        If Response = IDYES Then
            rs.AddNew
            rs!Country = strNewValue
           rs.Update
           Me!cmboCountry.Requery
        Else
            Me!cmboCountry.SetFocus
            SendKeys " +{Tab}"
        End If
    rs.Close
   Set rs = Nothing
   Set db = Nothing
  Else
   ' value was found in list do nothing
  
 End If
End Sub

I tried using the After update event, and it seemed Ok at first. I found that if you chose No from the message box, you went back to the combo as expected, but could then Tab out of it without being stopped.

Reply | Quote