Best practice for record look up in a form?

[otterbaub]

Hey guys, ive been chasing my tail on this for a couple of hours and was wondering what is a good way to attack the problem. Not so plainly speaking i would like to have a vlookup formula result type in a form. So when the user inputs value in to txtboxA, txtboxB will display its corresponding record. The user will input a "123456789" into txtboxA, and txtboxB will display "555-555-5555"

I've tried using a query with the input on the form and the query executes and shows a good value, but does not update in the form. I tried the Dlookup in expression builder and i get an #name? error or just a blank txtbox. The only thing i havent done yet is write some VBA that will execute SQL query. Any guidance would be helpful

Below is my sample table, all values are unique so there is no chance of duplicates


MDN_Table

PK	ICC_ID	MDN
1	123456789	555-555-5555
2	987654321	222-222-2222

<tbody>
</tbody>

 

[xenou]

You can google using the keywords "cascading comboboxes msaccess" or "dependent comboboxes msaccess" for web pages on this topic.
Example (the only one I've tried myself):
Access Tips: Cascading Lists for Access Forms

Edit:
Oops, actually, since you only need a single value in a text box then yes, DLOOKUP should work. So you've just probably entered the formula wrong.

This is unclear. What do you mean "execute a query" on a form? Do you just mean the query or table the form is based on? Is DLookup in the form Textbox or in the Query?

I've tried using a query with the input on the form and the query executes and shows a good value,

 

[otterbaub]

I' tried each method a couple of times individually.
For the Query
I created a query,MDN_lookup, using the MDN Table with ICC_ID(not visible) and MDN fields, and a 3rd field using the expression creator that used txtboxA on a form as the search value. When i ran the MDN_lookup query i received good results on the query screen. I then tried ,using the expression builder, to display the output from MDN_lookop into txtboxB but received #Name?

For the Dlookup i googled how the formula works using MS Access: DLookup Function as a reference. However there seems to be some difference in syntax or i don't have enough knowledge on formulas. The diffrence being the text uses quotes for table and field names, where the expression builder uses brackets

the Dlookup should be this right?

in textboxB
=DLookUp("MDN","MDN_table","ICCID = " & [Forms]![Case]![txtboxA])

 

[xenou]

I've never used the expression builder (well, technically I have used it once). The syntax looks right.

If ICCID is text you will need quotes:
=DLookUp("MDN","MDN_table","ICCID = '" & [Forms]![Case]![txtboxA] & "'")

Also make sure you have used the correct form name -- Case -- and the right control name -- txtBoxA -- in the DLookup function. For instance, you have TextBoxB (with an E in textbox) but only txtBoxA in the other control (with no E in txtBoxA) but that may just be spelling here in the forum, not in real life.

What is in txtBoxA when the DLookup fails?

 

[otterbaub]

the apostrophe was the culprit, so now everything works as intended. You are correct the "e" was a forum spelling mistake and not in access. Now on to the next hurdle