Function similar to Excel VLOOKUP?

[xyba]

I have a table with 2 columns and in a form I have 2 textbox fields. When I input a specific value in one of the textboxes I want to automatically look up the value I've input in the first column of the table and populate the second textbox with the corresponding value in the second column of the table. e.g.

Table
A B
1 Cat
2 Dog
3 Horse

If I input "1" in the first textbox I need the text "Cat" to appear in the second textbox, similar to the Excel VLOOKUP function.

Is this possible in Access?

 

[shiznaw]

A simple Query will suffice. ComboBox with dropdown should be bound to Table. The 2nd control (TxTBox) should be bound to a query.

 

[xyba]

A simple Query will suffice. ComboBox with dropdown should be bound to Table. The 2nd control (TxTBox) should be bound to a query.

That's where I'm getting stuck unfortunately. Very new to Access and queries are something I'm struggling with. How do I link the query to the result textbox? Totally lost sorry.

 

[shiznaw]

I promise that I will get to you in under 2 hours. I'm just at work coding on a Database right now. If no one else has given an answer, I will provide you with the solution you need. ~ John Allen Shaw.

 

[xyba]

I promise that I will get to you in under 2 hours. I'm just at work coding on a Database right now. If no one else has given an answer, I will provide you with the solution you need. ~ John Allen Shaw.

OK, thank you

 

[JHB]

[FONT=&quot]The two [/FONT][FONT=&quot]hours have[/FONT][FONT=&quot] certain [/FONT][FONT=&quot]long overdue!
Create a form place 2 controls in it; call the first one A, (it should be unbound), call the second one B, the controlsource should be:

=DLookUp("B";"A_Table";"A=" & Nz([A];0))

[/FONT]

 

[arnelgp]

you put the code on the afterupdate event of your first textbox.

 

[xyba]

[FONT=&quot]The two [/FONT][FONT=&quot]hours have[/FONT][FONT=&quot] certain [/FONT][FONT=&quot]long overdue!
Create a form place 2 controls in it; call the first one A, (it should be unbound), call the second one B, the controlsource should be:

=DLookUp("B";"A_Table";"A=" & Nz([A];0))

[/FONT]

No problem thanks for getting back to me about this.
I keep getting an invalid syntax error when using the above. Maybe I've edited it wrongly? I've changed the code to include the names of my elements but not sure if I've done this correctly. Also, not sure what the Nz is and the final [A]. I've put:

=DLookUp("NameOfTxtBoxWithControlSource";"NameOfMyTable";"ColumnInTableWithDataToLookup=" & Nz([A];0))

 

[arnelgp]

use comma if semi colon is not working.

=dlookup("2nColumnNameInTable","tableName","1stColumnNameInTable = " & Nz([yourFirstTextBoxName], 0))

you have to supply the correct field names and control name.

 

[xyba]

use comma if semi colon is not working.

=dlookup("2nColumnNameInTable","tableName","1stColumnNameInTable = " & Nz([yourFirstTextBoxName], 0))

you have to supply the correct field names and control name.

Changing the semi-colon to a comma stopped the syntax error but...I now get #Error when in form view in the box I entered the code and it seems to have locked up the database now and I can't close it. Ooops!

 

[JHB]

Try the attached database.

 

[arnelgp]

do it in the afterupdate event of your first textbox, ie:

private sub yourfirstTextBoxName_AfterUpdate()
Me.secondTextBox.Value = dlookup("2nColumnNameInTable","tableName","1stCol umnNameInTable = " & Nz([yourFirstTextBoxName], 0))
end sub

 

[shiznaw]

Changing the semi-colon to a comma stopped the syntax error but...I now get #Error when in form view in the box I entered the code and it seems to have locked up the database now and I can't close it. Ooops!

Make sure that there is nothing in the Control Source for TextBox A (the numbered textbox).

 

[shiznaw]

The attached is my way of doing it:
Press Alt+Fn11 to get into VBA code:

Copy and Paste the following Code:

Private Sub Text_AfterUpdate()
Dim db As DAO.Database
Dim sqlstr As String
Dim rst As DAO.Recordset


Set db = CurrentDb()

sqlstr = "SELECT [TableName].[Animal Field Name] FROM [TableName] WHERE [Table Name].[Numbr Field Name] =" & TxtboxName
Set rst = db.OpenRecordset(sqlstr, dbOpenDynaset)

Me.Text0.Value = rst(0).Value
Set rst = Nothing
End Sub

 

[xyba]

Thanks for all the replies but I'm still struggling to get this to work so I've attached a redacted version of my actual db.

On the Update form when a value is input in DocID textbox, this value is searched in DocumentID column of DocT table and the respective document name from the second column is populated in DocTitle textbox on the Update form.

 

[JHB]

.. but I'm still struggling to get this to work so I've attached a redacted version of my actual db.
..

Yes because you've turn it wrong + didn't use the correct control name.
And have ID's in textformat, normally ID is numberformat - then it is difficult for us.
You should also have mention the form was a Split form.

 

[xyba]

Yes because you've turn it wrong + didn't use the correct control name.
And have ID's in textformat, normally ID is numberformat - then it is difficult for us.
You should also have mention the form was a Split form.

That works perfectly, thank you and apologies for the confusion/lack of info.

 

[JHB]

You're welcome, good luck.