Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 182
  • Last Modified:

Automatic entry in text box

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?  



1 Solution
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!


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!
TerenceHewettAuthor Commented:
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.  



Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

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

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

"=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"

TerenceHewettAuthor Commented:
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.


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

TerenceHewettAuthor Commented:
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.


Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now