Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    2 Star Lounger
    Join Date
    May 2001
    Location
    indiana
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Advanced NotInList (Access 97)

    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

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Advanced NotInList (Access 97)

    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.
    Wendell

  3. #3
    2 Star Lounger
    Join Date
    May 2001
    Location
    indiana
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Advanced NotInList (Access 97)

    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

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Advanced NotInList (Access 97)

    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.
    Charlotte

  5. #5
    2 Star Lounger
    Join Date
    May 2001
    Location
    indiana
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Advanced NotInList (Access 97)

    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

  6. #6
    New Lounger
    Join Date
    Dec 2002
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Advanced NotInList (Access 97)

    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

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Advanced NotInList (Access 97)

    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.
    Charlotte

  8. #8
    2 Star Lounger
    Join Date
    May 2001
    Location
    indiana
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Advanced NotInList (Access 97)

    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...

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Advanced NotInList (Access 97)

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

  10. #10
    2 Star Lounger
    Join Date
    May 2001
    Location
    indiana
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Advanced NotInList (Access 97)

    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

  11. #11
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Advanced NotInList (Access 97)

    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.
    Charlotte

  12. #12
    2 Star Lounger
    Join Date
    May 2001
    Location
    indiana
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Advanced NotInList (Access 97)

    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.

  13. #13
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Advanced NotInList (Access 97)

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

  14. #14
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Advanced NotInList (Access 97)

    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.



    <pre>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
    </pre>


    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.
    Regards
    John



  15. #15
    2 Star Lounger
    Join Date
    May 2001
    Location
    indiana
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Advanced NotInList (Access 97)

    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. <img src=/S/groan.gif border=0 alt=groan width=16 height=15>

    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 <img src=/S/sneaky.gif border=0 alt=sneaky width=15 height=15>

    Thanks for your help, as always

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •