Automatic entry in text box

Posted on 2005-05-05
Last Modified: 2009-07-29
Hi EE,

I have a form based on one table. In the table that the form is based on I have two columns called column 1 and 2 and these both have data in them.

On the form I have two text boxes, textbox 1 and textbox 2.  If a user enters a number like 123456 into textbox 1 (and this same number is within column 1 in the table, I would like textbox 2 to automatically populate with the corresponding reference that is within column 2 in the table (i.e. next to the relevant number in column 1).

Is this possible?  



Question by:TerenceHewett
    LVL 46

    Expert Comment

    Hi Terry ,
    It's very possible!
    It depends whether you need to enter data in Textbox 2.
    If not use a combo box instead with 2 columns the first one hidden- row source column1 ,column2.
    If you need to enter data in Textbox 2 simple code will do it,Let us know.

    Good Luck!


    LVL 1

    Expert Comment

    I'm not sure I understand what you want to do, but I think you can use the DLookUp function in textbox 2. Just add something like this to the control source for textbox 2:

    =DLookUp("column2Name","tableName","[column1Name]=" & Me.textBox1)

    Hope this helps!

    Author Comment

    Hi to both and thanks for your posts.

    I would like to have text automatically come into textbox 2 on my form and the text is dependant upon what is entered in textbox 1.

    Taking cindyrod's idea, I have would have the following Dlookup syntax:

    =DLookUp("Heading","tblcustomerlist","[custnumber]=" & [Me].[custnumber])

    Heading is the column that I want to appear in textbox 2 on my form.

    tblcustomerlist is the name of the table the form is sourced on and is also the table that the data is within.

    custnumber is the name of the column in the table and is also the name of textbox 1.

    I am not very good with Dlookup so would appreciate it if someone can explain where I am going wrong.  



    LVL 6

    Expert Comment

    Private Sub txtBox2_GotFocus()
    Dim rs As Recordset
    Set rs = CurrentDb.OpenRecordset("myTable", dbOpenSnapshot)

    If (rs.RecordCount = 0) Then
        'do nothing
        Do While Not rs.EOF
            If rs!Col1 = CInt(Me.txtBox1.Value) Then
                Me.txtBox2 = rs!Col2
                Exit Do
                Me.txtBox2 = "No data"
            End If
    End If
    End Sub

    you can do it in two ways

    1  Private Sub txtBox1_LostFocus()
           MsgBox "On Lost Focus"                   '<<< when you click on other object  
        End Sub

    2  Private Sub txtBox2_GotFocus()
             MsgBox "On Got Focus"                 '<<< when you click on box 2
        End Sub

    LVL 39

    Expert Comment

    Use a combobox instead of Text1, this will make things easier all around, for the examples below let's call it cboCompany which has 2 columns, the Company No in column 1 and Company Name in column 2. Lets change the name of Text2 to txtCoName

    If you do not need to edit the info in txtCoNamethen make the Control Source property of txtCoName (on the Data tab of the properties dialog) be ...


    if you do need to edit directly in txtCoName then use the After_Update event of cboCompany ...

    Private Sub cboCompany_AfterUpdate()
        Me.txtCoName.Value = Me.cboCompany.Column(1)
    End Sub

    LVL 12

    Expert Comment

    "=DLookUp("column2Name","tableName","[column1Name]=" & Me.textBox1)"

    This post by cindyrod should work for ur purpose.

    In ur case is "Heading" is also the name of field in the table "tblcustomerlist"


    Author Comment

    Hi all,

    I am actually having trouble creating a DLookup statement in general.  I have created a testbed database to check my syntax, and I still have a problem.  

    This is what I have:

    1 database with one table and one form.   The table has been called "table1" and the form has been called "form1" and the recordsource of this form is table1.

    table1 has 2 fields on it. They are "ref" and "heading".
    form1 has 2 unbound fields on it. unbound textbox1 is called "txtref" and unbound textbox2 is called "txtheading".

    When I enter a number into the "txtref" textbox on the form (and the number is one of the numbers in the ref field within the table), I would like the txtheading textbox to automatically find the relevant number in the ref field in the table and display the heading for that value.

    For example:

    ref: 1
    heading: name of person1

    ref: 2
    heading: name of person2

    ref: 3
    heading: name of person3

    If I enter into the txtref textbox on the form "2", I would like the txtheading textbox to display "name of person2".
    If I enter into the txtref textbox on the form "3", I would like the txtheading textbox to display "name of person3".
    and so on...

    I hope this helps you understand what I am trying to achieve. This is a huge headache for me so would appreciate your advice here.

    Thanks again to all for your help.


    LVL 12

    Accepted Solution

    chk as follows :

    Private Sub textbox1_AfterUpdate()
     Me.txtheading = DLookup("[heading]", "[tbl1]", "[ref]=" & Me.txtref)
    End Sub

    "[heading]" is the name of field in ur table and would bring this value for ur form txtheading [Me.txtheading]

    tbl1 is the table which has ref and heading as fields

    [ref] is the name of field in tbl1 whose value is being compared with value entered in form text box viz [Me.txtref]

    hope it should work


    Author Comment

    Thanks to all for your posts.  Aziz, thanks for your code, this appears to be doing what I needed. If I have further questions, I will post again.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    The first two articles in this short series — Using a Criteria Form to Filter Records ( and Building a Custom Filter ( — discuss in some detail how a form can be…
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now