none
Auto populate fields based on a Text field selection

    Question

  • I am trying to auto fill two fields based on a after-update of a text field.    

    This is the code i am using, but nothing happen.  

    Dim strText As String
        Dim strSearch As String
        strText = Me.Unit_Description.Value
        strSearch = "SELECT * from UICTable where (Unit Description like ""*"""""

    I am sensing i have to specify the fields that need to be auto populated. I am not sure how to incorporate that into my code.

    Thank You


    olu Solaru

    Wednesday, May 8, 2019 4:23 PM

All replies

  • Hi. Where are you doing this? Where is this code located? Is the form bound to the same table you're trying to lookup and populate? Can you post some screenshots, maybe?
    Wednesday, May 8, 2019 6:53 PM
  • I am trying populate the top two feilds based on the after update of the Unit Description field  Yes it is bound to the table


    olu Solaru

    Wednesday, May 8, 2019 7:29 PM
  • Hi. Looks like the Unit_Description box is a free entry box. If so, how do you know what is the UIC for whatever the user enters as a unit description? Do you have a table with UICs and matching descriptions? If so, what happens if the user misspells the description they entered?
    Wednesday, May 8, 2019 7:38 PM
  • It's pulling from a table called Unit Info Table  . Unit ode  is a unique value on table

    olu Solaru

    Wednesday, May 8, 2019 7:43 PM
  • It's pulling from a table called Unit Info Table  . Unit ode  is a unique value on table

    olu Solaru

    Right, but your question is you want to pull those information from the table after the user enters a "description," correct? So, my question is how do we know which Unit Code belongs to which description? Is it also in the table? If so, what about my question when the user mistypes the description? Seems to me you should be using a combobox rather than a textbox for this.
    Wednesday, May 8, 2019 9:04 PM
  • I have a combo box that populates the other fields based on the UIC Code, which is the UIC code, but the end users would like the option to do it by Unit Description.  The problem is that the Unit Description is rather long and will not make much sense using a Combo box, that is why I decided to use a textbox

    olu Solaru

    Wednesday, May 8, 2019 9:25 PM
  • I have a combo box that populates the other fields based on the UIC Code, which is the UIC code, but the end users would like the option to do it by Unit Description.  The problem is that the Unit Description is rather long and will not make much sense using a Combo box, that is why I decided to use a textbox

    olu Solaru


    Okay, that explains the reasoning behind using a text box. But, it doesn't answer my question how do we know which UIC to pull from the table based on what the user types. And also, the question of how do you want to handle those times (and they will happen) when the user mistypes the description? It might help also if you could show us what your table looks like and what is in it. For example, let's say you have a UIC of 57025 with a description of CNAP, it would be easy to pull 57025 "if" the user types in "CNAP" in the box. But what if they typed COMNAVAIRPAC?
    Wednesday, May 8, 2019 9:33 PM

  • olu Solaru

    Thursday, May 9, 2019 5:19 PM
  • Hi. Okay, you could try using DLookup(). For example, to get the UIC, try:

    DLookup("UIC","TableName","[Unit_Description]='" & [Unit_Description] & "'")

    I hope you don't have spaces in the names of your fields.

    Still though, you haven't answered my question what do you expect or want to happen if the user enters a description that doesn't match any of the ones you have in your table?

    Thursday, May 9, 2019 5:56 PM
  • I was actually thinking about doing something with a "Like" statement. According to the end users they are more likely to know the Unit Description than the UIC Code.  

    olu Solaru

    Thursday, May 9, 2019 7:34 PM
  • I was actually thinking about doing something with a "Like" statement. According to the end users they are more likely to know the Unit Description than the UIC Code.  

    olu Solaru

    I guess it would/could be something like this then.

    DLookup("UIC","TableName","[Unit_Description] Like '*" & [Unit_Description] & "*'")

    Thursday, May 9, 2019 7:55 PM
  • But how do i get it to auto populate the two other fields?  What would my syntax look like?

    olu Solaru

    Friday, May 10, 2019 2:41 PM
  • But how do i get it to auto populate the two other fields?  What would my syntax look like?

    olu Solaru


    Hmm, did you try the syntax I posted above?
    Friday, May 10, 2019 3:07 PM
  • If you are going to use a DLookUp such as DLookup("UIC","UICTable","[Unit_Description] Like '*" & [Unit_Description] & "*'") then in your After Update event you can use:

    Me.Unit_Code = DLookup("[UIC]","UICTable","[Unit Description] Like '*" & [Unit Description] & "*'")

    Me.Unit_Location = DLookup("[Unit Location]","UICTable","[Unit Description] Like '*" & [Unit Description] & "*'")

    Both Unit_Code and Unit_Location must be unbound controls on your form. Then once they are populated, you can use those criteria to search records. It appears you are trying to build a form where records can be searched and you want to automatically populate 2 of the search criteria fields. Is that correct?

    I will also be very interested to know if using the Like operator in a DLookUp function produces the results you are expecting.


    Friday, May 10, 2019 4:48 PM
  • Thank you for responding.  I tried the first line of code as follows:  


        Me.Unit_Description = DLookup("[Unit Description]", "Unit Information", "[Unit Description] Like '*" & [Unit Description] & "*'")

    And I get the following error message: 

    Access Run Time 2465

    Microsoft Access Can't find the theild '|1' referred to in your expression


    olu Solaru

    Wednesday, May 15, 2019 7:21 PM
  • I beleive you said your table name is UICTable, not Unit Information. So:

    Me.Unit_Location = DLookup("[Unit Location]","UICTable","[Unit Description] Like '*" & [Unit Description] & "*'")

    But I still don't know how using the Like operator in a DLookUp function is going to work unless you specify the field name in your input search form.

    You may need to use:

    Me.Unit_Location = DLookup("[Unit Location]","UICTable","[Unit Description] Like '*" & Forms![Your Form Name]![Unit_Description] & "*'")


    Wednesday, May 15, 2019 8:52 PM
  • So what I am using now are Sharepoint Lists instead of the tables within the database.  So I am making a reference to the linked tables that I created within the database that sync with the lists on sharepoints.  The table on the local database is called UICTable, but the table that links to the List on sharepoint is called Unit Information.  Hope that makes sense.  Let me post a screen shot.  The icons in yellow are the links to the list on sharepoint. You probably know about this.


    olu Solaru

    Thursday, May 16, 2019 8:38 PM
  • I know nothing about Sharepoit Lists but if they are the same as tables then I guess you would use:

    Me.Unit_Location = DLookup("[Unit Location]","Unit Information","[Unit Description] Like '*" & Forms![Your Form Name]![Unit_Description] & "*'")

    or you could use:

    Me.Unit_Location = DLookup("[Unit Location]","Unit Information","[Unit Description] Like '*" & Me.Unit_Description  & "*'")

    I think using a combo box to let the user select the actual Unit_Description instead of using the Like operator in a DLookUp is a much better way, but I will be interested to see if your method works for you.

    Thursday, May 16, 2019 9:00 PM