Results 1 to 5 of 5
  1. #1
    ino_mart is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2011
    Posts
    16

    Combobox not in list: lookup entered value in other table and return synonym if exists

    All

    I have a combobox which contains a list of words which are stored in a table.
    for e.g. table tblWords
    ID Word
    1 Dog
    2 Cat

    Many of these words have synonyms but to have it standardized only the predefined words in that table are allowed. So I have a table tblSynonyms
    ID Word_FK Synonym
    1 1 Hound
    2 1 Canine
    3 2 Kitty
    4 2 Pussycat

    I have a combobox which is linked with the first table. If the user enters a word which is not in the list, it must be searched into the second table. If it is in, it must be replaced by the related word in the first table.

    So with other words, if the user types in "Canine" in the combobox and leaves the field, the value must be replaced by Dog.

    I tried with code below, but I still get a message the value is not in the list although it is and NewData does get the correct word from the first table.



    Code:
    Private Sub Ingredient_fk_NotInList(ByRef NewData As String, Response As Integer)
    intX = Nz(DFirst("Word_fk", "tblSynonyms, "synonym='" & NewData & "'"), 0)
    
    
    If intX <> 0 Then
        NewData = DFirst("Word", "tblWords", "teller=" & intX)
    End If
    
    
    End Sub

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Probably get the error message because you're not setting the Response parameter. I suggest something more on the lines of
    http://www.databasedev.co.uk/not_in_list.html
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    ino_mart is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2011
    Posts
    16
    I found a solution

    Code:
    Private Sub Ingredient_fk_NotInList(ByRef NewData As String, Response As Integer)
    intX = Nz(DFirst("Word_fk", "tblSynonyms, "synonym='" & NewData & "'"), 0)
    
    
    If intX <> 0 Then
        Me.cboIngredient_FK.text= DFirst("Word", "tblWords", "teller=" & intX)
        Response = acDataErrContinue
    End If
    End Sub
    Last edited by ino_mart; 05-10-2021 at 01:36 PM.

  4. #4
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    So what Micron said then?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  5. #5
    ino_mart is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2011
    Posts
    16
    partially. I did need the Respone = acDataErrContinue to have no error message.
    But I had to assign the foreign key ID to the combobox "text"-property and not the value stored in variable NewData

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Pros and cons of table field value list vs lookup table
    By pmhb2011 in forum Database Design
    Replies: 9
    Last Post: 10-02-2020, 01:11 AM
  2. Replies: 13
    Last Post: 06-28-2018, 06:47 PM
  3. Code to return 'No Record exists'
    By coach32 in forum Programming
    Replies: 2
    Last Post: 07-28-2015, 07:29 AM
  4. Replies: 12
    Last Post: 08-20-2014, 05:26 PM
  5. Replies: 1
    Last Post: 12-09-2012, 07:11 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums